Class Hierarchies with SQL

A standard DBMS stores information, such as objects, in the rows of the tables, conceived as set-theoretic relations in classical relational database systems. The relational database language SQL is used to define, populate, update, and query these kinds of databases. However, there are also simpler data storage techniques that don’t support SQL, but allow us to store data in table rows. For example, key-value storage systems, such as JavaScript’s Local Storage API, allow us to store a serialization of a JavaScript entity table as a key. This serialization, which is a map of entity records, is saved as a string value associated with the table name.

While there is no support for subtyping and inheritance in the classical version of SQL (SQL92), this changes in SQL99. However, the subtyping-related language elements of SQL99 have only been implemented in some DBMSs, for example, in the open-source DBMS PostgreSQL. As a result, we can’t use SQL99 features for subtyping when we make design models that can be implemented with various frameworks with various SQL DBMSs (including MySQL and SQLite). Instead, we have to model inheritance hierarchies in database design models using plain tables and foreign key dependencies. Object-Relational-Mapping frameworks such as JPA Providers (like Hibernate), Microsoft’s Entity Framework, or the Active Record approach of the Rails framework are well-known for this mapping of class hierarchies to relational tables.

Alternative approaches

A class hierarchy with database tables can be represented in three additional ways:

1. Single Table Inheritance (STI) is the simplest approach. The entire class hierarchy is represented by a single table that contains columns for all attributes of the root class and all of its subclasses. It’s named after the name of the root class.

2. Table per Class Inheritance (TCI) is an approach where each class of the hierarchy is represented by a corresponding table that also contains columns for inherited properties, repeating the columns of the tables that represent its superclasses.

3. Joined Tables Inheritance (JTI) is a more logical approach. Each segment subclass is represented by a corresponding table (subtable) which is connected to the table that represents its superclass (supertable). The primary key of the subtable references the primary key of the supertable, so that the inherited properties of the superclass are not represented as columns in subtables.

Notice that the Single Table Inheritance approach is closely related to the Class Hierarchy Merge design pattern. Whenever we apply this design pattern to the design model, or we refactor the design model according to this design pattern, the class hierarchies concerned (their subclasses) are eliminated in the design. As a result, they’ve also been eliminated in the data model to be coded in the form of class definitions in the application’s model layer, so there’s no longer any need to map class hierarchies to single tables. Otherwise, the design model contains a class hierarchy that’s implemented with a corresponding class hierarchy in the application’s model layer, which is then mapped to the database tables with the help of the Single Table Inheritance approach.

We illustrate the use of these approaches with the help of two simple examples. The first example is the Book class hierarchy: