Inheritance is a fundamental concept in object-oriented modeling, allowing you to define a general entity and then create specialized subtypes that inherit attributes and behaviors from the parent. When designing a relational database, however, representing inheritance is not straightforward. Relational databases are based on tables and rows, which do not natively support inheritance hierarchies. This creates challenges when you need to model real-world entities that share some properties but also have unique characteristics. You must choose a mapping strategy that balances query simplicity, storage efficiency, and maintainability.

-- Single-table inheritance: All employee types in one table, distinguished by a 'type' column
CREATE TABLE employees_single (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    department_id INTEGER REFERENCES departments(department_id),
    manager_id INTEGER REFERENCES employees_single(employee_id),
    type VARCHAR(20) NOT NULL CHECK (type IN ('FullTime', 'PartTime')),
    salary NUMERIC(12,2),         -- Only for FullTime
    bonus NUMERIC(12,2),          -- Only for FullTime
    hourly_rate NUMERIC(8,2),     -- Only for PartTime
    hours_per_week INTEGER        -- Only for PartTime
);

There are several strategies for mapping inheritance hierarchies to relational tables. Two common approaches are class table inheritance and concrete table inheritance.

Class table inheritance creates a separate table for each class in the hierarchy, including the base class and all subclasses. Each subclass table contains only the fields specific to that subclass and a primary key that is also a foreign key referencing the base class. This approach preserves normalization and avoids nulls for subclass-specific fields, but querying for a complete object often requires joining multiple tables, which can impact performance and increase query complexity.

Concrete table inheritance creates a separate table for each concrete subclass, duplicating all the fields from the base class into each subclass table. There is no table for the abstract base class. This approach simplifies querying for subclass instances and avoids joins, but it introduces data redundancy and makes it harder to enforce constraints that should apply to all types.

Choosing between these strategies depends on your data access patterns, the number of fields unique to each subclass, and how often you need to query across the entire hierarchy.

-- Class table inheritance: Separate tables for base class and each subclass

-- Base class table
CREATE TABLE employees_base (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    department_id INTEGER REFERENCES departments(department_id),
    manager_id INTEGER REFERENCES employees_base(employee_id)
);

-- Subclass table for FullTime employees
CREATE TABLE full_time_employees_cti (
    employee_id INTEGER PRIMARY KEY REFERENCES employees_base(employee_id),
    salary NUMERIC(12,2) NOT NULL,
    bonus NUMERIC(12,2) DEFAULT 0
);

-- Subclass table for PartTime employees
CREATE TABLE part_time_employees_cti (
    employee_id INTEGER PRIMARY KEY REFERENCES employees_base(employee_id),
    hourly_rate NUMERIC(8,2) NOT NULL,
    hours_per_week INTEGER NOT NULL
);