Tuesday 28 June 2011

ISA hierarchy of tables inside a database and how to implement a single form

Please consider the following: Imagine I have a simple ISA hierarchy: parent A with children B and C. So let's say we have the corresponding tables named "a", "b", "c" and (created with the help of the gii model creator) the classes A, B, C The following is a screenshot from MySQL workbench:
 
 

The tables b and c have a primary key which ALSO plays the role of the foreign key to the primary key of table a (as expected)
I have an attribute which are common among the children classes B and C .. BUT for other reasons (as you can see other foreign keys) I cannot move these properties to the parent class A.
Initially I names these attributes with different names. This proved to be bad design! So I got back to the database and named them with the same name "protocol"
Now to access these attributes from the parent class A you have to create a custom property inside class A.
A custom property is not a variable but a combination of get/set methods to read and write to a variable. If you have getAttr and setAttr then the attribute is named "attr".
What I did was to create a getChild method (but no setChild method was necessary) Remember the parent class (created from gii) has already access to its children from the public function relations. So exploit this to get the child object.
Note: that this solution works only if row of A has ONLY ONE child, either B or C
The goal is to create a common form to fill the database tables a and b or c So you need an attribute to differentiate the category. Is model A a parent of B or a parent of C? Once more you use custom properties. I used getCateg and setCateg methods. In my case if model A is a newRecord then the category is null but you may want to use a different approach

I create with the help of CHtml a drop down list so user can select B or C This drop down list has values (0, 1) which correspond to class B or C The extra bonus is that this "categ" custom property is an attribute that can be used from the form widget and be validated
$attribute = "pvCat";
print $form->labelEx($model, $attribute);
print $form->dropDownList( $model, $attribute, $model::getCatDescs() );
print $form->error($model, $attribute);
As you see you have a seamless portion of code as the rest of the attributes which are real columns in table "a"
So the dropdown list feeds the "categ" attribute
This way the used has chosen the class. So you can now add a $protocol variable in your class A. This protocol is only temporary and plays the same role for both class B and C. This way you create a textbox to fill protocol for either class B or class C in a seamless way.

$attribute = "protocol";
print $form->labelEx($model, $attribute);
print $form->textField($model, $attribute);
print $form->error($model, $attribute);

I also edited the "rules" method of class A. For instance the "categ" has the 'in' validator with a specific range
But currently I use the controller to call explicitly $modelB->save() or $modelC->save() There is an idea to override save() function in class A to automagically save the child model (since in object oriented terms the class/model A is an abstract class) Please let me know if that works better for you

Authored by George Pligor
 

Applied Ideas Copyright © 2010
George Pligor - Stergios