Entity Hierarchies (IsA)

The IsA relationship

An entity hierarchy (also called an IsA hierarchy, generalisation/specialisation, or superclass/subclass) models the situation where one entity type is a refinement of another. The sub-entity (child) is a specific kind of the super-entity (parent).

Consider a university database:

Temporary_Employee and Contract_Employee are both kinds of Employee. They inherit all attributes (including the key employee_id) and all relationships of the parent entity. The sub-entities add attributes specific to their subtype (hourly_rate for temporary staff, contract_id for contract staff).

Key properties

There are several important properties of the IsA hierarchy:

1. Attribute inheritance: sub-entities automatically possess all attributes of their parent. There is no need to repeat employee_id or name in the sub-entity definitions.
2. Relationship inheritance: any relationship the parent participates in is also inherited by the sub-entities. If Employee has a Works_In relationship with Department, then both Temporary_Employee and Contract_Employee also have that relationship.
3. No separate key: sub-entities do not have their own key or discriminator. They are identified by the parent’s key. A Temporary_Employee with employee_id = 142 is the same real-world entity as the Employee with employee_id = 142.
4. Multiple inheritance: an entity can have multiple parent types (though this is rare in practice and some modelling disciplines discourage it).

Coverage constraints

An IsA hierarchy may specify whether the sub-entities cover all possible instances of the parent:

Constraint	Meaning
Total (double line from parent to IsA)	Every Employee must be either a Temporary_Employee or a Contract_Employee. No plain Employee instances exist.
Partial (single line)	There may be Employee instances that are neither temporary nor contract staff (e.g., permanent salaried employees).

There may also be a disjointness constraint:

Constraint	Meaning
Disjoint	An Employee cannot be both a Temporary_Employee and a Contract_Employee simultaneously.
Overlapping	An Employee could belong to multiple sub-entities (e.g., someone who is both a Lecturer and a Researcher).

Relational mapping strategies

There are three standard approaches for mapping an entity hierarchy to relational tables. Choosing among them involves trade-offs between query complexity, storage efficiency, and constraint enforcement.

Strategy 1: Single table with type tag

Put all attributes — parent and all sub-entities — into one table, with a discriminator column and nullable sub-entity columns:

CREATE TABLE Employee (
    employee_id  SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    emp_type     TEXT NOT NULL CHECK (emp_type IN ('temp', 'contract')),
    hourly_rate  NUMERIC,       -- NULL for contract employees
    contract_id  TEXT           -- NULL for temporary employees
);

Trade-off	Detail
Pro	Simple queries; no joins needed to get full employee data.
Con	Many NULL columns; wasted space. Cannot enforce NOT NULL on sub-entity attributes (a Temporary_Employee must have an hourly_rate, but the column must allow NULLs for contract rows).
Con	Adding a new sub-entity requires altering the table schema.

Strategy 2: Separate tables for sub-entities

One table for the parent, and one table for each sub-entity containing only the sub-entity-specific columns, with the parent’s key as both primary key and foreign key:

CREATE TABLE Employee (
    employee_id  SERIAL PRIMARY KEY,
    name         TEXT NOT NULL
);

CREATE TABLE Temporary_Employee (
    employee_id  INTEGER PRIMARY KEY REFERENCES Employee(employee_id),
    hourly_rate  NUMERIC NOT NULL
);

CREATE TABLE Contract_Employee (
    employee_id  INTEGER PRIMARY KEY REFERENCES Employee(employee_id),
    contract_id  TEXT NOT NULL
);

Trade-off	Detail
Pro	No nullable columns; sub-entity-specific NOT NULL constraints are enforceable.
Con	Querying all attributes of a Temporary_Employee requires a join with the parent table.
Con	Does not enforce disjointness natively — a single employee_id could appear in both Temporary_Employee and Contract_Employee unless additional constraints are added.

Strategy 3: One table per concrete sub-entity

Separate tables for each concrete sub-entity, each containing all inherited attributes plus its own specific attributes. No table for the abstract parent:

CREATE TABLE Temporary_Employee (
    employee_id  SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    hourly_rate  NUMERIC NOT NULL
);

CREATE TABLE Contract_Employee (
    employee_id  SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    contract_id  TEXT NOT NULL
);

Trade-off	Detail
Pro	No joins needed for any query; all attributes in one place.
Con	If a relationship points to Employee (the parent), you cannot declare a foreign key to a single table — you must use a view, a union, or application-level logic.
Con	Keys are no longer globally unique across the hierarchy. Two sub-entity tables could independently generate employee_id = 1.

Multi-level hierarchies

IsA hierarchies can extend to multiple levels. For example:

Employee
  └── Academic_Staff
        ├── Lecturer
        └── Professor
  └── Administrative_Staff
        ├── Registrar
        └── Technician

The same mapping strategies apply recursively. A multi-level hierarchy mapped with Strategy 2 would require joins through several tables to reconstruct a complete entity; the performance cost must be weighed against the schema clarity.

Summary

• An IsA hierarchy models generalisation/specialisation: sub-entities inherit attributes and relationships from the parent.
• Sub-entities have no independent key; they are identified by the parent’s key.
• Coverage constraints (total vs. partial) and disjointness constraints (disjoint vs. overlapping) refine the hierarchy.
• Three mapping strategies exist: single table with type tags, separate sub-entity tables, or one table per concrete sub-entity.
• The choice involves trade-offs: query complexity, NULL handling, constraint enforcement, and key uniqueness.