What is Single Table Inheritance?

What Single Table Inheritance Means

Single Table Inheritance (STI) is a database design pattern that allows you to store an entire hierarchy of related object-oriented classes within a single relational database table. This approach uses a special ‘discriminator’ column to identify which specific subclass each row represents.

Essentially, instead of creating separate tables for each class in your inheritance structure, you consolidate all of them into one large table. Imagine you have a base class like Vehicle, and subclasses like Car and Motorcycle. With STI, you’d have just one vehicles table, and a column—often named type or vehicle_type—would tell you if a given row is a Car, a Motorcycle, or another type of Vehicle.

Single Table Inheritance represents an inheritance hierarchy of classes as a single table that has columns for all the fields of the various classes. Martin Fowler’s PofEAA

How Single Table Inheritance Works in Practice

The core of Single Table Inheritance lies in its unified table structure. Here’s a closer look at how it operates:

• One Table for All: All parent and child classes share a single database table. This table contains columns for every possible attribute that any class in the hierarchy might possess.
• The Discriminator Column: A crucial column, often named type, kind, or category, is added to the table. This column stores a string or integer value that explicitly tells you which subclass a particular row belongs to. For instance, if your table stores Employees, and you have subclasses FullTimeEmployee and PartTimeEmployee, the discriminator column might hold ‘FullTimeEmployee’ or ‘PartTimeEmployee’ for each record.
• Handling Attributes: If a subclass doesn’t use a particular attribute that another subclass does, the corresponding column for that row in the single table will simply hold a NULL value. For example, if Car has a number_of_doors column but Motorcycle doesn’t, motorcycle records will have NULL in the number_of_doors column.

When you retrieve data, your application code (often with the help of an Object-Relational Mapper or ORM) uses this discriminator column to instantiate the correct object type. This means if you query for an Employee and the type column says ‘FullTimeEmployee’, your ORM will return a FullTimeEmployee object, even though all the data came from the generic employees table.

When Single Table Inheritance is a Good Choice

I find that STI shines in specific scenarios, particularly when simplicity and performance for certain queries are priorities:

• Shallow Hierarchies: If your inheritance hierarchy is not very deep and has only a few subclasses, STI can be quite manageable. The more complex your hierarchy, the more unwieldy the single table can become.
• Similar Attributes: STI works best when your subclasses share a significant number of common attributes. If each subclass has vastly different properties, you’ll end up with many NULL columns, which I’ll discuss as a drawback.
• Small Number of Subclasses: Having just two or three distinct types inheriting from a parent class is a sweet spot for STI. As the number of subclasses grows, the table’s width increases, potentially impacting performance and readability.
• Frequent Queries Across All Types: If your application frequently needs to fetch all objects of a base type (e.g., all Vehicles, regardless of whether they are cars or motorcycles), STI is very efficient. There are no expensive database joins required, as all the data is in one place.
• ORM Support: Many popular ORMs, such as Ruby on Rails’ ActiveRecord, have solid, built-in support for STI, making its implementation relatively straightforward and abstracting away some of the underlying database complexities.

The Downsides of Using Single Table Inheritance

While STI offers clear benefits, it also comes with notable drawbacks that can become problematic as your application scales or evolves:

• Sparse Tables and Null Values: This is one of the most significant concerns. As mentioned, if subclasses have unique attributes, the single table will contain columns for all of them. Rows belonging to a subclass that doesn’t use a specific column will have NULL values in that column. Over time, this can lead to a very wide table with many empty cells, potentially wasting storage space and making the schema harder to comprehend.
• Table Bloat: A wide table with many columns, especially if some are large text fields or arrays, can become very large. This “table bloat” can impact query performance, as the database needs to read more data from disk, even if much of it is NULL or irrelevant to a specific object type.
• Schema Evolution Challenges: Adding a new attribute to any subclass means you must add a new column to the single shared table. In a large production database, altering a table can be a slow, resource-intensive operation that might require downtime or careful planning. This can hinder agile development if your object model changes frequently.
• Difficulty with Constraints: Enforcing database-level constraints like NOT NULL can be tricky. You often cannot mark a column as NOT NULL if it’s only relevant to a subset of the rows (i.e., only one subclass uses it). This can push validation logic up to the application layer, potentially compromising data integrity at the database level.
• Readability and Maintainability: A single table with dozens of columns, many of which are NULL for most rows, can be confusing for developers and database administrators alike. Understanding which columns apply to which object type requires consulting application code or documentation, rather than inferring from the database schema itself.

I’ve seen projects where STI was initially chosen for its simplicity, only to face significant performance and maintenance headaches years later due to these issues. It’s a pattern that requires careful consideration of future growth.

Alternatives to Single Table Inheritance

If Single Table Inheritance doesn’t quite fit your needs, or if you anticipate the drawbacks outweighing the benefits for your project, there are other common database inheritance strategies:

• Class Table Inheritance (CTI): This approach uses one table for the base class and separate tables for each subclass. The subclass tables contain only their specific attributes and a foreign key referencing the base class table. This results in a more normalized schema with fewer NULL values but requires database joins to reconstruct a complete object.
• Concrete Table Inheritance: With this strategy, each concrete (non-abstract) subclass gets its own complete table. These tables duplicate common attributes from the parent class. This avoids joins when querying a specific subclass but can lead to data duplication if not managed carefully, and querying across all types in the hierarchy becomes more complex.

Choosing the right strategy depends heavily on your specific application’s data structure, query patterns, and expected evolution. For simple hierarchies with shared attributes, I would consider STI. However, for more complex or rapidly changing object models, I think exploring Class Table Inheritance or even a more denormalized approach with Concrete Table Inheritance might offer better long-term maintainability.