Mapping Supertypes, Subtypes, and Inheritance

Learning outcomes

Conceptual models sometimes contain a supertype with specialized subtypes. For example, Party may have Customer and Supplier subtypes, or Employee may have Technician and Dispatcher subtypes. Relational databases do not have one universally correct inheritance representation, so designers choose among several mapping strategies.

Start with subtype semantics

Before choosing tables, answer:

• Can one supertype instance belong to more than one subtype?
• Must every supertype instance belong to at least one subtype?
• Can subtype membership change over time?
• Do subtypes have different attributes, relationships, or lifecycle rules?

These determine whether the specialization is disjoint or overlapping, and total or partial.

Example: Employee, Technician, Dispatcher

Suppose WorkshopHub has:

Employee  employee_id  employee_number  full_nameTechnician subtype  certification_levelDispatcher subtype  dispatch_region

If an employee can be both technician and dispatcher, the subtypes overlap. If every employee must be at least one of them, specialization is total.

Strategy 1: single-table inheritance

Store all supertype and subtype attributes in one table:

CREATE TABLE employee (    employee_id          INTEGER PRIMARY KEY,    employee_number      TEXT NOT NULL UNIQUE,    full_name            TEXT NOT NULL,    employee_type        TEXT NOT NULL,    certification_level  TEXT,    dispatch_region      TEXT);

This is simple to query but introduces nullable columns for attributes that apply only to certain subtypes.

Single-table advantages

• Simple queries over all employees.
• No joins to retrieve subtype-specific data.
• Easy foreign keys from other tables to one employee table.
• Simple inserts for disjoint subtypes.

Single-table disadvantages

• Many subtype-specific nullable columns as the hierarchy grows.
• Complex check constraints to ensure attributes match subtype.
• Awkward support for overlapping subtypes.
• Large tables may combine unrelated specialized attributes.

Strategy 2: class-table inheritance

Store shared attributes in the supertype table and subtype attributes in separate tables whose primary key is also a foreign key:

CREATE TABLE employee (    employee_id     INTEGER PRIMARY KEY,    employee_number TEXT NOT NULL UNIQUE,    full_name       TEXT NOT NULL);CREATE TABLE technician (    employee_id          INTEGER PRIMARY KEY,    certification_level  TEXT NOT NULL,    FOREIGN KEY (employee_id)      REFERENCES employee(employee_id));CREATE TABLE dispatcher (    employee_id      INTEGER PRIMARY KEY,    dispatch_region  TEXT NOT NULL,    FOREIGN KEY (employee_id)      REFERENCES employee(employee_id));

Class-table advantages

• Shared attributes are stored once.
• Subtype-specific attributes avoid irrelevant nulls.
• Overlapping subtypes are easy: the same Employee can have rows in multiple subtype tables.
• Subtype relationships can reference subtype tables directly.

Class-table disadvantages

• Subtype retrieval requires joins.
• Total/disjoint constraints can be difficult to enforce declaratively.
• Inserting a subtype instance requires coordinated writes to two tables.
• Queries across many subtype attributes become more complex.

Strategy 3: concrete-table inheritance

Store each concrete subtype independently, repeating shared attributes:

Technician(  technician_id,  employee_number,  full_name,  certification_level)Dispatcher(  dispatcher_id,  employee_number,  full_name,  dispatch_region)

This avoids joins within each subtype but duplicates supertype attributes and complicates references to “any Employee.”

Concrete-table risks

If the same person can be both Technician and Dispatcher, shared attributes may be duplicated and drift. Global uniqueness of employee_number becomes difficult across separate tables. Relationships to Employee require multiple foreign keys or polymorphic patterns that relational constraints cannot enforce easily.

Subtype discriminator

Disjoint single-table hierarchies often use a discriminator:

employee_type IN ('technician', 'dispatcher')

For overlapping subtypes, one discriminator is insufficient because one employee may belong to several subtypes. Separate subtype tables or a role-assignment structure works better.

Subtype or role?

Do not model every job responsibility as an inheritance subtype. “Approver,” “requester,” and “mentor” may be roles in relationships rather than stable identity categories.

Ask whether subtype membership changes the entity's intrinsic attributes and relationships, or merely describes how it participates in a process.

Subtype or separate entity?

Customer and Supplier might both be Parties, but depending on the organization they may have independent lifecycle, identifiers, and governance. A shared supertype is useful only if meaningful common identity exists.

Do not create Party solely to avoid repeating a name column.

Enforcing disjointness

With class-table inheritance, the database may need to ensure an Employee does not appear in both Technician and Dispatcher if subtypes are disjoint. Standard foreign keys alone do not enforce this cross-table exclusion. Options include:

• one discriminator on Employee plus subtype tables;
• database-specific triggers;
• transaction-layer enforcement;
• redesigning the hierarchy if exclusivity is central.

Enforcing total specialization

“Every Employee must be a Technician or Dispatcher” is also difficult with plain foreign keys because Employee can be inserted before a subtype row. The rule may be enforced transactionally or at a workflow boundary.

WorkshopHub recommendation

If Technician has meaningful specialized attributes and assignments while Dispatcher has different specialized fields, class-table inheritance is a strong default:

Employee  employee_id  employee_number  full_nameTechnician  employee_id PK/FK  certification_levelDispatcher  employee_id PK/FK  dispatch_region

WorkOrderAssignment can reference Technician specifically, while opened_by_employee_id can reference Employee.

Mapping strategy comparison

Practice: choose a mapping

Summary and next lesson

Inheritance mapping is a tradeoff among nullability, joins, duplication, subtype constraints, and shared identity. The final lesson of Chapter 5 maps composite, multivalued, and derived attributes—concepts that often produce repeating groups or redundant columns when mapped carelessly.

References

• Martin Fowler, Patterns of Enterprise Application Architecture.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Martin Fowler, UML Distilled.