The pattern demonstrates a way to map a complete inheritance hierarchy to a single database table.
How do you map an inheritance hierarchy of classes to database tables?
The forces relevant here, besides the General Forces are:
Polymorphic read and space consumption versus write/update performance: In an inheritance hierarchy you need to support queries for all Party objects matching some given criteria. The result set is polymorphic, In our example it might contain Employees, FreelanceEmployees or SalariedEmployees. Solutions that best support polymorphic queries are those who either waste disk space or are expensive in terms of write performance.
Locking schemes of your database: Some databases implement page level locking only or might be programmed to escalate locks very early. In this case you have to take care that database traffic on a single table does not exceed a limit that results in excess locks and poor performance. If you map to many classes to a single table, it is likely that you attract much traffic.
Depth of the inheritance hierarchy: Some solutions that work acceptable with flat inheritance hierarchies become ugly with very deep inheritance hierarchies.
Maintenance effort: Mapping solutions that clutter a single object’s data across several tables might be fast for polymorphic reading. The drawback is, they are very hard to maintain in case new object attributes are added or existing object attributes are deleted. Schema evolution needs to take into account that data are replicated across the physical data model. This may easily turn into a maintenance nightmare. Other maintenance cases are insertion or deletion of a class in an inheritance hierarchy.
User-defined queries: If you want to give your user the opportunity to form her own queries you need to assure that table mappings are still understandable from a user’s perspective.
Use the union of all attributes of all objects in the inheritance hierarchy as the columns of a single database table. Use Null values to fill the unused fields in each record.
The
table design for our running example looks as follows.
Write and update performance: Using One Inheritance Tree One Table allows reading and writing of any BaseClass descendant with a single database operation.
Polymorphic read performance: As all BaseClass descendants can be found in a single table, polymorphic reading is straightforward. The only challenge is to construct the correct object type for a selected database record. There are plenty of patterns for this task like Abstract Interface [Col96 ].
Space consumption: As you see from in the mapping depicted above, storing the objects’ attributes requires more space than absolutely necessary. The waste of space depends on the depth of the inheritance hierarchy. The deeper the hierarchy and the bigger the difference between the union of all attributes and the attributes of an average object, the bigger the waste of space.
Balancing database load across tables: Mapping too many classes to a single table may cause poor performance. The sources of such problems can be found in database behavio
If your database uses page level locking, too much traffic on a single table may severely slow down the access. Parts of the effect may be compensated by clever clustering. If traffic on a single table gets too heavy, expect performance degradation and also deadlocks
Too many locks on a single table may result in lock escalation[1]. The number of locks that cause lock escalation is typically a parameter of relational database systems.
Some classes need secondary database indexes to speed up search. If you implement many classes in a single database table, you add up indexes on that table. Too many indexes on a single table cause updates to become very slow as all the indexes have to be updated.
Maintenance cost: As the mapping is straightforward and easy, schema evolution is also comparably straightforward and easy as long as the inheritance hierarchy does not become too deep.
Ad-hoc queries: As the mapping is intuitively clear, formulating ad-hoc queries is fairly easy.
Consider mapping all objects to a single table: You may also use the mapping to store all object types in a single table - resulting in heavy traffic on the table. For small applications this is a feasible and very flexible approach.
Waste of space: You might check, whether your relational database allows packing of NULL values. In this case the above mapping becomes more attractive as you avoid waste of space for NULL values.
Type identification: You need to insert type information into your table. You could compute the type information from the NULL values. This is not too convenient as Synthetic Object Identities should contain type information anyway. Hence it is better to use a straight Synthetic Object Identity that contains type information.
See also Representing Inheritance in a Relational Database [Bro+96] or see the same thing as Single Inheritance Table from Martin Fowler