The minimum level of support for subtyping in OOP, as provided, for instance, by Java and C#, allows defining inheritance of properties and methods in single-inheritance hierarchies, which can be inspected with the help of an is-instance-of predicate that allows testing if a class is the direct or an indirect type of an object. In addition, it is desirable to be able to inspect inheritance hierarchies with the help of

A special case of an OOP language is JavaScript, which does not (yet) have an explicit language element for classes, but only for constructors. Due to its dynamic programming features, JavaScript allows using various code patterns for implementing classes, subtyping and inheritance (as we discuss in the next section on JavaScript).

A standard DBMS stores information (objects) in the rows of tables, which have been conceived as set-theoretic relations in classical relational database systems. The relational database language SQL is used for defining, populating, updating and querying such databases. But there are also simpler data storage techniques that allow to store data in the form of table rows, but do not support SQL. In particular, key-value storage systems, such as JavaScript's Local Storage API, allow storing a serialization of a JSON table (a map of records) as the string value associated with the table name as a key.

While in the classical, and still dominating, version of SQL (SQL92) there is no support for subtyping and inheritance, this has been changed in SQL99. However, the subtyping-related language elements of SQL99 have only been implemented in some DBMS, for instance in the open source DBMS PostgreSQL. As a consequence, for making a design model that can be implemented with various frameworks using various SQL DBMSs (including weaker technologies such as MySQL and SQLite), we cannot use the SQL99 features for subtyping, but have to model inheritance hierarchies in database design models by means of plain tables and foreign key dependencies. This mapping from class hierarchies to relational tables (and back) is the business of Object-Relational-Mapping frameworks such as Hibernate (or any other JPA Provider) or the Active Record approach of the Rails framework.

There are essentially two alternative approaches how to represent a class hierarchy with relational tables:

Notice that the Single Table Inheritance approach is closely related to the Class Hierarchy Merge design pattern discussed in Section 5 above. Whenever this design pattern has already been applied in the design model, or the design model has already been re-factored according to this design pattern, the class hierarchies concerned (their subclasses) have been eliminated in the design, and consequently also in the data model to be encoded in the form of class definitions in the app's model layer, so there is no need anymore to map class hierarchies to database tables. Otherwise, when the Class Hierarchy Merge design pattern does not get applied, we would get a corresponding class hierarchy in the app's model layer, and we would have to map it to database tables with the help of the Single Table Inheritance approach.

We illustrate both the Single Table Inheritance approach and the Joined Tables Inheritance with the help of two simple examples. The first example is the Book class hierarchy, which is shown in Figure 17.1 above. The second example is the class hierarchy of the Person roles Employee, Manager and Author, shown in the class diagram in Figure 17.8 below.

Figure 17.8. A class model with a Person roles hierarchy

6.2.1. Single Table Inheritance

Consider the single-level class hierarchy shown in Figure 17.1 above, which is an incomplete disjoint segmentation of the class Book, as the design for the model classes of an MVC app. In such a case, whenever we have a model class hierarchy with only one level (or only a few levels) of subtyping and each subtype has only one (or a few) additional properties, it's preferable to use Single Table Inheritance, so we model a single table containing columns for all attributes such that the columns representing additional attributes of subclasses are optional, as shown in the SQL table model in Figure 17.9 below.

Figure 17.9. An SQL table model with a single table representing the Book class hierarchy

Notice that it is good practice to add a special discriminator column for representing the category of each row corresponding to the subclass instantiated by the represented object. Such a column would normally be string-valued, but constrained to one of the names of the subclasses. If the DBMS supports enumerations, it could also be enumeration-valued. We use the name category for the discriminator column.

Based on the category of a book, we have to enforce that if and only if it is "TextBook", its attribute subjectArea has a value, and if and only if it is "Biography", its attribute about has a value. This implied constraint is expressed in the invariant box attached to the Book table class in the class diagram above, where the logical operator keyword "IFF" represents the logical equivalence operator "if and only if". It needs to be implemented in the database, e.g., with an SQL table CHECK clause or with SQL triggers.

Consider the class hierarchy shown in Figure 17.8 above. With only three additional attributes defined in the subclasses Employee, Manager and Author, this class hierarchy could again be implemented with the Single Table Inheritance approach. In the SQL table model, we can express this as shown in Figure 17.10 below.

Figure 17.10. An SQL table model with a single table representing the Person roles hierarchy

In the case of a multi-level class hierarchy where the subclasses have little in common, the Single Table Inheritance approach does not lead to a good representation.

6.2.2. Joined Tables Inheritance

In a more realistic model, the subclasses of Person shown in Figure 17.8 above would have many more attributes, so the Single Table Inheritance approach would be no longer feasible, and the Joined Tables Inheritance approach would be needed. In this approach we get the SQL data model shown in Figure 17.11 below. This SQL table model connects subtables to their supertables by defining their primary key attribute(s) to be at the same time a foreign key referencing their supertable. Notice that foreign keys are visuallized in the form of UML dependency arrows stereotyped with «fkey» and annotated at their source table side with the name of the foreign key column.

Figure 17.11. An SQL table model with the table Person as the root of a table hierarchy

The main disadvantage of the Joined Tables Inheritance approach is that for querying any subclass join queries are required, which may create a performance problem.