Thursday, April 1, 2010

Hierarchy mapping to relational databases

Today I was reading NHibernate in Action and while discussing the different approaches to map hierarchies there was a reference to this article "Mapping Objects to Relational Databases" by Scott Ambler (Ambler 2002) which I recommend you to read.

It was very useful because I saw formalized and justified all the decisions that I had to make in the past in order to represent that mapping in relational systems.

First, we have to recognize that in object oriented systems the relationships are "Is a" and "Has a" as oppose to relational databases where "Has a" is the only type of relationship. So, for the mapping "Is a" relationships have to be simulated using "Has a" relationships.

Taking this hierarchy as example:
Where Person is an abstract class (it could be an interface with a property name as well) and Student and Professor are subclasses with the former having a property studentNumber and the later a property salary.
There are 3 ways to map this hierarchy. First option, merge all the subclasses data into the parent; second, embed the parent into each child and third and final option make a one to one mapping of each table with each class in the hierarchy. This said in a more formalized mode:

  1. Using one table for an entire class hierarchy (merge all the subclasses data into the parent): With this approach a table is created with all the properties of the abstract class and the subclasses. In order to identify the semantic of the data stored in the table a column is created. With that column each row could be discriminated into one of the subclasses.
    In this example, Person has all properties of Student and Professor. Additionally it has also an objectType column to identify if the row of data represents a Student or a Professor.
    This is the easiest approach to take and the one that offers the best performance because there is no need query other tables to find related data. However, data integrity is compromised as all specific subclass properties need to be declared as null. Moreover, the data is not normalized at all.

  2. Using one table per concrete class (embed the parent into each child): Here the base abstract class is merged into each subclass so the result is a table per concrete subclass with the properties of the subclass and the properties of the base class.
    With this approach is more natural and efficient to manipulate and query each type separately. All the data need for each type is resumed into one single table.

    The main drawback is data replication because several columns have the same semantic in different tables. For example, Student has name as well as Professor. Any constraint needed for name has to be made in the name column of Student and the name column of Professor. There are problems representing the data when the two subclasses are not mutually exclusive (for example a student that is a professor as well). Maintainability could also be a huge issue as the hierarchy evolves and grows because a change in the parent means changing the corresponding column in each child.

  3. Using one table per class (one to one mapping of each table with each class in the hierarchy): One table is created for each class existing in the hierarchy. This approach is very straightforward as each subclass is mapped to a table that has all the properties and has a foreign key reference to the parent table representing the parent class.
    The main advantage of this approach is that the data is completely normalized. Subsequent changes and evolution to the Domain Model are easy to reproduce in the data definition. The constraints to the polymorphic properties could be applied without problems.
On the other hand, this is the one that perform the worse. It is very common to present the data as in the first approach so joins are required to accomplish that.
In my personal opinion, there is not only one approach to every hierarchy. The decision should depend on performance requirement, the nature of the interaction between the classes, etc. I would choose the first option (one table per entire hierarchy) for all the trivial cases. The second one for hierarchies were I don't need the polymorphic association between parent classes and subclasses and where subclasses are not very different one from each other. Finally, I would choose the last option for all complex cases and all other cases not covered by the first two.