Supertype and Subtype Modeling

Implement table-per-hierarchy or table-per-type patterns to manage entity inheritance.

In this lesson, you will learn how to translate hierarchical "is-a" relationships into physical database tables using two classic architectural patterns.

When we model data, we often find a parent entity (the Supertype) that contains attributes shared by everyone, and child entities (Subtypes) that contain specific, unique attributes. For example, if we have a system for Employees, we might have FullTime employees (who get benefits) and Contractors (who have an hourly rate).

To implement this in a relational schema, we use one of two primary strategies:

1. Table-per-Hierarchy (Single Table Pattern)

In this approach, you collapse the Supertype and all Subtypes into one wide table. To distinguish between rows, you add a "discriminator" column (e.g., employee_type).

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    employee_type VARCHAR(20), -- 'FT' or 'CON'
    salary DECIMAL(10,2),      -- NULL for Contractors
    hourly_rate DECIMAL(10,2)  -- NULL for FullTime
);

Pros: Simple queries and no complex joins. Cons: You must use NULL for attributes that don't apply to a specific subtype, which can compromise data integrity.

2. Table-per-Type (Class Table Inheritance)

Here, you create one base table for common attributes and separate tables for each subtype, linked by a shared Primary Key that also acts as a Foreign Key.

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE FullTime_Employees (
    emp_id INT PRIMARY KEY REFERENCES Employees(emp_id),
    salary DECIMAL(10,2)
);

CREATE TABLE Contractors (
    emp_id INT PRIMARY KEY REFERENCES Employees(emp_id),
    hourly_rate DECIMAL(10,2)
);

Pros: Highly normalized; you can enforce NOT NULL constraints on subtype-specific fields. Cons: Requires JOIN operations to retrieve a complete profile of an entity, which can increase query complexity.

When choosing between these, consider your business logic: use Table-per-Hierarchy for simple, overlapping data, and Table-per-Type when your subtypes have distinct, mandatory data requirements that must remain strictly valid.

Key Takeaway: Supertype/Subtype modeling forces a trade-off between the query simplicity of a single "flat" table and the rigid data integrity provided by normalized, separate subtype tables.