Modeling Inheritance in Relational Databases Correctly

1. The problem: OOP inheritance meets the relational model

Object-oriented languages have inheritance as a native language construct: a base class "PaymentMethod" defines shared fields, "CreditCard" and "BankTransfer" inherit from it and add their own fields. The relational model has no such concept, it only has tables with a fixed set of columns. Every mapping of database inheritance is therefore a deliberate decision about how a class hierarchy is projected onto one or several tables, with different consequences for storage, query effort and referential integrity.

The challenge becomes visible as soon as several subtypes of a base class carry different additional attributes. A credit card payment needs card_number and expiry_date, a bank transfer needs iban and bic. If all subtypes are combined into one table, columns inevitably appear that are meaningless and empty for part of the rows. If each subtype is stored in its own table, the ability to capture all payment methods together through a single query is lost, without merging several tables.

Three established patterns resolve this tension in different ways: Single Table Inheritance bundles everything into one table, Class Table Inheritance distributes shared and specific fields across several linked tables, Concrete Table Inheritance duplicates shared fields into each subtype table. Each of these patterns for database inheritance has a clear scope of application, which the following sections show in detail.

2. Single Table Inheritance in detail

Single Table Inheritance, STI for short, stores the entire class hierarchy in exactly one table. A discriminator column, usually called "type" or "payment_type", records which subtype a given row represents. All columns of all subtypes exist simultaneously in the table, with each row only filling the columns that belong to its own subtype, all others remain NULL. This form of database inheritance is the simplest implementation and works well when the number of subtype-specific columns stays small and the hierarchy rarely changes.

The big advantage of STI is query simplicity: a single SELECT without a JOIN returns all payment methods regardless of subtype. That is especially valuable for list views and reports that operate across the entire hierarchy. The drawback appears once subtypes carry many different fields: the table becomes wide, many columns are NULL for most rows, and CHECK constraints that ensure the right fields are set for a given type quickly become unwieldy.

-- Single Table Inheritance: all subtypes in one table
CREATE TABLE payment_method (
    payment_method_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    customer_id         BIGINT UNSIGNED NOT NULL,
    payment_type         VARCHAR(20) NOT NULL,   -- discriminator column
    -- fields shared by all subtypes
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- fields specific to credit_card
    card_number          VARCHAR(19),
    card_expiry          DATE,
    -- fields specific to bank_transfer
    iban                 VARCHAR(34),
    bic                  VARCHAR(11),
    CONSTRAINT chk_payment_type
        CHECK (payment_type IN ('credit_card', 'bank_transfer')),
    CONSTRAINT chk_credit_card_fields
        CHECK (payment_type <> 'credit_card'
               OR (card_number IS NOT NULL AND card_expiry IS NOT NULL)),
    CONSTRAINT chk_bank_transfer_fields
        CHECK (payment_type <> 'bank_transfer'
               OR (iban IS NOT NULL AND bic IS NOT NULL))
);

3. Class Table Inheritance in detail

Class Table Inheritance, CTI for short, takes the opposite route: a base table contains all shared fields, each subtype has its own table with only the specific fields, linked through a foreign key that is at the same time the primary key of the subtype table. This form of database inheritance most closely mirrors the object-oriented structure itself and consistently avoids NULL values, because each table only contains columns that actually make sense for its rows.

The drawback of CTI is query effort: reading a complete credit card payment with all base fields requires a JOIN between "payment_method" and "credit_card_payment". For list views that need to show all payment methods regardless of subtype, several JOINs combined with UNION are necessary, one per subtype, which becomes increasingly expensive as the number of subtypes grows. CTI is therefore especially well suited when subtypes have many fields of their own and are mostly queried individually rather than jointly across the hierarchy.

-- Class Table Inheritance: base table plus one table per subtype
CREATE TABLE payment_method (
    payment_method_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    customer_id         BIGINT UNSIGNED NOT NULL,
    payment_type         VARCHAR(20) NOT NULL,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE credit_card_payment (
    payment_method_id  BIGINT UNSIGNED PRIMARY KEY,
    card_number          VARCHAR(19) NOT NULL,
    card_expiry          DATE NOT NULL,
    CONSTRAINT fk_credit_card_payment
        FOREIGN KEY (payment_method_id)
        REFERENCES payment_method (payment_method_id)
        ON DELETE CASCADE
);

CREATE TABLE bank_transfer_payment (
    payment_method_id  BIGINT UNSIGNED PRIMARY KEY,
    iban                 VARCHAR(34) NOT NULL,
    bic                  VARCHAR(11) NOT NULL,
    CONSTRAINT fk_bank_transfer_payment
        FOREIGN KEY (payment_method_id)
        REFERENCES payment_method (payment_method_id)
        ON DELETE CASCADE
);

4. Concrete Table Inheritance in detail

Concrete Table Inheritance does away with a shared base table entirely. Each subtype gets its own standalone table that contains both shared and specific fields, duplicated across all subtype tables. This form of database inheritance eliminates any JOIN overhead for accessing a single subtype, because every table is complete in itself. For performance-critical access to exactly one known subtype, this is the fastest variant.

The price for that is redundancy at the schema level: every change to a shared field, for example a new "notes" column, has to be applied to every subtype table individually. A global foreign key meant to reference "any payment method regardless of subtype" cannot be represented cleanly without an additional construct such as an overarching ID table, because no shared primary key space exists. Concrete Table Inheritance is therefore best suited when subtypes are rarely referenced across the hierarchy and the number of subtypes is small and stable.

-- Concrete Table Inheritance: shared fields duplicated per subtype
CREATE TABLE credit_card_payment (
    credit_card_payment_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    customer_id               BIGINT UNSIGNED NOT NULL,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    card_number                 VARCHAR(19) NOT NULL,
    card_expiry                 DATE NOT NULL
);

CREATE TABLE bank_transfer_payment (
    bank_transfer_payment_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    customer_id                 BIGINT UNSIGNED NOT NULL,
    created_at                   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    iban                         VARCHAR(34) NOT NULL,
    bic                          VARCHAR(11) NOT NULL
);

5. Using discriminator columns and constraints correctly

The discriminator column is the central control element in Single Table Inheritance and deserves special care. A free-form VARCHAR invites typos, which is why a CHECK constraint with a fixed value list, as shown in the first example, is mandatory. For databases that support native ENUM types, such as PostgreSQL with CREATE TYPE or MySQL with the ENUM column type, the same protection can be achieved through the column type itself, which makes the intent in the schema even clearer.

A discriminator in the base table also makes sense for Class Table Inheritance, even if it is not strictly required for referential integrity. It allows determining the concrete subtype of a record without having to search every subtype table with LEFT JOIN. This column should be kept consistent with the actually existing subtype row through a trigger or an application layer, since the database does not automatically guarantee this consistency between the discriminator value and the actually populated subtype table without additional mechanisms.

-- PostgreSQL: native ENUM type instead of a plain VARCHAR with CHECK
CREATE TYPE payment_type_enum AS ENUM ('credit_card', 'bank_transfer');

ALTER TABLE payment_method
    ALTER COLUMN payment_type TYPE payment_type_enum
    USING payment_type::payment_type_enum;

-- MySQL: ENUM column type achieves the same protection
CREATE TABLE payment_method (
    payment_method_id  BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    payment_type         ENUM('credit_card', 'bank_transfer') NOT NULL
);

6. NULL handling with Single Table Inheritance

The biggest practical criticism of Single Table Inheritance is the amount of NULL values that grows with every additional subtype. With five subtypes carrying three specific fields each, every row carries at worst twelve NULL values and only three actually populated values. This pattern of database inheritance does not waste any relevant storage, because modern database systems encode NULL values efficiently, but it significantly hurts schema readability, because the table definition alone does not reveal which fields actually belong together.

CHECK constraints, as shown in the STI example, mitigate the problem by at least preventing a row with payment_type equal to credit_card from being stored without card_number. These constraints, however, grow linearly with the number of subtypes and become unwieldy quickly beyond four or five subtypes. In such cases, switching to Class Table Inheritance is usually the better path, even though it requires additional JOINs on read.

7. Queries and performance per strategy

With Single Table Inheritance, any query spanning all subtypes is a simple SELECT without a JOIN, which is the cheapest variant both for the developer and for the query planner. With Class Table Inheritance, the same query needs either several LEFT JOINs, one per subtype table, or a UNION across several targeted JOINs. Both variants are noticeably more expensive than the simple SELECT with STI, especially once the hierarchy covers more than three or four subtypes.

With Concrete Table Inheritance, access to a single known subtype is fastest, because no shared table is involved. A query spanning the entire hierarchy, on the other hand, requires a UNION ALL across all subtype tables, where each table brings its own indexes and the query planner must optimize every sub-query separately. For reporting queries across the entire hierarchy, this is typically the slowest of the three strategies.

-- Querying across the full hierarchy with Class Table Inheritance
SELECT
    pm.payment_method_id,
    pm.payment_type,
    cc.card_number,
    bt.iban
FROM payment_method pm
LEFT JOIN credit_card_payment cc ON cc.payment_method_id = pm.payment_method_id
LEFT JOIN bank_transfer_payment bt ON bt.payment_method_id = pm.payment_method_id
WHERE pm.customer_id = 100;

-- Same query with Concrete Table Inheritance, via UNION ALL
SELECT payment_method_id, 'credit_card' AS payment_type, card_number, NULL AS iban
FROM credit_card_payment WHERE customer_id = 100
UNION ALL
SELECT payment_method_id, 'bank_transfer' AS payment_type, NULL AS card_number, iban
FROM bank_transfer_payment WHERE customer_id = 100;

8. Migrating between inheritance strategies

Switching inheritance strategies while a system is live is effortful, but not unusual once a system grows and the original choice no longer fits. The typical path leads from Single Table Inheritance to Class Table Inheritance, when the number of subtypes increases and the STI table becomes too wide. The migration proceeds in steps: first the new subtype tables are created, then the data from the wide table is copied into the matching subtype table based on the discriminator value, only afterwards are the no longer needed columns removed from the base table.

Important for any migration of database inheritance is that the application layer must support both structures simultaneously during the transition period, usually through a feature flag or an abstracting repository layer. The final removal of the old columns should only happen after the new structure has been validated in production, ideally with a separate deployment step that keeps the old structure read-only before it is removed entirely.

9. Decision criteria: which pattern when

The choice of the right strategy for database inheritance depends on three factors: the number of subtypes, the number of subtype-specific fields, and the frequency of cross-hierarchy queries. Single Table Inheritance fits best with few subtypes carrying few additional fields and frequent queries across the entire hierarchy. Class Table Inheritance fits many subtypes with many fields of their own, where avoiding NULLs and data integrity matter more than query simplicity.

Concrete Table Inheritance is the right choice when subtypes are practically never queried together and maximum read speed for individual subtypes is the priority, for instance with highly specialized event log tables. In practice, a hybrid approach is often chosen: the subtypes most frequently queried together stay in an STI table, rare or strongly divergent subtypes get their own class table structure.

10. Summary

Database inheritance can be modeled with three established patterns, each making different compromises. Single Table Inheritance is simple to implement and query, but pays for that with a growing amount of NULL values. Class Table Inheritance consistently avoids NULL values, but requires JOINs for cross-hierarchy queries. Concrete Table Inheritance delivers the fastest access to individual subtypes, but duplicates shared fields across all subtype tables and complicates schema maintenance.

None of the three strategies is universally superior, the right choice depends on the number and similarity of subtypes as well as the typical query pattern. Anyone who plans database inheritance deliberately from the start, instead of letting it grow by accident, avoids costly later migrations between strategies and keeps a schema that stays both readable and performant.

11. FAQ: Modeling Inheritance in Relational Databases Correctly