The pattern discusses how to map each classes in an inheritance hierarchy to a separate database table.
How do you map an inheritance hierarchy of classes to database tables?
The forces are identical to those discussed with the One Inheritance Tree One Table pattern.
Map the attributes of each class to a separate table. Insert a Synthetic OID into each table to link derived classes rows with their parent table's corresponding rows.
Mapping our running example to tables results in five tables - one for each class. A single instance of a SalariedEmployee is represented in three of these five tables.
Write and update performance: The pattern provides a very flexible mapping but is not the best performer. Consider reading a FreelanceEmployee instance in our running example. This operation costs 3 database read operations: One on the FreelanceEmployee table, one on the Employee table and also one on the Party table. Writing costs 3 database write operations, each updating one or more indexes. The mapping is expensive in terms of database operations for write- and update intensive tasks. The costs rise with the depth of the inheritance hierarchy.
Polymorphic read performance: In our running example a FreelanceEmployee’s instance has a corresponding Employee instance and also a Party instance in the respective tables. Therefore, polymorphic reading only require reading one table. This is one of the attractive sides of the pattern besides space consumption and
Space consumption: The mapping has near optimal space consumption. The only redundant attributes are the additional synthetic OIDs needed to link the levels of hierarchy.
Maintenance cost: As the mapping is straightforward and easy to understand, schema evolution is straightforward and easy.
Ad-hoc queries: As the mapping generally requires accessing more than one table to retrieve an object instance’s data, ad-hoc queries are far from straight forward but hard to formulate for inexperienced users.
Heavy database load on root tables: The pattern causes heavy load on the root object type’s table. In our running example, each transaction holding a write lock on the FreelanceEmployee table needs to be accompanied by a write lock on the Party and also on the Employee table. See the Consequences Section of One Inheritance Tree One Table for a discussion of the negative effects of tables that form a bottleneck.
Abstract classes: Note that abstract classes are also mapped to a separate table.
Type identification: For the above example we presume, that a Synthetic Object Identity contains type information. Some type information is needed to construct the accurate class from the result of a polymorphic read query.
See also Representing Inheritance in a Relational Database [Bro+96] or see it as Concrete Inheritance Table at Martin Fowlers site.