27.03.2012 by daniel.barsotti

Introduction

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).

Naïve 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.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

/**

* 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.

@ORMInheritanceType: indicates that this entity is used as parent class in the table inheritance. This example uses single table inheritance, but using multiple tables inheritance is as easy as setting the parameter to “JOINED”. Doctrine will create an manage the unique or multiple database tables for you !

@ORMDiscriminatorColumn: indicates which column will be used as discriminator (i.e. to store the type of item). You don’t have to define this column in the entity, it will be automagically created by Doctrine.

@ORMDiscriminatorMap: this is used to define the possible values of the discriminator column as well as associating a specific entity class with each type of item. Here the discriminator columns may contain the string “event” or “blogpost”. When its value is “event” the class Event will be used, when its value is “blogpost”, the class BlogPost will be used.

Basically that’s the only thing you need to use table inheritance, but let’s have a look at the children entities.

The children entities

We have two regular entities to model the events and blog posts. Those entities extend LabLogItem.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

/**

* Represent a blog post item.

* Note that this class extends LabLogItem

*/

classLabLogItemBlogextendsLabLogItem

{

/**

* @ORMColumn(type="text")

*/

protected$content;

/***** Getters and setters *****/

publicfunctiongetContent()

{

return$this-&gt;content;

}

publicfunctionsetContent($content)

{

$this-&gt;content=$content;

}

}

/**

* Represent an event item.

* Note that this class extends LabLogItem

*/

classLabLogItemEventextendsLabLogItem

{

/**

* @ORMColumn(type="string")

*/

protected$eventType;

/**

* @ORMColumn(type="string")

*/

protected$location;

/**

* @ORMColumn(type="boolean")

*/

protected$requiresRegistration;

/***** Getters and setters *****/

publicfunctiongetEventType()

{

return$this-&gt;eventType;

}

publicfunctionsetEventType($type)

{

$this-&gt;eventType=$type;

}

// And so on...

}

There is not much special in the children entities. An important thing to note is that the common fields defined in the parent entity LabLogItem SHOULD NOT be repeated here. Also you may notice that there is no annotations in the children such as @ORMEntity to indicate that they are entities. Indeed they will inherit the annotations of LabLogItem and become entities.

From now on, when you create a PHP object of type Event and ask the entity manager to persist it, Doctrine will automatically do the complex work for you. From the developper point of view, Events and BlogPosts are just entities like any other.

It’s easy to do operations on items which you don’t know exactly the type:

// We have searched the Event entity repository so what we get in $item MUST BE an Event

echo$item-&gt;getEventType();

Conclusion

As you can see above using table inheritance with Symfony and Doctrine is very easy. It’s just a matter of creating the parent class and the correct mapping. Furthermore you can switch from single to multiple table inheritance by modifying one line of code.

This technique should be used whenever you need to store items with a common state but that are very different in their nature.

Your article is the best explanation I’ve found about dotrince inheritance mapping!
So i guess maybe you can help me a little… i would know can i create a relationship between an entity (ex User) and LabLogItem?

I don’t understand. Can you explain me how you can get the event type on LabLogItem object. Since you fetch LabLogItem and you don’t load LabLogItemEvent, i don’t know how you can call the method getEventType and get a result… Or doctrine is dealing with that and automatically load the good instance?

October 16, 2013 8:26 am by Damien

Of course. Each entity must have the annotation @Entity.
Besides you can either set a unique repository for each child entity or set the same repository (the parent one for example) for all child entities.
Services and factories are very helpful for that.

July 10, 2013 3:25 pm by Damien

Do you know how to create relations on the children entities ?
I have a parent entity and 15 children entities, some of them have relations with other entities.

I did everything fine, but Doctrine keeps telling me that my mapping is inconsistent

And do you know how to query multiple children in the parent repository.
For instance : where type in (1,2,5,9) ?

Thanks

September 7, 2013 6:07 pm by sensi

Nice tutorial, really helpful.
Is it possible to use this method with a “mappedSuperclass”?

October 14, 2013 6:59 pm by Jeroen Schouten

I got the following exception:

[DoctrineORMMappingMappingException]
Class “AppEntityChild” sub class of “AppEntitiyParent” is not a valid entity or mapped super class.

The fix is easy: The children entities must have a @ORMEntity annotation in order to work!

They basically only ever stored serialized objects and then created tables with just the fields they wanted to index and query on. A side benefit was that they could create or remove indexes and fields on indexes on the fly.

Or maybe using a search engine rather than directly querying objects on your data store could be interesting as well.

Not saying that these directly apply to your particular case. But interesting approaches for similar issues.

@khepin: there are use cases for this, yes. but for a simple case like we encountered, it was a really bad choice. the jcr implementation jackrabbit for example does that: serialize the data into table cells and using lucene to search. in the jackalope-doctrine-dbal implementation of PHPCR we do it similar, but for now without lucene and thus
without full search.

March 29, 2012 9:02 am by Jory Geerts

Nice article.
I would personally go with multiple table inheritance (which the Doctrine documentation confusingly calls ‘class table inheritance’) since it is a cleaner setup and the performance differences shouldn’t be that big. (For small-ish datasets anyway.)

I worked on a project where we had a bigger inheritance tree, I think 6 entities with one common parent, and one of those 6 had two child-entities itself.
Do you guys have experience with that kind of a setup? (Either in Doctrine, or in general – for the project I worked on we just had 6 tables and a buckload of duplicate fields. )
I’d say the number of “dead data fields” gets pretty high pretty fast.

Thanks for the informative post. I think it’s explaining the topic very good.

There’s one mistake regarding drupal: “…, Drupal does just the same, … and a data field where we serialized the corresponding PHP object.”

Actually Drupal core (d6/d7) does implement a kind of multi table inheritance and it does not store any serialized data for nodes in the node, node_revision and field_data_body(d7) tabels. If you are using CCK(d6)/Fields(d7) it actually creates tabels for your subtypes.

To be precise: Drupal is not using any of the OO features of PHP for node types. But custom node types can be seen as a subtype of the general node type.