Inheritance Mapping Selector

Example 1: Sports Player Hierarchy (STI recommended)

Scenario: SportsDB system models Player (name, dateOfBirth) with three subclasses: Footballer (club, position), Cricketer (battingAverage), Bowler (bowlingSpeed, bowlingStyle). The application frequently loads "all active Players" for reporting and roster views. Hierarchy is unlikely to deepen further.

Trigger: "Should we use STI or separate tables for Player/Footballer/Cricketer? We need to query all players at once frequently."

Process: 1. Hierarchy: 3 concrete classes, 1 abstract base. Subclass-specific fields: 1–2 per subclass. Field divergence is low. 2. Dimension score: STI wins on joins (none), refactoring tolerance (none), polymorphism (single query). Cost: wasted columns for ~2–3 nullable columns per row — tolerable. No FK constraint requirement on club or battingAverage. 3. Routing: STI. Fowler uses this exact example to demonstrate Single Table Inheritance. 4. Mixing: Not needed — all branches are similar in shape. 5. ORM config: Hibernate @Inheritance(SINGLE_TABLE) with @DiscriminatorColumn(name="type") on Player; @DiscriminatorValue("F") on Footballer.

Output (schema sketch):

CREATE TABLE players (
  id         BIGINT PRIMARY KEY,
  type       VARCHAR(1) NOT NULL,      -- discriminator: F, C, B
  name       VARCHAR(100) NOT NULL,
  club       VARCHAR(100),             -- Footballer only
  batting_avg DECIMAL(5,2),            -- Cricketer only
  bowling_speed INT,                   -- Bowler only
  bowling_style VARCHAR(50)            -- Bowler only
);

Example 2: Organization Hierarchy with Strict Constraints (CTI recommended)

Scenario: HR system with LegalEntity (taxId, registeredAddress) as abstract base, Corporation (stockExchange, tickerSymbol), Partnership (partnerCount), SoleTrader (tradingName) as concrete subclasses. Every Corporation must have a non-null stockExchange. The compliance team requires FK integrity on all subtype-specific columns. Polymorphic reads ("load any LegalEntity by id") occur at application startup only; most queries are type-specific.

Trigger: "We need strict FK constraints on Corporation fields. Can we still use inheritance?"

Process: 1. Hierarchy depth: 2 levels. Subclass fields: 3–5 per subclass, highly divergent. 2. Dimension score: FK enforcement → CTI required (STI cannot enforce NOT NULL on stockExchange for Corporations). Refactoring impact acceptable (hierarchy is stable by design). Polymorphic reads are infrequent → join cost acceptable. 3. Routing: Class Table Inheritance (Joined). Hibernate @Inheritance(JOINED). 4. Mixing: Not needed.

Output (schema sketch):

CREATE TABLE legal_entities (id BIGINT PRIMARY KEY, tax_id VARCHAR(20), address TEXT, type VARCHAR(20));
CREATE TABLE corporations (id BIGINT REFERENCES legal_entities(id), stock_exchange VARCHAR(10) NOT NULL, ticker VARCHAR(10) NOT NULL);
CREATE TABLE partnerships (id BIGINT REFERENCES legal_entities(id), partner_count INT NOT NULL);
CREATE TABLE sole_traders (id BIGINT REFERENCES legal_entities(id), trading_name VARCHAR(100) NOT NULL);

Example 3: Independent Product Types, Rarely Queried Polymorphically (Concrete recommended)

Scenario: E-commerce catalog with Product as abstract base and three highly divergent concrete types: PhysicalProduct (weight, dimensions, shippingClass), DigitalProduct (fileSize, downloadUrl, licenseType), SubscriptionProduct (billingCycle, trialDays, renewalPolicy). Each has 8–12 unique fields. The reporting team queries each type independently ("all digital downloads this month"). Polymorphic "all products" queries exist only in one admin view.

Trigger: "Our Product table is a mess — 40 columns, half NULL on any given row. What's the alternative?"

Process: 1. Subclass fields: 8–12 per subclass, highly divergent. Only 3 shared fields on Product (id, name, price). 2. Dimension score: Current STI is the problem (wasted space). Concrete Table wins on no NULLs, self-contained tables, per-class query performance. Cost: polymorphic "all products" query → UNION. But this query is rare (one admin view). 3. Routing: Concrete Table Inheritance. Cross-table key uniqueness → use UUID primary keys. 4. Key uniqueness: UUIDs eliminate the cross-table collision problem. 5. Polymorphic admin view: Accept a UNION query here — document the performance expectation; cache if needed.

Output (schema sketch):

CREATE TABLE physical_products (id UUID PRIMARY KEY, name VARCHAR, price DECIMAL, weight_kg DECIMAL, dim_cm VARCHAR, shipping_class VARCHAR);
CREATE TABLE digital_products (id UUID PRIMARY KEY, name VARCHAR, price DECIMAL, file_size_mb INT, download_url TEXT, license_type VARCHAR);
CREATE TABLE subscription_products (id UUID PRIMARY KEY, name VARCHAR, price DECIMAL, billing_cycle VARCHAR, trial_days INT, renewal_policy VARCHAR);
-- Polymorphic admin query:
SELECT id, name, price, 'physical' AS type FROM physical_products
UNION ALL SELECT id, name, price, 'digital' FROM digital_products
UNION ALL SELECT id, name, price, 'subscription' FROM subscription_products;