What is Single Table Inheritance?

Single Table Inheritance is a database design pattern that maps an entire object-oriented class hierarchy to a single database table. A special discriminator column, typically named type, identifies which subclass a row represents while attributes exclusive to other subclasses are stored as null values.

Instead of creating separate database tables for every class in your domain model, Single Table Inheritance (STI) consolidates all parent and child class fields into one master table. Imagine a software domain with a parent class named Vehicle and child subclasses named Car, Motorcycle, and Truck. Using STI, you create a single vehicles table. If a Car has four doors while a Motorcycle has a handlebar type, both attributes live as columns in that same table. When reading or writing records, your application reads the discriminator column to determine which class instance to create.

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 does single table inheritance work?

Single Table Inheritance relies on three core mechanisms: a unified schema, a discriminator column, and null column handling.

1. Unified schema

All attributes across the entire class hierarchy are defined inside one database table. If your parent class is User and your child classes are AdminUser, Customer, and Vendor, every property from all four classes exists as a column in the users table.

2. The discriminator column

The discriminator column—usually named type, class_name, or kind—stores the precise identifier or class name for each record. When an Object-Relational Mapper (ORM) fetches a row where the type column contains the value Customer, it automatically instantiates a Customer object rather than a generic User object.

3. Attribute handling and null values

Because every subclass shares one table, attributes specific to a single child class are left empty for all other subclasses. For instance, if Customer has a loyalty_points column and AdminUser has a permissions_level column, an admin record stores a NULL value inside loyalty_points, and a customer record stores a NULL value inside permissions_level.

Step-by-step example of implementing single table inheritance

To understand how STI operates in application code and relational databases, consider an e-commerce platform that handles multiple types of payment methods: credit cards, bank transfers, and digital wallets.

Step 1: Create the unified database schema

In your database, build a single table named payment_methods. Include common fields alongside subclass-specific fields and the discriminator column.

• id: Primary key (integer)
• type: Discriminator column (string)
• account_holder_name: Shared column (string)
• card_number: Specific to credit card payments (string, nullable)
• expiration_date: Specific to credit card payments (date, nullable)
• bank_account_number: Specific to bank transfers (string, nullable)
• routing_number: Specific to bank transfers (string, nullable)
• wallet_email: Specific to digital wallets (string, nullable)

Step 2: Map the parent class in application code

Define the base PaymentMethod class in your application framework. The base class links to the payment_methods table and specifies shared logic like validation for account_holder_name.

Step 3: Define child subclasses

Create individual child classes that inherit directly from PaymentMethod:

• CreditCardPayment < PaymentMethod
• BankTransferPayment < PaymentMethod
• WalletPayment < PaymentMethod

When you call CreditCardPayment.create(account_holder_name: "Jane Doe", card_number: "4111..."), the framework automatically writes 'CreditCardPayment' into the type column.

Step 4: Query data polymorphically

When you query the base class (e.g., PaymentMethod.all), the ORM executes a SQL query fetching all rows from payment_methods. It evaluates the type string on each row to instantiate the correct sub-objects automatically.

When you query a child class directly (e.g., CreditCardPayment.all), the ORM automatically appends a WHERE type = 'CreditCardPayment' clause to filter out unrelated rows.

Single table inheritance vs other database inheritance patterns

When evaluating architectural patterns alongside common object-relational mapping approaches, comparing STI to alternatives clarifies when each strategy makes sense.

Class Table Inheritance (CTI) creates a base table storing common attributes, plus individual subclass tables linked by primary keys. CTI maintains full third normal form and supports standard database constraints, but queries across subclasses require heavy JOIN operations.

Concrete Table Inheritance drops the base table entirely. Each concrete subclass gets its own stand-alone table containing both shared and specific columns. This avoids database joins when querying a known subclass, but querying across all entities requires costly UNION queries.

When should you use single table inheritance?

Single Table Inheritance is the right choice under specific application requirements:

• Shallow inheritance hierarchies: You have a parent class with only two to four child subclasses.
• High attribute overlap: The subclasses share 80% or more of their fields, with only a few subclass-specific columns.
• Frequent polymorphic reads: Your application regularly fetches lists containing mixed types (e.g., displaying all account transactions regardless of transaction type).
• Rapid prototyping: Managing migrations on a single table is faster during early product development than coordinating joins across multiple tables.
• Built-in ORM support: Frameworks like Ruby on Rails (ActiveRecord) and Python (SQLAlchemy) support STI natively with minimal configuration.

When is single table inheritance the wrong choice?

STI creates architectural problems when applied in the wrong context. Avoid STI if your project matches these conditions:

• Divergent subclasses: Each subclass introduces dozens of unique fields. The table ends up with scores of sparse columns that hold NULL values for almost every record.
• Strict database integrity requirements: You rely heavily on database engine constraints like NOT NULL or FOREIGN KEY rules to guarantee data validity.
• High write volume across distinct domain types: Placing highly distinct, busy entity types in one table leads to write lock contention, large index sizes, and table bloat.

Common problems with single table inheritance and how to fix them

Working with STI introduces several database-level challenges. Here is how to resolve them.

Problem 1: Inability to use standard NOT NULL constraints

Because subclass-specific columns must remain empty for sibling classes, you cannot apply a standard NOT NULL schema constraint to a child attribute. For example, if a Car requires a number_of_doors, making that column NOT NULL breaks insertions for Motorcycle records.

Fix: Use application-level validation rules inside your subclass models (e.g., validating presence of number_of_doors only inside the Car class). Alternatively, if your database engine supports check constraints (like PostgreSQL or MySQL 8.0+), write conditional check constraints:

ALTER TABLE vehicles ADD CONSTRAINT check_car_doors CHECK (type != 'Car' OR number_of_doors IS NOT NULL);

Problem 2: Table bloat and slow index scans

As row counts reach millions, indexing every column across all subclasses causes index sizes to explode. Scanning indexes becomes slower because non-relevant rows clutter the B-tree index structure.

Fix: Implement partial indexes (filtered indexes) targeted specifically at specific discriminator values. For example:

CREATE INDEX idx_cars_doors ON vehicles (number_of_doors) WHERE type = 'Car';

This keeps the index compact and performant because it ignores rows where type is not Car.

Problem 3: Schema pollution and confusing maintenance

As teams add features, developer visibility drops. It becomes difficult to know which table columns belong to which subclass without inspecting application code.

Fix: Maintain explicit domain documentation and group subclass-specific attributes using database column naming prefixes or ORM attribute groupings. If the number of sparse columns exceeds 10–15 columns, refactor the specific attributes into a separate JSON/JSONB column or transition to Class Table Inheritance.

Sources: Single Table Inheritance

Frequently asked questions

What is the discriminator column in single table inheritance?

The discriminator column is a table field (commonly named type) that stores the specific class or subtype name for each record. The ORM reads this string during database queries to instantiate the corresponding child class in application memory.

Does single table inheritance waste database storage space?

In modern relational databases like PostgreSQL and MySQL, NULL values consume negligible storage space (typically a few bits in a null bitmap). However, table bloat can still occur if large, indexed, or wide string columns are repeatedly left empty across millions of rows.

How do you query a specific subclass in single table inheritance?

Most ORMs handle this automatically when you query the subclass model directly (e.g., Car.all). Under the hood, the ORM generates a standard SQL query filtered by the discriminator column, such as SELECT * FROM vehicles WHERE type = 'Car'.