News Archive
PhpRiot Newsletter
Your Email Address:

More information

Table Inheritance with Doctrine

Note: This article was originally published at Planet PHP on 28 March 2012.
Planet PHP


Lately we had several projects where we had to store in a database very different items that shared a common state.

As an example take the RocketLab website you are reading: Events and BlogPosts are aggregated in the LabLog list as if they were similar items. And indeed they all have a Title, a Date and a Description.

But if you get the detail page of an Event or a BlogPost you can see that they actually don't contain the same information: a BlogPost contains essentially formatted text when an Event contains more structured information such as the place where the event will take place, the type of event it is, if people need to register to attend, etc..

Still we have to access those entities sometimes as similar items (in the LabLog list) or as different items (in the events list and in the blog posts list).

NaAve database model

Our first idea, and it was not that bad, Drupal does just the same, was to have a database table with the common fields, a field containing the type of item (it's either an event or a blog post) and a data field where we serialized the corresponding PHP object. This approach was ok until we had to filter or search LabLog items based on fields that were contained in the serialized data.

Indeed SQL does not know anything about PHP serialized data, thus you cannot use any of it's features on that data.

So how do you get all the LabLog items that are Events, happen in April 2012 and are "techtalks"? The only way is to go through all the Events records of April, unserialize the data and check if it's a techtalk event. In SQL you would normally only do a single request to find those items.

A better database model

There is a better way to model this in a database, it's called table inheritance. It exists in two forms: single table inheritance and multiple table inheritance.

Multiple table inheritance

Multiple table inheritance requires to use three tables instead of a single one. The idea is to keep the common data in a "parent" table, which will reference items either in the Event table or in the BlogPost table. The type column (called the discriminator) helps to find out if the related item should be searched in the Event table or in the BlogPost table. This is called multiple table inheritance because it tries to model the same problem as object inheritance using multiple database tables.

When you have a LabLogItem you check the type field to know in which table to find the related item, then you look for that item with the ID equals to related_id.

Single table inheritance

Alternatively the same can be modelled in a single table. All the fields are present for all the types of LabLogItem but the one that do not pertain to this particular type of item are left empty. This is called single table inheritance.

Single or multiple table inheritance

The difference is really only in how the data is stored in the database. On the PHP side this will not change anything. One may notice that single table inheritance will promote performance because everything is in a single table and there is no need to use joins to get all the information. On the other hand, multiple table inheritance will allow a cleaner separation of the data and will not introduce "dead data fields", i.e. fields that will remain NULL most of the time.

Table inheritance with Symfony and Doctrine

Symfony and Doctrine make it extremely easy to use table inheritance. All you need to do is to model your entities as PHP classes and then create the correct database mapping. Doctrine will take care of the hassle of implementing the inheritance in the database server.

Please note that the code I present here is not exactly what we use in RocketLab; we are developers and as such we always have to make things harder. But the idea is there...

The parent entity

In the case of RocketLab we created a parent (abstract) entity, called LabLogItem, that contains the common properties.

/** * This class represents a LabLog item, either a BlogPost or an Event. * It is abstract because we never have a LabLog entity, it's either an event or a blog post. * @ORM\Entity * @ORM\Table(name="lablog") * @ORM\InheritanceType("SINGLE_TABLE") * @ORM\DiscriminatorColumn(name="type", type="string") * @ORM\DiscriminatorMap({"event" = "Event", "blogpost" = "BlogPost"}) */ abstract class LabLogItem { /** * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @ORM\Column(type="date") */ protected $da

Truncated by Planet PHP, read more at the original (another 4637 bytes)