Consider the single-level class hierarchy shown in Figure 12.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 a few additional properties, it's preferable to use STI, so we model a single table containing columns for all attributes such that the columns representing additional attributes of segment subclasses ("segment attributes") are optional, as shown in the SQL table model in Figure 12.9 below.

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

It is a common approach 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, which, in the case of our Book class hierarchy example, has a frozen value constraint since the textbook-biography segmentation is rigid.

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.

When the given segmentation is disjoint, a single-valued enumeration attribute category is used for representing the information to which subclass an instance belongs. Otherwise, if it is non-disjoint, a multi-valued enumeration attribute categories is used for representing the information to which subclasses an instance belongs. Such an attribute can be implemented in SQL by defining a string-valued column for representing a set of enumeration codes or labels as corresponding string concatenations.

Consider the class hierarchy shown in Figure 12.8 above. With only three additional attributes defined in the subclasses Employee, Manager and Author, this class hierarchy can again be mapped with the STI approach, as shown in the SQL table model Figure 12.10 below.

Figure 12.10. An STI table model representing the Person roles hierarchy

Notice that now the discriminator column categories is multi-valued, since the segmentation of Person is not disjoint, but overlapping, implying that a Person object may belong to several categories. Notice also that, since a role segmentation (like Employee, Manager, Author) is not rigid, the discriminator column categories does not have a frozen value constraint.

An example of an admissible population for this model is the following:

Notice that the Person table contains four different types of people:

Pros of the STI approach: It leads to a faithful representation of the subtype relationships expressed in the original class hierarchy; in particular, any row representing a subclass instance (an employee, manager or author) also represents a superclass instance (a person).

Cons: (1) In the case of a multi-level class hierarchy where the subclasses have little in common, the STI approach does not lead to a good representation. (2) The structure of the given class hierarchy in terms of its elements (classes) is only implicitly preserved.

In a more realistic model, the subclasses of Person shown in Figure 12.8 above would have many more attributes, so the STI approach would be no longer feasible. In the TCI approach we get the SQL table model shown in Figure 12.11 below. A TCI model represents each concrete class of the class hierarchy as a table, such that each segment subclass is represented by a table that also contains columns for inherited properties, thus repeating the columns of the table that represents the superclass.

Figure 12.11. A TCI table model representing the Person roles hierarchy

A TCI table model can be derived from the information design model by performing the following steps:

Each table would only be populated with rows corresponding to the direct instances of the represented class. An example of an admissible population for this model is the following:

Pros of the TCI approach: (1) The structure of the given class hierarchy in terms of its elements (classes) is explicitly preserved. (2) When the segmentations of the given class hierarchy are disjoint, TCI leads to memory-efficient non-redundant storage.

Cons: (1) The TCI approach does not yield a faithful representation of the subtype relationships expressed in the original class hierarchy. In particular, for any row representing a subclass instance (an employee, manager or author) there is no information that it represents a superclass instance (a person). Thus, the TCI database schema does not inform about the represented subtype relationships; rather, this meta-information, which is kept in the app's class model, is de-coupled from the database. (2) The TCI approach requires repeating column definitions, which is a form of schema redundancy. (3) The TCI approach may imply data redundancy whenever the segment subclasses overlap. In our example, authors can also be employees, so for any person in the overlap, we would need to duplicate the data storage for all columns representing properties of the superclass (in our example, this only concerns the property name).

For avoiding the data redundancy problem of TCI in the case of overlapping segmentations, we could take the JTI approach as exemplified in the SQL table model shown in Figure 12.12 below. This model connects tables representing subclasses (subtables) to tables representing their superclasses (supertables) by defining their primary key column(s) to be at the same time a foreign key referencing their supertable's primary key. Notice that foreign keys are visualized 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 12.12. A JTI table model representing the Person roles hierarchy

An example of an admissible population for this model is the following:

Pros of the JTI approach: (1) Subtyping relationships and the structure of class hierarchies are explicitly preserved. (2) Data redundancy in the case of overlapping segmentations is avoided.

Cons: (1) The main disadvantage of the JTI approach is that for querying a subclass, join queries (for joining the segregated entity data) are required, which may create performance issues.