Solution: Model the Subtypes Using Inheritance

Whenever EAV seems like the right design, we should take a second look before we implement it. In most cases, we will probably find that our project’s data can be modeled in a traditional table design more easily and with greater assurance of data integrity.

There are several ways to store such data without using EAV. Most solutions work best when we have a finite number of subtypes, and we know the attribute of each subtype. Our choice of the best solution depends on how we intend to query the data, so we should decide the design on a case-by-case basis. Let’s discuss these first. There are three types of inheritance we are going to discuss, namely:

• Single Table Inheritance
• Concrete Table Inheritance
• Class Table Inheritance

Single Table Inheritance

The simplest design is to store all related types in one table, with distinct columns for every attribute that exists in any type. Under this design, we use one attribute to define the subtype of a given row. In our example, this attribute is called issue_type. Some attributes are common to all subtypes. Many attributes are subtype-specific, and these columns must be given a null value in every such row that stores an object for which the attribute does not apply; this would result in the columns with non-null values becoming sparse.

The name for this design comes from Martin Fowler’s book Patterns of Enterprise Application ArchitectureMartin Fowler. Patterns of Enterprise Application Architecture. Addison Wesley Longman, Reading, MA, 2003..