SQL by Design: How to Model Supertypes and Subtypes

Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.

Model a supertype when several entity types share one identity and common facts. Model subtypes when each type adds its own attributes, relationships, or business rules. For most portable SQL designs, use one supertype table and one table per subtype, with the subtype’s primary key also serving as a foreign key to the supertype.

That pattern is a strong default—not a universal rule. The right design depends on whether subtype membership is disjoint or overlapping, total or partial, how the hierarchy is queried, how often it changes, and how much enforcement the database must provide.

#	Preview	Product	Price
1		Grokking Relational Database Design	$45.49	Buy on Amazon
2		Learning SQL: Generate, Manipulate, and Retrieve Data	$34.65	Buy on Amazon
3		Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data	$19.99	Buy on Amazon
4		SQL Database Query Programmer T-Shirt	$16.99	Buy on Amazon

What a supertype and subtype mean

A supertype represents the common portion of several related entity types. A subtype represents a meaningful subset of those entities with additional data or rules.

Person
├── Student
└── Employee

A Person may have a name and date of birth. A Student may additionally have a student number and major, while an Employee may have an employee number and hire date.

#1 Best Overall

Sale

Grokking Relational Database Design

$45.49

Buy on Amazon

The key test is the “is-a” test:

• A Student is a Person.
• A Car is a Vehicle.
• A CheckingAccount is an Account.

Do not create a subtype merely because two tables share column names. Shared columns may instead indicate a reusable component, a one-to-one extension, a role, a category, or duplicated design.

Decide the semantics before choosing tables

Are sibling subtypes disjoint or overlapping?

Disjoint subtypes allow an entity to belong to at most one sibling subtype. A vehicle might be classified as a car, truck, or motorcycle, but not more than one of these.

Overlapping subtypes allow multiple memberships. A person can be both a student and an employee. Separate subtype tables naturally support this: the same person_id can appear once in each table.

Never assume that sibling subtypes are disjoint simply because they appear side by side in an ER diagram.

Is specialization total or partial?

Total specialization means every supertype row must belong to at least one subtype. For example, every account must be either a checking account or a savings account.

Partial specialization permits a supertype row with no subtype. A person may be stored before the application knows whether that person is a student or employee.

A foreign key from a subtype to a supertype enforces only the child-to-parent relationship. It does not prove that every supertype has a subtype, that every supertype has exactly one subtype, or that sibling memberships are disjoint.

Is the concept really a subtype?

Some frequently misclassified concepts should be modeled differently:

• Status: “Paid order” is usually a state that can change, not a permanent subtype of order.
• Role: A person who works as an employee and also acts as a customer may have independent roles.
• Category: A product’s category is often a classification or many-to-many relationship.
• Capability: An editor permission is usually a role or authorization, not a subtype of user.

Ask whether the distinction represents a stable “is-a” relationship with its own attributes and constraints, rather than a value that changes frequently.

What belongs in each table?

Put attributes in the supertype when they are true for every member of the hierarchy. The shared identifier, common relationships, and hierarchy-wide constraints belong there too.

CREATE TABLE person (
    person_id       bigint PRIMARY KEY,
    full_name       varchar(200) NOT NULL,
    date_of_birth   date
);

Put subtype-specific attributes and relationships in the subtype table:

CREATE TABLE student (
    person_id       bigint PRIMARY KEY,
    student_number  varchar(30) NOT NULL UNIQUE,
    major           varchar(100),

    CONSTRAINT student_person_fk
        FOREIGN KEY (person_id)
        REFERENCES person (person_id)
);

CREATE TABLE employee (
    person_id       bigint PRIMARY KEY,
    employee_number varchar(30) NOT NULL UNIQUE,
    hire_date       date NOT NULL,

    CONSTRAINT employee_person_fk
        FOREIGN KEY (person_id)
        REFERENCES person (person_id)
);

The subtype primary key is also a foreign key. This shared-key arrangement ensures that every subtype row refers to one existing person and that a person has at most one row in each particular subtype.

It does not prevent the same person from appearing in both student and employee. That is correct for an overlapping hierarchy and requires extra enforcement if the business rule says the subtypes are disjoint.

Rank #2

Sale

Learning SQL: Generate, Manipulate, and Retrieve Data

$34.65

Buy on Amazon

The main SQL mapping strategies

Relational modeling guidance commonly identifies three principal mappings: a relation for every entity type, relations only for leaf types, or one relation for the entire hierarchy. The practical choices are as follows.

1. Class-table inheritance: one table per entity type

This is the portable shared-key design shown above:

person(person_id, full_name, date_of_birth)
student(person_id, student_number, major)
employee(person_id, employee_number, hire_date)

Advantages:

• Common attributes are stored once.
• Subtype-specific columns are not irrelevant nullable columns in the supertype.
• Foreign keys, unique constraints, and subtype-specific checks are straightforward.
• The design works across most relational database systems.
• Overlapping membership is natural.

Costs:

• Complete subtype objects require joins.
• Creating or deleting an entity may involve several tables.
• Total and disjoint rules require additional enforcement.
• Deep hierarchies can produce long join chains.

Use this as the default when the subtype distinction is important, the schema should remain portable, and normalization and integrity matter more than eliminating joins.

2. Single-table inheritance: one table with a discriminator

All types share one table, and a discriminator identifies the row’s type:

CREATE TABLE person (
    person_id       bigint PRIMARY KEY,
    person_type     varchar(20) NOT NULL,
    full_name       varchar(200) NOT NULL,
    student_number  varchar(30),
    major           varchar(100),
    employee_number varchar(30),
    hire_date       date,

    CONSTRAINT person_type_ck
        CHECK (person_type IN ('STUDENT', 'EMPLOYEE')),

    CONSTRAINT student_fields_ck
        CHECK (
            person_type <> 'STUDENT'
            OR (student_number IS NOT NULL AND major IS NOT NULL)
        ),

    CONSTRAINT employee_fields_ck
        CHECK (
            person_type <> 'EMPLOYEE'
            OR (employee_number IS NOT NULL AND hire_date IS NOT NULL)
        )
);

Advantages: common queries are simple, complete objects require no joins, and querying the entire hierarchy is convenient.

Costs: subtype columns may be sparse, adding a subtype requires altering the shared table, and the discriminator can become inconsistent with the populated columns unless constraints or controlled write paths prevent it.

Nullable subtype columns are not automatically wrong. A small, stable hierarchy with few subtype attributes may be easier to operate as one table. The design becomes problematic when it turns into a wide, ambiguous table with weak conditional constraints.

3. Concrete-table inheritance: one complete table per leaf

Each leaf table contains both common and specific columns:

student(
    person_id, full_name, date_of_birth, student_number, major
)

employee(
    person_id, full_name, date_of_birth, employee_number, hire_date
)

This gives fast, simple subtype-specific reads without joins, but duplicates common data. Updating shared attributes becomes harder, global identity must be managed carefully, and queries over all people require UNION ALL.

Concrete tables are most defensible when leaf populations are operationally independent, cross-subtype queries are rare, common attributes are few and stable, or the populations are not truly one shared entity population.

4. Role or category tables

If membership is independently assigned and revoked, model it as a role or classification rather than inheritance:

CREATE TABLE person (
    person_id bigint PRIMARY KEY,
    full_name varchar(200) NOT NULL
);

CREATE TABLE person_role (
    person_id bigint NOT NULL
        REFERENCES person(person_id),
    role_code varchar(30) NOT NULL,
    PRIMARY KEY (person_id, role_code)
);

This works well when roles are numerous, independently managed, or not associated with a fixed set of subtype-specific attributes. “Employee” may still be a genuine subtype if it has employee-only facts such as a hire date and payroll relationships; “editor” is more likely a role or permission.

A complete portable design

For a vehicle hierarchy, the shared-key pattern looks like this:

Rank #3

Sale

Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data

$19.99

Buy on Amazon

CREATE TABLE vehicle (
    vehicle_id  bigint PRIMARY KEY,
    vin         varchar(17) NOT NULL UNIQUE,
    make        varchar(80) NOT NULL,
    model       varchar(80) NOT NULL
);

CREATE TABLE car (
    vehicle_id  bigint PRIMARY KEY,
    door_count  integer NOT NULL CHECK (door_count BETWEEN 2 AND 6),

    CONSTRAINT car_vehicle_fk
        FOREIGN KEY (vehicle_id)
        REFERENCES vehicle (vehicle_id)
);

CREATE TABLE truck (
    vehicle_id  bigint PRIMARY KEY,
    payload_kg  numeric(10, 2) NOT NULL CHECK (payload_kg >= 0),

    CONSTRAINT truck_vehicle_fk
        FOREIGN KEY (vehicle_id)
        REFERENCES vehicle (vehicle_id)
);

Here, the database can enforce that a car or truck cannot exist without its vehicle row, while each subtype enforces its own attributes.

Insert and delete safely

For class-table inheritance, create the supertype and subtype in one transaction:

BEGIN;

INSERT INTO person (person_id, full_name, date_of_birth)
VALUES (1001, 'Avery Chen', DATE '1998-04-12');

INSERT INTO student (person_id, student_number, major)
VALUES (1001, 'S-1001', 'Computer Science');

COMMIT;

If the subtype insert fails, the transaction should roll back the supertype insert. In production, a stored procedure or service transaction can make this lifecycle rule the only supported write path.

Choose deletion behavior explicitly. Cascading is convenient:

FOREIGN KEY (person_id)
REFERENCES person(person_id)
ON DELETE CASCADE

Deleting the person then deletes the subtype row. This is appropriate when subtype data has no independent lifetime, but it can be destructive. The default restrictive behavior, or an explicit ON DELETE RESTRICT, is safer when deleting a parent should first require dependent records to be handled.

Never allow a caller to create an orphan subtype row or silently delete shared data without deciding what should happen to dependent relationships, audit records, and history.

Querying a hierarchy

To retrieve all supertype entities:

SELECT person_id, full_name, date_of_birth
FROM person;

To retrieve students with common and subtype-specific data:

SELECT
    p.person_id,
    p.full_name,
    s.student_number,
    s.major
FROM person AS p
JOIN student AS s
  ON s.person_id = p.person_id;

To display optional subtype information for every person:

SELECT
    p.person_id,
    p.full_name,
    s.student_number,
    e.employee_number
FROM person AS p
LEFT JOIN student AS s
  ON s.person_id = p.person_id
LEFT JOIN employee AS e
  ON e.person_id = p.person_id;

For overlapping subtypes, both joins may legitimately return data for the same person. For disjoint subtypes, the data model should prevent or validate that state rather than relying on every query author to remember the assumption.

What SQL constraints do—and do not—enforce

• Primary key: identifies one row and prevents duplicate identifiers.
• Foreign key: ensures that a subtype identifier matches an existing supertype identifier.
• NOT NULL: requires a value for a column.
• UNIQUE: prevents duplicate business identifiers such as student numbers.
• CHECK: validates a row-local condition.

A foreign key does not enforce total specialization or sibling disjointness. A row-level check generally cannot inspect other tables. For example, a check on vehicle cannot by itself verify that exactly one row exists in either car or truck.

Possible solutions include:

• an application transaction that creates and updates all required rows;
• a stored procedure used as the controlled write interface;
• triggers for cross-table validation;
• deferred or database-specific constraint mechanisms;
• periodic validation queries and operational monitoring;
• a different schema that makes the invalid state impossible.

Triggers can enforce useful invariants, but they also introduce hidden behavior, ordering concerns, portability problems, bulk-load surprises, and concurrency-testing requirements. Document and test them under concurrent writes.

With a single-table design, a discriminator plus conditional checks can enforce row-local consistency. Keep the logic readable with separate named constraints and be careful with SQL’s treatment of NULL. In PostgreSQL, a check constraint succeeds when its expression evaluates to TRUE or UNKNOWN; use appropriate NOT NULL constraints when an actual value is required. See the PostgreSQL constraints documentation.

Validating total and disjoint rules

These queries can detect invalid states in the vehicle example.

Rank #4

Sale

SQL Database Query Programmer T-Shirt

• Database Programming design. Funny database SQL joke that makes a great gift for database administrators, programmers or computer scientists. Fun gift for database administrators, programmers and hackers who like to wear funny nerd clothes.
• Funny gift for men and women who love SQL. The perfect SQL Query top for programmers, hackers and SQL database fans who love relational databases.
• Lightweight, Classic fit, Double-needle sleeve and bottom hem

$16.99

Buy on Amazon

Find vehicles that appear in both supposedly disjoint subtypes:

SELECT v.vehicle_id
FROM vehicle AS v
LEFT JOIN car AS c
  ON c.vehicle_id = v.vehicle_id
LEFT JOIN truck AS t
  ON t.vehicle_id = v.vehicle_id
WHERE c.vehicle_id IS NOT NULL
  AND t.vehicle_id IS NOT NULL;

Find vehicles that belong to neither subtype in a total hierarchy:

SELECT v.vehicle_id
FROM vehicle AS v
LEFT JOIN car AS c
  ON c.vehicle_id = v.vehicle_id
LEFT JOIN truck AS t
  ON t.vehicle_id = v.vehicle_id
WHERE c.vehicle_id IS NULL
  AND t.vehicle_id IS NULL;

These are validation queries, not universal declarative enforcement. Whether they run as part of a write transaction, a constraint mechanism, or a monitoring job depends on the database system and the required guarantees.

Portability and database-specific inheritance

SQL does not provide one portable, universal inheritance feature. Do not confuse three different ideas:

1. Mapping a conceptual supertype/subtype hierarchy to ordinary relational tables.
2. Using a vendor’s table-inheritance feature.
3. Using object-relational types or application ORM inheritance.

PostgreSQL has an INHERITS table feature, but it is not a drop-in replacement for the shared-key design. PostgreSQL documents that SQL:1999-style inheritance is not supported and describes database-specific inheritance behavior and limitations. Consult the CREATE TABLE documentation before adopting it.

Oracle documents inheritance for SQL object types. That is an object-relational feature, not the same as mapping an EER hierarchy into ordinary tables. See Oracle’s inheritance documentation.

If migration between database systems matters, ordinary primary-key and foreign-key tables are usually easier to understand, test, and port.

Performance, evolution, and operations

Class-table designs trade joins for cleaner data. Index subtype foreign keys when they are used for joins, filtering, or parent lookups. A primary key usually creates an index, but confirm the database’s behavior and indexing needs for your workload.

Single-table designs reduce joins but may produce wide rows and require table changes as subtypes evolve. Concrete-table designs can make leaf queries simple but complicate global reporting and shared updates.

Deep hierarchies deserve special scrutiny:

Entity → Person → Employee → Manager → RegionalManager

Each level can add joins, lifecycle rules, and migration complexity. Keep a level only if it contributes an independently useful attribute set, relationship, or constraint. Otherwise, flatten selected levels or use a more explicit association.

When a subtype can change over time, determine whether the change represents a current state, a historical classification, a role, or a permanent type. A table hierarchy may be inappropriate if entities frequently move between types and the history of those changes matters.

Multiple inheritance can create conflicting attributes, ambiguous constraints, and difficult diamond-shaped relationships. Explicit associative tables or capabilities are often easier to reason about unless multiple-inheritance semantics are genuinely required.

Common mistakes

• One giant nullable table with no discriminator: the database cannot tell which combinations of columns are valid.
• Unconstrained subtype IDs: matching column names do not create referential integrity; declare the foreign key.
• Assuming a discriminator is enough: it must stay synchronized with subtype-specific data.
• Using subtypes for statuses: changing states usually belong in status and event tables.
• Using polymorphic foreign keys: a target_type/target_id pair cannot enforce references to unrelated tables portably.
• Using EAV as a default: entity-attribute-value designs often weaken type checking, uniqueness, foreign keys, indexing, and reporting.
• Duplicating common columns without a reason: concrete tables make global identity and updates harder.
• Confusing partitioning with inheritance: partitioning organizes storage; it does not automatically express an “is-a” relationship.

A practical decision table

Implementation checklist

• Does every subtype pass the “is-a” test?
• Are sibling memberships disjoint or overlapping?
• Is specialization total or partial?
• Are the proposed supertype attributes truly universal?
• What must be stored only for one subtype?
• Will complete-object reads or subtype-specific reads dominate?
• How will subtype membership be prevented from becoming inconsistent?
• What should happen when the supertype is deleted?
• Are all shared identifiers backed by declared primary and foreign keys?
• Is database portability important?
• Will new subtypes be added frequently?
• Would roles, categories, or status history describe the domain more accurately?

For broader conceptual-modeling background, see the Engineering LibreTexts overview of mapping supertypes and subtypes and the University of Minnesota’s subtype and supertype material.

Quick Recap

SaleBestseller No. 1

Grokking Relational Database Design

$45.49

SaleBestseller No. 2

Learning SQL: Generate, Manipulate, and Retrieve Data

$34.65

SaleBestseller No. 3

Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data

$19.99

SaleBestseller No. 4

SQL Database Query Programmer T-Shirt

Lightweight, Classic fit, Double-needle sleeve and bottom hem

$16.99

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.