Mapping Inheritance

The conceptual elegance of EER inheritance hierarchies must eventually be translated into physical database schemas. Unlike object-oriented languages that directly support inheritance, relational databases have no native inheritance construct—tables are flat, relationships are through foreign keys, and polymorphism must be simulated.

This translation from conceptual inheritance to relational tables is one of the most consequential decisions in database design. The mapping strategy you choose affects storage efficiency, query performance, data integrity enforcement, schema evolution flexibility, and application code complexity.

This page presents the three primary strategies for mapping inheritance to relational schemas, analyzes their trade-offs across multiple dimensions, and provides guidance for selecting the right approach for your specific domain and performance requirements. Mastering these mapping patterns is essential for any database professional translating conceptual models into production systems.

The Object-Relational Impedance Mismatch:

Relational databases were not designed with inheritance in mind. A table is simply a set of tuples with a fixed schema—there's no built-in concept of one table being a 'subtype' of another. This creates the famous object-relational impedance mismatch for inheritance.

What Must Be Represented:

When mapping an inheritance hierarchy, the physical schema must capture:

1. Attribute Storage: Where the attributes of each type are physically stored
2. Type Identity: How to determine the specific type of any given instance
3. Relationship Anchoring: Where foreign keys referencing the hierarchy point
4. Polymorphic Access: How to query 'all instances of supertype including subtypes'
5. Type-Specific Access: How to efficiently query just one specific subtype
6. Constraint Enforcement: How to enforce type-specific constraints
7. Integrity Rules: How to maintain referential integrity across the hierarchy

The Three Primary Strategies:

Database designers have developed three fundamental approaches to mapping inheritance:

1. Single Table Hierarchy (STH) All types in the hierarchy are stored in one table. A discriminator column identifies the specific type of each row. Subtype-specific columns are NULL for rows of other types.

2. Table Per Type (TPT) Each type in the hierarchy gets its own table. Tables are linked by primary key (shared identity). Subtype tables only store local attributes; inherited attributes are in supertype tables.

3. Table Per Concrete Class (TPC) Only concrete (instantiable) types get tables. Each table stores all attributes (inherited + local). No tables for abstract supertypes.

Each strategy makes different trade-offs between storage efficiency, query complexity, constraint enforcement, and schema evolution. There is no universally 'best' strategy—the right choice depends on your specific requirements.

Single Table Hierarchy (also called Single Table Inheritance or Table Per Hierarchy) stores all types in one table with a discriminator column that identifies the specific type of each row.

Implementation:

Given the hierarchy:

EMPLOYEE (PersonId, Name, HireDate)
├── MANAGER (Budget, DirectReports) 
├── ENGINEER (ProgrammingLanguage, GitHubHandle)
└── SALES_REP (Territory, QuotaTarget)

STH Schema:

CREATE TABLE Employee (
    -- Identity and discriminator
    PersonId UUID PRIMARY KEY,
    EmployeeType VARCHAR(20) NOT NULL,  -- 'MANAGER', 'ENGINEER', 'SALES_REP'
    
    -- Inherited attributes (from EMPLOYEE)
    Name VARCHAR(100) NOT NULL,
    HireDate DATE NOT NULL,
    
    -- MANAGER-specific attributes
    Budget DECIMAL(15,2),          -- NULL for non-managers
    DirectReports INTEGER,          -- NULL for non-managers
    
    -- ENGINEER-specific attributes
    ProgrammingLanguage VARCHAR(50),-- NULL for non-engineers
    GitHubHandle VARCHAR(50),       -- NULL for non-engineers
    
    -- SALES_REP-specific attributes  
    Territory VARCHAR(100),         -- NULL for non-sales
    QuotaTarget DECIMAL(12,2),      -- NULL for non-sales
    
    -- Type-specific constraints via CHECK
    CONSTRAINT chk_manager CHECK (
        EmployeeType != 'MANAGER' OR 
        (Budget IS NOT NULL AND DirectReports IS NOT NULL)
    ),
    CONSTRAINT chk_engineer CHECK (
        EmployeeType != 'ENGINEER' OR 
        ProgrammingLanguage IS NOT NULL
    ),
    CONSTRAINT chk_sales CHECK (
        EmployeeType != 'SALES_REP' OR 
        (Territory IS NOT NULL AND QuotaTarget IS NOT NULL)
    )
);

Query Patterns with STH:

-- Polymorphic query: all employees
SELECT PersonId, Name, HireDate FROM Employee;

-- Type-specific query: only managers
SELECT PersonId, Name, Budget, DirectReports
FROM Employee
WHERE EmployeeType = 'MANAGER';

-- Type discovery: what type is this employee?
SELECT EmployeeType FROM Employee WHERE PersonId = @id;

When to Use STH:

• Hierarchy has few subtypes (2-5)
• Subtypes have few local attributes (total columns manageable)
• Polymorphic queries are the dominant access pattern
• Query simplicity is prioritized over storage efficiency
• Subtype-specific constraints are simple or can be enforced in application layer

Table Per Type (also called Joined Table Inheritance or Class Table Inheritance) creates a separate table for each type in the hierarchy. Tables are linked by shared primary key, with subtype tables only storing local attributes.

Implementation:

Given the same hierarchy:

EMPLOYEE (PersonId, Name, HireDate)
├── MANAGER (Budget, DirectReports)
├── ENGINEER (ProgrammingLanguage, GitHubHandle)
└── SALES_REP (Territory, QuotaTarget)

TPT Schema:

-- Supertype table
CREATE TABLE Employee (
    PersonId UUID PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    HireDate DATE NOT NULL,
    EmployeeType VARCHAR(20) NOT NULL  -- Optional discriminator
);

-- Subtype tables (linked by PK)
CREATE TABLE Manager (
    PersonId UUID PRIMARY KEY 
        REFERENCES Employee(PersonId) ON DELETE CASCADE,
    Budget DECIMAL(15,2) NOT NULL,
    DirectReports INTEGER NOT NULL
);

CREATE TABLE Engineer (
    PersonId UUID PRIMARY KEY 
        REFERENCES Employee(PersonId) ON DELETE CASCADE,
    ProgrammingLanguage VARCHAR(50) NOT NULL,
    GitHubHandle VARCHAR(50)
);

CREATE TABLE SalesRep (
    PersonId UUID PRIMARY KEY 
        REFERENCES Employee(PersonId) ON DELETE CASCADE,
    Territory VARCHAR(100) NOT NULL,
    QuotaTarget DECIMAL(12,2) NOT NULL
);

Query Patterns with TPT:

-- Polymorphic query: all employees (supertype attributes only)
SELECT PersonId, Name, HireDate FROM Employee;

-- Polymorphic query: all employees with full attributes (requires UNIONs)
SELECT e.PersonId, e.Name, e.HireDate,
       m.Budget, m.DirectReports,
       NULL as ProgrammingLanguage, NULL as GitHubHandle,
       NULL as Territory, NULL as QuotaTarget
FROM Employee e
JOIN Manager m ON e.PersonId = m.PersonId
UNION ALL
SELECT e.PersonId, e.Name, e.HireDate,
       NULL, NULL,
       eng.ProgrammingLanguage, eng.GitHubHandle,
       NULL, NULL
FROM Employee e
JOIN Engineer eng ON e.PersonId = eng.PersonId
UNION ALL
-- ... similar for SalesRep

-- Type-specific query: only managers (simple JOIN)
SELECT e.PersonId, e.Name, e.HireDate, m.Budget, m.DirectReports
FROM Employee e
JOIN Manager m ON e.PersonId = m.PersonId;

-- Type discovery: check table presence
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM Manager WHERE PersonId = @id) THEN 'MANAGER'
        WHEN EXISTS (SELECT 1 FROM Engineer WHERE PersonId = @id) THEN 'ENGINEER'
        WHEN EXISTS (SELECT 1 FROM SalesRep WHERE PersonId = @id) THEN 'SALES_REP'
        ELSE 'UNKNOWN'
    END as EmployeeType;

Table Per Concrete Class (also called Concrete Table Inheritance) creates tables only for concrete (instantiable) types. Each table contains all attributes—both inherited and local. Abstract supertypes have no tables.

Implementation:

Assuming EMPLOYEE is abstract (no direct instances exist):

TPC Schema:

-- No Employee table! (abstract supertype)

-- Each concrete class gets full-width table
CREATE TABLE Manager (
    PersonId UUID PRIMARY KEY,
    -- Inherited from Employee
    Name VARCHAR(100) NOT NULL,
    HireDate DATE NOT NULL,
    -- Local to Manager
    Budget DECIMAL(15,2) NOT NULL,
    DirectReports INTEGER NOT NULL
);

CREATE TABLE Engineer (
    PersonId UUID PRIMARY KEY,
    -- Inherited from Employee (duplicated definition)
    Name VARCHAR(100) NOT NULL,
    HireDate DATE NOT NULL,
    -- Local to Engineer
    ProgrammingLanguage VARCHAR(50) NOT NULL,
    GitHubHandle VARCHAR(50)
);

CREATE TABLE SalesRep (
    PersonId UUID PRIMARY KEY,
    -- Inherited from Employee (duplicated definition)
    Name VARCHAR(100) NOT NULL,
    HireDate DATE NOT NULL,
    -- Local to SalesRep
    Territory VARCHAR(100) NOT NULL,
    QuotaTarget DECIMAL(12,2) NOT NULL
);

Critical Requirement: Global Key Uniqueness

With TPC, each concrete table has its own PersonId column. You MUST ensure uniqueness ACROSS ALL TABLES, not just within each table:

-- Option 1: Use globally unique identifiers (UUIDs)
-- Option 2: Create a key registry table
CREATE TABLE EmployeeIds (
    PersonId UUID PRIMARY KEY
);
-- All concrete tables FK to this
ALTER TABLE Manager 
    ADD CONSTRAINT fk_manager_id REFERENCES EmployeeIds(PersonId);
-- (repeat for other tables)

Query Patterns with TPC:

-- Polymorphic query: REQUIRES UNION of all concrete tables
SELECT PersonId, Name, HireDate, 
       Budget, DirectReports, 
       NULL as ProgrammingLanguage, NULL as GitHubHandle,
       NULL as Territory, NULL as QuotaTarget,
       'MANAGER' as EmployeeType
FROM Manager
UNION ALL
SELECT PersonId, Name, HireDate,
       NULL, NULL,
       ProgrammingLanguage, GitHubHandle,
       NULL, NULL,
       'ENGINEER' as EmployeeType
FROM Engineer
UNION ALL
SELECT PersonId, Name, HireDate,
       NULL, NULL,
       NULL, NULL,
       Territory, QuotaTarget,
       'SALES_REP' as EmployeeType
FROM SalesRep;

-- Type-specific query: simple, single table access
SELECT PersonId, Name, HireDate, Budget, DirectReports
FROM Manager
WHERE Budget > 100000;

-- Type discovery: check all tables
SELECT CASE
    WHEN EXISTS (SELECT 1 FROM Manager WHERE PersonId = @id) THEN 'MANAGER'
    WHEN EXISTS (SELECT 1 FROM Engineer WHERE PersonId = @id) THEN 'ENGINEER'
    ...
END;

Choosing the right inheritance mapping strategy requires evaluating trade-offs across multiple dimensions. No strategy is universally best—each excels in different scenarios.

Decision Framework:

Choose STH When:

• Hierarchy has ≤5 subtypes
• Subtypes have few local attributes (≤3-4 each)
• Polymorphic queries dominate (>70% of access)
• Query simplicity is more important than storage
• Application layer can enforce complex constraints

Choose TPT When:

• Hierarchy is deep (3+ levels)
• Each type has significant local attributes
• Schema normalization is important
• Need strong type-specific constraints
• Balance between polymorphic and type-specific access
• Foreign keys need to reference the hierarchy

Choose TPC When:

• Type-specific queries dominate (>80% of access)
• Polymorphic queries are rare or can be handled in application
• Each concrete type is relatively independent
• Performance of type-specific reads is critical
• Hierarchy is shallow (mostly 1 level of subtypes)
• Can manage referential integrity externally

Real-world hierarchies often benefit from hybrid strategies that apply different mapping approaches to different parts of the hierarchy. Advanced techniques can also address specific challenges.

Hybrid Strategy 1: TPT Root with STH Subtypes

Use TPT for the main hierarchy levels, but collapse leaf-level siblings into single tables.

EMPLOYEE (TPT - separate table)
├── MANAGER (TPT - separate table)
├── TECHNICAL (STH - one table for all technical roles)
│   ├── Engineer
│   ├── Architect  
│   └── DataScientist
└── SALES (STH - one table for all sales roles)
    ├── SalesRep
    └── AccountManager

Implementation:

-- TPT tables for upper levels
CREATE TABLE Employee (PersonId UUID PRIMARY KEY, ...);
CREATE TABLE Manager (PersonId UUID PRIMARY KEY REFERENCES Employee, ...);

-- STH table for Technical subtree
CREATE TABLE Technical (
    PersonId UUID PRIMARY KEY REFERENCES Employee,
    TechnicalRole VARCHAR(20), -- 'Engineer', 'Architect', 'DataScientist'
    -- All technical role attributes
    ProgrammingLanguages TEXT,  -- All technical roles
    SystemsDesigned INTEGER,    -- Architects only, NULL for others
    ModelsDeployed INTEGER      -- DataScientists only, NULL for others
);

Hybrid Strategy 2: TPT with Discriminator Views

Use TPT for storage but create views that simulate STH for query convenience:

-- Create unified view for polymorphic access
CREATE VIEW AllEmployees AS
SELECT e.PersonId, e.Name, e.HireDate,
       CASE 
           WHEN m.PersonId IS NOT NULL THEN 'MANAGER'
           WHEN eng.PersonId IS NOT NULL THEN 'ENGINEER'
           WHEN sr.PersonId IS NOT NULL THEN 'SALES_REP'
       END as EmployeeType,
       m.Budget, m.DirectReports,
       eng.ProgrammingLanguage, eng.GitHubHandle,
       sr.Territory, sr.QuotaTarget
FROM Employee e
LEFT JOIN Manager m ON e.PersonId = m.PersonId
LEFT JOIN Engineer eng ON e.PersonId = eng.PersonId
LEFT JOIN SalesRep sr ON e.PersonId = sr.PersonId;

-- Application queries the view as if it were STH
SELECT * FROM AllEmployees WHERE EmployeeType = 'MANAGER';
SELECT * FROM AllEmployees WHERE HireDate > '2020-01-01';

Hybrid Strategy 3: Registry Table for TPC

Use TPC for storage efficiency and type-specific queries, but add a registry table for FK references:

-- Registry table (provides FK target)
CREATE TABLE EmployeeRegistry (
    PersonId UUID PRIMARY KEY,
    EmployeeType VARCHAR(20) NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Concrete tables reference registry
CREATE TABLE Manager (
    PersonId UUID PRIMARY KEY REFERENCES EmployeeRegistry(PersonId),
    -- All attributes including inherited
    Name VARCHAR(100) NOT NULL,
    ...
);

-- Other tables can FK to registry
CREATE TABLE Order (
    OrderId UUID PRIMARY KEY,
    CreatedBy UUID REFERENCES EmployeeRegistry(PersonId),
    ...
);

Multiple inheritance adds complexity to the mapping process. When a type inherits from multiple supertypes, the physical schema must accommodate the diamond pattern without duplicating data or losing integrity.

TPT for Multiple Inheritance:

TPT naturally handles multiple inheritance—the subtype table simply references multiple parent tables:

-- Root (shared ancestor)
CREATE TABLE Person (
    PersonId UUID PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

-- First branch
CREATE TABLE Student (
    PersonId UUID PRIMARY KEY REFERENCES Person(PersonId),
    StudentNumber VARCHAR(20) UNIQUE,
    Major VARCHAR(100)
);

-- Second branch
CREATE TABLE Employee (
    PersonId UUID PRIMARY KEY REFERENCES Person(PersonId),
    EmployeeNumber VARCHAR(20) UNIQUE,
    Salary DECIMAL(12,2)
);

-- Multiple inheritance: references BOTH branches
CREATE TABLE StudentEmployee (
    PersonId UUID PRIMARY KEY
        -- FK to Student (which FKs to Person)
        REFERENCES Student(PersonId),
    -- Additional constraint: must also exist in Employee
    CONSTRAINT chk_also_employee CHECK (
        EXISTS (SELECT 1 FROM Employee WHERE Employee.PersonId = StudentEmployee.PersonId)
    ),
    -- Local attributes
    MaxHoursPerWeek INTEGER,
    WorkStudyEligible BOOLEAN
);

-- Alternative: Trigger-based enforcement of dual-reference
CREATE TRIGGER trg_student_employee_check
BEFORE INSERT ON StudentEmployee
FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Student WHERE PersonId = NEW.PersonId) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Must exist in Student';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM Employee WHERE PersonId = NEW.PersonId) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Must exist in Employee';
    END IF;
END;

Query for Multiple Inheritance Entities:

-- Get full StudentEmployee data
SELECT 
    p.PersonId, p.Name,                    -- From Person
    s.StudentNumber, s.Major,              -- From Student
    e.EmployeeNumber, e.Salary,            -- From Employee
    se.MaxHoursPerWeek, se.WorkStudyEligible  -- From StudentEmployee
FROM StudentEmployee se
JOIN Student s ON se.PersonId = s.PersonId
JOIN Employee e ON se.PersonId = e.PersonId
JOIN Person p ON se.PersonId = p.PersonId;

-- 4-way JOIN: one per hierarchy level

Insert Pattern for Multiple Inheritance:

-- Must insert in order: root → branches → leaf
BEGIN TRANSACTION;

-- 1. Insert into root
INSERT INTO Person (PersonId, Name) VALUES (@id, 'Jane Doe');

-- 2. Insert into both branches
INSERT INTO Student (PersonId, StudentNumber, Major) 
VALUES (@id, 'S12345', 'Computer Science');

INSERT INTO Employee (PersonId, EmployeeNumber, Salary)
VALUES (@id, 'E98765', 25000.00);

-- 3. Insert into multiple-inheritance subtype
INSERT INTO StudentEmployee (PersonId, MaxHoursPerWeek, WorkStudyEligible)
VALUES (@id, 20, TRUE);

COMMIT;