Page 1 of 1

Possibilities with Modify Actions amd Databases

Posted: Wed Apr 25, 2007 11:56 am
by Ollie Saunders
Imagine there are 2 tables. Head and Hair. Head has a foreign key to Hair that may be null (a head can be bald). A simple parent child relationship, each table describes itself. I have these in separate tables because I believe it gives me extra flexibility e.g. if I wanted a new table to describe a cat I could use the existing Hair table to describe its fur. OK I realise this is an eccentric example but bare with me.

So the SQL looks a bit like this

Code: Select all

CREATE TABLE `Head` (
    `headId`           INT UNSIGNED        NOT NULL AUTO_INCREMENT,
                                            PRIMARY KEY(`headId`),
    `roundness`        INT UNSIGNED        NOT NULL DEFAULT 0,
    `_hairId`          INT UNSIGNED        NULL
);
CREATE TABLE `Hair` (
    `hairId`           INT UNSIGNED        NOT NULL,
    `hexColour`        CHAR(6)        NOT NULL,
    `greasyness`       INT UNSIGNED        NULL
);
Now say I have a load of PHP code where people can create and modify heads. There is one create form and one modify form. This means the POST submissions from these forms are going to be dealing with both tables. The conditionally of the hair is handled by using a check field to toggle hair on or not. On the create form this is simple:

Code: Select all

if ($_POST['wantHair']) {
    // add a hair record 
    $hairId = $db->insert_id;
} else {
    $hairId = 'null';
}
// create the head record using the $hairId
My predicament begins with the modify form. When I modify a head that Head record may or may not have Hair and the user may or may not want it modified to have Hair. So I'm faced with 4 possible scenarios I have to cope with.
  • There is currently no hair on the head and we don't need to add any (SELECT `Head` followed by UPDATE `Head`)
  • There is currently no hair on the head and the user wants to add some (SELECT `Head` followed by INSERT `Hair` followed by UPDATE `Head`)
  • There is currently hair on the head and the users wants to get rid of it (SELECT `Head` followed by DELETE `Hair` followed by UPDATE `Head`)
  • There is currently hair on the head and the user wants to keep it and no doubt update some of properties of the hair (SELECT `Head` followed by UPDATE `Hair` followed by UPDATE `Head`)
I always have to SELECT FROM `Head` in order to determine if Hair already exists and I always have to UPDATE `Head` because the user might have changed the roundness property of the Head. Of course by the time I've written this I've drowned in a sea of logic. There must be an easier way!!

Oh this is only with one optional table. What if there are 2 or more! Say I want my head to optionally have a hat.

Posted: Wed Apr 25, 2007 12:29 pm
by Begby
This would be a ton easier if you stored headID on the hair table instead of hairID in the head table.

Posted: Wed Apr 25, 2007 3:16 pm
by Christopher
Move color and greasyness into the Head record and lose the Hair record. I don't see how hair color is different from roundness. Do people need/have multiple sets of hair?

Posted: Wed Apr 25, 2007 4:44 pm
by Ollie Saunders
I think I'm going to go with your suggestion aborint but I am aware that I'm sacrificing database flexibility. Right now I don't give a stuff about that, so my mind is decided, but in future what is the best of a tackling this? Have I completely misinterpreted database normalisation or is it a ridiculous pipe dream I should stop caring about?

Posted: Wed Apr 25, 2007 8:31 pm
by Begby
Here is a simplified idea for this logic encapsulated into a class. You can probably make hair extend an attribute class with the save method(), then have head have an attributes collection that you call save on. The key is to encapsulate it all into a save() method instead of trying to determine insert, update, delete within the head object, let the save method determine that.

With this kind of structure you can add as many attributes as you like without going nuts.

Code: Select all

class Head
{

 private $headID ;
 public $roundness ;
 
 public $hair ;

 public function save()
 {
   // save the head record
   $this->headID = someSQLStuff() ;
   //  save the hair
   $this->hair->headID = $this->headID ;
   $this->hair->save() ;

   // the above is a little messed, for instance you won't need to set the headID if you aren't inserting a new
  // head, but you can probably figure that out
 }
}


class hair()
{
  public headID ;
  public hairID ;
  public color ;
  public greasyness ;

  public function save()
  {
    if ($this->color == null && $this->hairID == null ) 
    {
       return ;  // do nothing
    }
    else if ($this->color == null && $this->hairID != null )
    {
       // delete record
    }
    else if ($this->color != null && $this->hairID == null )
    {
       // insert a record
    } 
    else
    {
      // update the record
     }
  }
}

$head = new Head() ;
$head->hair->color = 'blue' ;
$head->save() ;