Free tools Windows power users keep installed
One-click scans. No signup required.
Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
Implementing a supertype/subtype model means converting an enhanced entity–relationship (EER) hierarchy into tables, keys, constraints, and queries. The three practical strategies are table per hierarchy (TPH), table per type (TPT), and table per concrete type (TPC). Choose among them only after documenting whether specialization is total or partial, disjoint or overlapping, and whether the type distinction is a stable business fact rather than an application label.
For a small, stable, mostly disjoint hierarchy, TPH is often the simplest default. TPT is a better fit when subtype attributes and constraints are substantial and should remain in separate, normalized tables. TPC can make concrete-type reads simple, but duplicates inherited data and complicates global identifiers. Microsoft documents these mappings for EF Core, while Oracle SQL Developer Data Modeler exposes comparable engineering choices (EF Core inheritance mapping; Oracle Data Modeler guide).
What are supertypes and subtypes?
A supertype is a generalized entity that owns attributes and relationships common to several categories. A subtype inherits the supertype’s identity and common properties, then adds attributes, relationships, or rules of its own. Specialization is the top-down process of refining a general entity; generalization is the bottom-up process of factoring shared properties into a supertype.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Person
├── Student
└── Employee
└── ManagerInheritance can span multiple levels. A manager inherits employee and person properties, not merely the attributes declared directly on manager. Oracle’s object-relational documentation describes similar multi-level subtype inheritance, but native Oracle object types are a separate feature from ordinary relational table mapping (Oracle object-type inheritance).
#1 Best Overall
Validate the model before designing tables
Write these decisions down before choosing a physical strategy:
- Completeness: In a total (complete) specialization every supertype row belongs to at least one subtype. In a partial specialization, a bare supertype row is valid.
- Disjointness: In a disjoint hierarchy an instance belongs to one subtype only. In an overlapping hierarchy it may belong to several, such as a person who is both an employee and a customer.
- Depth: Decide whether subtypes can themselves have subtypes. Every additional level adds joins, discriminator values, or migration work.
- Identity: Usually the subtype represents the same real-world object, so its key is the supertype key, not a second unrelated identifier.
Use inheritance when categories share a stable identity and common meaning but have genuinely different properties or rules. A status column is usually better when the difference is only a lifecycle state. Roles, a many-to-many category table, or composition are better when categories overlap, change independently, or can be added by users.
Worked example
Assume Person has person_id, first_name, and last_name. Students additionally require student_number and major; employees require employee_number and hire_date. The examples below assume a disjoint hierarchy unless stated otherwise.
Strategy 1: Single table (table per hierarchy)
TPH stores every hierarchy member in one table and uses a discriminator to identify the concrete type. EF Core uses TPH by default and allows the discriminator name and values to be configured (Microsoft EF Core documentation).
CREATE TABLE person (
person_id BIGINT PRIMARY KEY,
person_type VARCHAR(20) NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
student_number VARCHAR(30),
major VARCHAR(100),
employee_number VARCHAR(30),
hire_date DATE,
CONSTRAINT ck_person_type
CHECK (person_type IN ('PERSON', 'STUDENT', 'EMPLOYEE')),
CONSTRAINT ck_student_fields
CHECK (person_type <> 'STUDENT' OR
(student_number IS NOT NULL AND major IS NOT NULL
AND employee_number IS NULL AND hire_date IS NULL)),
CONSTRAINT ck_employee_fields
CHECK (person_type <> 'EMPLOYEE' OR
(employee_number IS NOT NULL AND hire_date IS NOT NULL
AND student_number IS NULL AND major IS NULL))
);For a total, disjoint hierarchy, make the discriminator NOT NULL and allow only concrete values, such as STUDENT and EMPLOYEE. Include PERSON only when the supertype itself is instantiable. Conditional checks are essential: a discriminator alone does not make subtype columns valid.
Strengths and weaknesses
- One row represents one entity, making identity, polymorphic foreign keys, and supertype-wide queries straightforward.
- Concrete reads require no inheritance joins.
- Subtype-only columns are nullable for other types, and a large hierarchy can become wide and sparse.
- Complex overlapping rules are awkward with a single discriminator.
For overlapping membership, use separate flags only for a very small, stable set of roles. A membership table scales better:
CREATE TABLE person_subtype (
person_id BIGINT NOT NULL REFERENCES person(person_id),
subtype_code VARCHAR(30) NOT NULL,
PRIMARY KEY (person_id, subtype_code)
);Strategy 2: Table per type (table per child)
TPT stores common columns in the supertype table and subtype columns in child tables. Each child primary key is also a foreign key to the parent.
CREATE TABLE person (
person_id BIGINT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL
);
CREATE TABLE student (
person_id BIGINT PRIMARY KEY
REFERENCES person(person_id) ON DELETE CASCADE,
student_number VARCHAR(30) NOT NULL,
major VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
person_id BIGINT PRIMARY KEY
REFERENCES person(person_id) ON DELETE CASCADE,
employee_number VARCHAR(30) NOT NULL,
hire_date DATE NOT NULL
);Subtype fields can be genuinely NOT NULL, and subtype-specific relationships are natural. Creating a student must be atomic:
BEGIN;
INSERT INTO person (person_id, first_name, last_name)
VALUES (1001, 'Ava', 'Morgan');
INSERT INTO student (person_id, student_number, major)
VALUES (1001, 'S-1001', 'Physics');
COMMIT;A concrete query joins the tables:
SELECT p.person_id, p.first_name, p.last_name,
s.student_number, s.major
FROM person AS p
JOIN student AS s ON s.person_id = p.person_id
WHERE p.person_id = 1001;A foreign key guarantees that a child has a parent; it does not guarantee that every parent has a child or that a parent appears in only one child table. Totality and disjointness therefore require controlled write procedures, triggers, a membership table, or another database-specific mechanism. TPT also adds joins to polymorphic queries, and Microsoft warns that its generated SQL is commonly more complex and can be slower than TPH (EF performance white paper).
Strategy 3: Table per concrete type (table per leaf)
TPC creates a complete table for each concrete subtype, including inherited columns. Abstract supertypes have no table.
CREATE TABLE student (
person_id BIGINT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
student_number VARCHAR(30) NOT NULL,
major VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
person_id BIGINT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
employee_number VARCHAR(30) NOT NULL,
hire_date DATE NOT NULL
);Concrete reads avoid joins, but common data is duplicated. A supertype query needs a union:
SELECT person_id, first_name, last_name, 'STUDENT' AS person_type
FROM student
UNION ALL
SELECT person_id, first_name, last_name, 'EMPLOYEE' AS person_type
FROM employee;Independent identity columns can generate the same number in different tables. If identifiers are global across the hierarchy, use a shared sequence, application-generated UUIDs, a central identifier table, or non-overlapping numeric ranges. EF Core documents this key-generation issue for TPC (EF Core inheritance strategies).
Exclusive arcs and separate membership
A diagram showing one supertype with exclusive links to child tables does not automatically become a database constraint. An exclusive-arc design is useful when subtype tables are large or have many relationships, but explicitly define whether a parent may have no child, exactly one child, or several. Oracle Data Modeler documents variants of these engineering choices, including identifying relationships and complete hierarchies (Data Modeler documentation).
For overlapping or dynamic categories, an explicit association such as person_subtype or role tables is generally clearer than adding flags or child tables indefinitely.
How to choose
| Requirement | Usually favors |
|---|---|
| Simplest schema and frequent whole-hierarchy queries | TPH |
| Many sparse subtype attributes | TPT or TPC |
Strict subtype-specific NOT NULL rules |
TPT or TPC |
| Concrete reads dominate and duplicated common data is acceptable | TPC |
| Deep hierarchy or globally unique identity | Usually TPH or TPT |
| Overlapping membership | TPT or roles/association tables |
| Frequent addition of user-defined types | Category or extension model |
There is no universally fastest strategy. Row width, selectivity, indexes, data volume, and access patterns matter. Measure representative workloads before committing; Microsoft’s performance guidance explicitly recommends doing so (EF Core performance modeling).
Implementation workflow
- Specify the hierarchy: list the supertype, subtypes, completeness, disjointness, abstract/concrete status, and subtype relationships.
- Confirm identity: decide whether all members share one key domain.
- Choose the strategy: compare reads, writes, nullability, joins, schema evolution, ORM support, and constraint enforceability.
- Build referential integrity: for TPT, make each child primary key a foreign key to the parent.
- Enforce membership: use a constrained discriminator, child existence, or an explicit membership table.
- Add targeted indexes: index discriminator and subtype columns only when query patterns justify them.
- Expose views when useful: a unified view can simplify reports over TPT or TPC, but it does not fix write-path integrity.
- Test invalid states: missing required subtype, orphan child, incompatible discriminator, duplicate disjoint membership, failed cascade, duplicate TPC key, unknown type, and concurrent subtype creation.
EF Core mapping notes
EF Core supports all three relational strategies. TPH can be configured as:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Person>()
.HasDiscriminator<string>("person_type")
.HasValue<Person>("person")
.HasValue<Student>("student")
.HasValue<Employee>("employee");
}TPT maps each type to a table:
modelBuilder.Entity<Person>().ToTable("person");
modelBuilder.Entity<Student>().ToTable("student");
modelBuilder.Entity<Employee>().ToTable("employee");TPC uses UseTpcMappingStrategy() on the root and maps concrete types to full tables. EF Core 5 introduced TPT support and EF Core 7 introduced TPC support (EF Core 7 what’s new). Verify generated migrations, discriminator checks, cascade behavior, indexes, and SQL plans rather than assuming the object model enforces every database rule.
Common failures and recovery
TPH becomes unmanageably sparse
Split large subtype families into TPT, compose optional detail tables, or replace volatile categories with roles. Keep TPH for a stable common core.
TPT makes polymorphic reads slow
Use projections and covering indexes, add read-optimized views or materialized projections, and benchmark TPH against production-like data before redesigning.
Recommended Free Tools
TPC creates duplicate IDs
Adopt UUIDs, a shared sequence, a central identifier service, or explicitly redefine IDs as table-local.
Disjointness exists only in the diagram
Add a discriminator or membership table and centralize writes in a procedure or service; foreign keys alone are insufficient.
A lifecycle state was modeled as inheritance
Use status history or temporal membership when records move between states and historical classification matters. Structural subtype changes can otherwise lose data or make migrations brittle.
Inheritance is not the only design
- Composition: a common entity owns optional detail tables.
- Roles: separate
PersonEmployeeRoleandPersonCustomerRoletables for independent capabilities. - Category association: a many-to-many entity/category model for overlapping or user-defined classifications.
- Status: a constrained column or status-history table for lifecycle states.
- Extension attributes: a carefully governed extension model when attributes are genuinely dynamic.
The correct physical design is the simplest one that preserves the business rules and matches dominant workload patterns. Do not choose a mapping merely because it resembles the application’s class hierarchy.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Quick Recap
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.
