Single table inheritance is the simplest of several ways to design SQL tables that reflect a class/subclass or generalization/specialization relationship.
Single table inheritance is the simplest of several ways to design SQL tables that reflect a class/subclass or generalization/specialization relationship.
Inheritance is a central concept in object modeling, and is dealt with in class/subclass models. It is well understood by people who build object oriented systems. The parallel concept in ER (Entity-Relationship) modeling is called generalization/specialization. Unfortunately, many introductions to ER modeling do not present generalization/specialization, leaving the beginner to reinvent the concept on their own.
SQL, as such, has no mechanism for implementing inheritance. There are several design techniques for mimicking the effects of inheritance in SQL tables. The simplest of these techniques is called single table inheritance.
In single table inheritance, a single table is used to retain data that pertains to either the superclass or any of its subclasses. Each attribute will have its own column, and each instance will have its own row.
The result of this design is that all data about any member of the class can be obtained without doing any joins. If the intersection of a given column and a given row is not applicable, it is left as an SQL NULL.
SQL NULLS do result in slower retrieval of the rows that contain them, but this is generally offset by not having to do joins. SQL NULLS do increase the amount of space needed to store rows that contain them, but this is generally a secondary consideration.
Where NULLS can be problematic is when they appear in Boolean comparisons like equality tests in WHERE clauses. In SQL a boolean test can result in three possibilities: TRUE, FALSE, or UNKNOWN. If either side or both sides in a comparison are NULL, the result is UNKNOWN. If a test for equality yields UNKNOWN, the same test for inequality will also yield UNKNOWN. This can be baffling to people who are used to two valued logic. The use of nullable columns in WHERE clauses has to be considered very carefully, in order to avoid unexpected results.
Another issue is that it can be difficult to tell which subclass a given row belongs to. For this reason, a separate column, often called EntityType (e.g. VehicleType), is used to indicate subclass membership explicitly.
In complex situations, there are two alternatives to single table inheritance. One is called class table inheritance, which has its own tag: class-table-inheritance. another is called concrete table inheritance.
