How do you design a Domain Model and the related database tables? People usually follow one of the following two approaches:
- Design relational database first and then proceed with creating classes (of course, if you are using object-oriented technologies)
- Design domain model first followed with the database design
While both approaches lead to the same outcome i.e. database + domain model design, there are advantages of following the second approach.
Inheritance is one of the feature of object-oriented programming. It is used to define “is-a” relationship between objects. However, relational databases don’t support inheritance. Hence, when we do database design first, we might end up with designing tables without considering their inheritance. Let’s see an example.
I have to store an information for two types of statements such as bank account statement and credit card statement for a user. I can create a table for each type of statement which will have all the attributes related to it. Thus, I can create two tables – AccountStatement and CreditCardStatement – as shown in Figure 1 (only few columns are added for simplicity).
Figure 1. Database tables for Concrete Table Inheritance.
The database design for these tables does not use inheritance. However, when I create the domain model, instead of creating two non-related classes AccountStatement and CreditCardStatement, I would create an abstract Statement class as a base class for these two classes as shown in Figure 2. In this way, I would be using the Concrete Table Inheritance or Table per Concrete Class Strategy of JPA 2.0.
Figure 2. Domain Model
There is a disadvantage of using this database design. The tables AccountStatement and CreditCardStatement are not related to each other. We can create records in these tables using the same primary keys. For e.g. we can have an AccountStatement record with the primary key value 1002 and also a CreditCardStatement record with the same primary key value 1002. However, when we use the domain model in Figure 2 logically we should not have more than one Statement objects with the same primary key value. Thus, primary key management is one of the disadvantages of using this strategy.
Moreover, the Table per Concrete Class Strategy has poor support for polymorphic queries and its support is optional in JPA 2.0 which means that not all persistence providers provide its support.
Thus, a better way to design this database is to use a Statement table which contains the common attributes. When we do this database design first, we can end up using a “has a” relationship as shown in Figure 3. Note that the AccountStatement and CreditCardStatement tables contain a primary key as well as a foreign key to the Statement table.
Figure 3. Database design which uses a “has a” relationship
The domain model for this database design is shown in Figure 4. You can see that the inheritance is not used in this domain model design as it includes a statement property within the subclasses. Thus, the domain model also uses a “has a” relationship.
Figure 4. Domain Model for database design using “has a” relationship
To use a “is a” relationship, we need to use the domain model design given in Figure 5.
Figure 5. Using “is a” relationship in domain model
The Joined Subclass Strategy of JPA allows the use of “is a” relationship in domain model as well as the underlying database design. The database design which uses the “is a” relationship is given in Figure 6. The statementId column in both AccountStatement and CreditCardStatement tables is as a primary key as well as the foreign key to the Statement table.
Figure 6. Database design using “is a” relationship
To summarize, for the correct use of inheritance in domain model, the database design also needs to use inheritance. Since RDBMS does not support inheritance, we could end up using the “has a” relationship if we do database design first followed by the domain model design. If we do the domain model design first followed by the database design, the chances of using inheritance accurately are high. Though you can follow any approach, you should consciously design your database to use inheritance if you do the database design first.
