jacopo beschi: yet another web guy

Php class table inheritance

Created at: Jan/07/2014 jacopo beschi

In the previous article i talked about Single table inheritance which is one of the four ways to map inheritance into RDBMS (Relational Database Management System). As stated in the other article this pattern comes from Marwin fowler PoEAA Book. In this one we talk about Class table inheritance. Class table inheritance is an approach that consist in creating a table for each class in the object-model. To explain that let's use the example used in the other post: imagine the hierarchy of animals and in particular dogs and cats, they both are pets and but they are also animals. Imagine now that every animal have the attribute sex, pet have name and dog collar. Here is the UML diagram of the class structure:

file

And here is the code that creates the structure in the db following the pattern:

CREATE TABLE ANIMAL (ID int NOT NULL AUTO_INCREMENT, Sex Varchar(255), PRIMARY KEY ID);
CREATE TABLE PET (ID int NOT NULL, Name Varchar(255), PRIMARY KEY ID, FOREIGN KEY (ID) REFERENCES ANIMAL(ID) );
CREATE TABLE DOG (ID int NOT NULL, Collar Varchar(255), PRIMARY KEY ID, FOREIGN KEY (ID) REFERENCES PET(ID) );
CREATE TABLE CAT (ID int NOT NULL, PRIMARY KEY ID, FOREIGN KEY (ID) REFERENCES PET(ID) );

As you can see with Class table inheritance we create a table for every class; note that we use the ID of every subclass as a foreign key for the parent class, in this way we have the same key for the set of tables rappresenting the same leaf class in the hierarchy.

The advantages of this approach are:

  • You don't waste any space: every column is relative to the right class.
  • It's easy to see the relation between database and classes.

There are some disadvantages with this approach aswell:

  • Every time you load or save an object you need to check multiple tables (Many joins)
  • Moving fields up and down with the hierarchy requires db changes
  • The top table class may become a bottleneck

If you use Laravel framework and want to implement that with his ORM Eloquent you may land in some big troubles: in fact this pattern doesn't fit well with Active Record (Pattern used by Eloquent). Aniways i'm planning to write and article explaining how you can implement that (Mostly proof of concept). My strong advise in the case you wanna use this pattern is to use that with Doctrine ORM. Aniways there are some other orm that support that built-in like propel orm.

Well, that's all for today. Enjoy!

If you liked this article:

comments powered by Disqus