Subtyping and inheritance have been supported in Object-Oriented Programming (OOP), in database languages (such as SQL99), in the XML schema definition language XML Schema, and in other computational languages, in various ways and to different degrees. At its core, subtyping in computational languages is about defining type hierarchies and the inheritance of features: mainly properties and methods in OOP; table columns and constraints in SQL99; elements, attributes and constraints in XML Schema.
In general, it is desirable to have support for multiple classification and multiple inheritance in type hierarchies. Both language features are closely related and are considered to be advanced features, which may not be needed in many applications or can be dealt with by using workarounds.
Multiple classification means that an object has more than one direct type. This is mainly
the case when an object plays multiple roles at the same time, and therefore directly
instantiates multiple classes defining these roles. Multiple inheritance is typically also
related to role classes. For instance, a student assistant is a person playing both the role
of a student and the role of an academic staff member, so a corresponding OOP class
StudentAssistant inherits from both role classes
AcademicStaffMember. In a similar way, in our example model above, an
AmphibianVehicle inherits from both role classes
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 pre-defined instance-level property for retrieving the direct type of an object (or its direct types, if multiple classification is allowed);
a pre-defined type-level property for retrieving the direct supertype of a type (or its direct supertypes, if multiple inheritance is allowed).
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:
Single Table Inheritance is the simplest approach, where the entire class hierarchy is represented with a single table, containing columns for all attributes of the root class and of all its subclasses, and named after the name of the root class.
Joined Tables Inheritance is a more logical approach, where each subclass is represented by a corresponding subtable connected to the supertable via its primary key referencing the primary key of the supertable.
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 14.1 above. The
second example is the class hierarchy of the
Author, shown in the class
diagram in Figure 14.8
Consider the single-level class hierarchy shown in Figure 14.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 14.9 below.
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
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 14.8 above. With only three additional attributes defined
in the subclasses
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 14.10 below.
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.
In a more realistic model, the subclasses of
Person shown in Figure 14.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 14.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.
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.