Page Comparison

...

The overview in the previous What is re-motion? chapter briefly explains that three methods are popular when storing object instance data in relational database tables:

• single table inheritance
• concrete table inheritance
• class table inheritance

If you are familiar with these approaches, you can safely skip this section. An alternative discussion can be found at the castle project's website

In the following example, we use four classes:

• The abstract class Person
• The concrete class Employee, derived from Person
• The concrete class Customer, also derived from Person
• The abstract class Order, not derived from Person

Here is the UML diagram:

In the following discussion, we will simply use dashed boxes for signaling table borders.

...

1.) Table Person, with the columns

• FirstName
• LastName
• EmployeeNumber (NULL for objects of type Customer)
• Wage (NULL for objects of type Customer)
• CustomerID (NULL for objects of type Employee)

2.) Table Order, with the columns

• OrderID
• Date}

Since Order is entirely independent from Person, it gets its own table. This strategy for storing objects has three big advantages:

• the localization of instances in the table is very fast
• the reading of instances in the table is very fast
• the writing of instances in the table is very fast

The big disadvantage can't be seen in the handful of objects in our example, but for a practical number of classes and more bushy trees of inheritance, the number of columns in tables explodes and quickly becomes unmanageable.

...

The table Employee has these columns:

• FirstName
• LastName
• EmployeeNumber
• Wage

The table Customer has these columns:

• FirstName
• LastName
• CustomerID

Order is not affected by our move to concrete table inheritance. It's columns remain

• OrderID
• Date

Concrete table inheritance does not need redundant columns for objects of a base-class not sporting the columns of the sub-class, so it solves both the problem of not having a NULL for member variables and the problem of exploding numbers of columns. However, please note that there still is some redundancy, because both the Employee and the Customer table require their own FirstName and LastName columns.
Concrete table inheritance trades the column redundancy in single table inheritance for column duplication. Concrete table inheritance does not give you extra columns in your instance that have a purpose only in instances of some other type, but it gives you columns that are duplicated in the tables for other types. In the example above both the Employee and the Customer table require their own FirstName and LastName columns.

...

Consequently, we end up with four tables.

Person has the columns

• FirstName
• LastName

(and a column with a reference to either an object/row in the Customer or Employee table)

Employee has the columns

• EmployeeNumber
• Wage

(and a column with a reference to an object/row in the Person table)
Customer has the column

• CustomerID

(and a column with a reference to an object/row in the Person table)

...

In order to give you an impression how these approaches can be combined, we look at a simple toy application. The toy application is a typical enterprise application with the following concrete types of domain objects:

• Employees
• Premium Customers
• Standard Customers
• Order

The concrete classes for those domain objects are derived from the abstract classes Person and Customer classes in fairly obvious ways, and, just for luck, we throw in our mascot class Order in for good measure. Here is the UML diagram, with dashed lines delineating table boundaries:

...

As you can see, a mixture of single table inheritance, concrete table inheritance and class table inheritance is used. The layout of the given design is not arbitrary, careful analysis of how many objects there are of each class and how they are used must be taken into account. The design is based on the following assumptions:

• The company will process many more orders than it has customers or employees
• The company will have many more customers than employees
• The company will have many more standard customers than premium customers

Observe that the deepest line of ancestry is the one going from Object to StandardCustomer, along Person and Customer. In terms of extra columns, this makes single table inheritance somewhat problematic. On the other hand, overall performance of the system in typical use will be decided by how quickly instances of these classes can be processed. For this reason, we decide that quick access is the most important requirement and put all the member fields of StandardCustomer and all its superclasses into a single table Person:
StandardCustomer columns:

• CreatedBy
• CreatedAt
• FirstName
• LastName
• CustomerID
• CreditCardInfo

In this particular case, the decision of using single table inheritance looks particularly clever, because there are no other subclasses in the same table spawning off that go into the same table. Alright, Employee is a subclass of Person, and PremiumCustomer is a subclass of Customer, but these classes get their own tables, so their columns don't pollute the StandardCustomer columns. In this particular case, StandardCustomer's single table inheritance comes with all the benefits and only modest disadvantages.
As can be seen in the diagram, Employee and PremiumCustomer each get their own tables, but they need references back into the StandardCustomer table, because some of their instance data is stored there. Consequently:

Employee columns:

• EmployeeNumber
• Wage
• (A foreign key into the StandardCustomer table storing some more instance data)

PremiumCustomer columns:

• KeyAcctMgr
• (A foreign key into StandardCustomer table storing some more instance data)

Observe that the foreign keys into another table where the instance data shared with the superclass can be found clearly makes this a class table inheritance mapping, but here it comes at a price: For every Employee object and every PremiumCustomer object, not only a row in the Employee, or PremiumCustomer table must be created, but also one in StandardCustomer. This StandardCustomer table comes with an extra {{CreditCardInfo column that is neither part of an Employee object or PremiumCustomer object.
This column will be redundant for Employee and PremiumCustomer instances, but since we don't expect that many of either, we believe it is a good decision to hitch them a ride on the Person table. Assembling the instance data for Employee or PremiumCustomer objects is complicated, of course. Whenever we need instance data for either, we have to join it with the instance data in Person. This, too, is a fair price to pay here, because Employee data is needed only once a month for payroll, and PremiumCustomers are premium because they place huge orders once in a blue moon, giving the company large sales with very little administrative overhead. This means that slow access to instance data won't matter much for overall system performance.
Mapping the Order class is a no-brainer. It has nothing to share with any subclass of Person, so it needs no relation to that table. Since we don't have a separate Object table, we use concrete table inheritance for storing both Object members and Order members in a single table.

Order table columns:

• CreatedBy
• CreatedAt
• OrderID
• Date

In practice, concrete table inheritance is the most frequently used type. Again, be reminded that re-motion does not support class table inheritance yet. What re-motion does support is combinations of single table inheritance and class table inheritance within its domain.
A very practical feature is support for database views. Views can make any type of * table inheritance look like single table inheritance by merging the data from multiple tables into a single result set.

...