Database Management SystemCategories (Union Types)

Categories and Union Types in EER Modeling

LevelAdvanced

Duration60 mins

TopicCategories (Union Types)

2 / 5

Selective Inheritance in Categories

The Inheritance Puzzle

In traditional specialization hierarchies, inheritance follows a clear, predictable pattern: a subclass inherits all attributes and relationships from its superclass. An EMPLOYEE inherits everything from PERSON because every employee IS a person.

But categories present a fundamentally different scenario. A category has multiple superclasses, each with its own distinct set of attributes and relationships. If an OWNER can be a PERSON, COMPANY, or GOVERNMENT_AGENCY, what exactly does an OWNER instance inherit?

The answer is selective inheritance—a mechanism where each category instance inherits from exactly one superclass based on which superclass it actually belongs to. This seemingly simple concept has profound implications for database design, query construction, and system architecture.

What You Will Learn

By the end of this page, you will understand how selective inheritance works in categories, how to trace which attributes and relationships apply to specific category instances, and how this differs from traditional inheritance in specialization hierarchies.

Understanding Selective Inheritance

Selective inheritance is the inheritance mechanism in categories where each category instance inherits attributes and relationships from only the one superclass to which it belongs—not from all superclasses that define the category.

Formal Definition:

Given a category C defined by superclasses {S₁, S₂, ..., Sₙ}:

For any instance c ∈ C where c corresponds to an instance of Sᵢ
c inherits all attributes of Sᵢ
c inherits all relationships in which Sᵢ participates
c does NOT inherit attributes or relationships from any Sⱼ where j ≠ i

This is fundamentally different from multiple inheritance in object-oriented programming, where an object might inherit from all parent classes simultaneously.

Inheritance Comparison
Construct	Inheritance Mechanism	What Instance Inherits
Specialization (Single)	Full inheritance from one superclass	All superclass attributes and relationships
Shared Subclass (Multiple)	Full inheritance from all superclasses	All attributes and relationships from all superclasses
Category (Union Type)	Selective inheritance	Attributes and relationships from exactly ONE superclass

The Runtime Selection

In categories, inheritance is not statically determined at design time—it's dynamically determined per instance. Each category instance 'knows' which superclass it came from, and inheritance follows that path exclusively. This is why categories often require a discriminator attribute to track superclass membership.

Visualizing the Inheritance Path:

Consider the ACCOUNT_HOLDER category with three superclasses:

INDIVIDUAL                      CORPORATION                     TRUST
├── ssn (PK)                    ├── tax_id (PK)                 ├── trust_id (PK)
├── first_name                  ├── corporate_name              ├── trust_name
├── last_name                   ├── incorporation_date          ├── creation_date
├── date_of_birth               ├── state_of_incorporation      ├── trustee_name
├── home_address                ├── headquarters_address        ├── beneficiaries
└── driver_license_no           └── num_employees               └── grantor_name
                     
                      \          |          /
                       \         |         /
                        \        |        /
                         \       |       /
                          (ACCOUNT_HOLDER)
                           └── holder_id
                           └── holder_since
                           └── credit_rating

For a specific ACCOUNT_HOLDER instance:

If it's an INDIVIDUAL: inherits ssn, first_name, last_name, date_of_birth, home_address, driver_license_no
If it's a CORPORATION: inherits tax_id, corporate_name, incorporation_date, state_of_incorporation, headquarters_address, num_employees
If it's a TRUST: inherits trust_id, trust_name, creation_date, trustee_name, beneficiaries, grantor_name

Never a combination. Always exactly one path.

Selective Inheritance of Attributes

Let's examine attribute inheritance in detail, exploring the mechanics and implications of selective inheritance for category design.

Attribute Categories in Union Types:

When working with categories, attributes fall into three distinct groups:

1. Superclass-Specific Attributes These belong to individual superclasses and are inherited selectively:

INDIVIDUAL.date_of_birth → Only ACCOUNT_HOLDERS who are individuals have DOB
CORPORATION.num_employees → Only ACCOUNT_HOLDERS who are corporations have this
TRUST.beneficiaries → Only ACCOUNT_HOLDERS who are trusts have beneficiaries

2. Category-Own Attributes These belong to the category itself and apply to ALL category instances:

ACCOUNT_HOLDER.holder_id → Every account holder has this
ACCOUNT_HOLDER.holder_since → Every account holder has this
ACCOUNT_HOLDER.credit_rating → Every account holder has this

3. Derived/Computed Attributes These might be computed differently based on superclass type:

ACCOUNT_HOLDER.display_name → 
  IF INDIVIDUAL: first_name + last_name
  IF CORPORATION: corporate_name
  IF TRUST: trust_name

The Attribute Visibility Problem

This selective nature creates an interesting challenge: without knowing which superclass an instance belongs to, you cannot know which attributes are available. Querying 'date_of_birth' for all account holders would return NULL for corporations and trusts. This asymmetry must be handled in application logic and query design.

Attribute Inheritance Example: Insurance System
Attribute	Belongs To	Available When INSURED_ASSET Is...
vin	VEHICLE	A vehicle (car, truck, motorcycle)
make, model, year	VEHICLE	A vehicle
property_id	REAL_ESTATE	Real estate (house, building, land)
square_feet, lot_size	REAL_ESTATE	Real estate
hull_id	WATERCRAFT	A watercraft (boat, yacht)
boat_length, engine_type	WATERCRAFT	A watercraft
tail_number	AIRCRAFT	An aircraft
max_altitude, range_miles	AIRCRAFT	An aircraft
asset_id (surrogate)	INSURED_ASSET	Any insured asset (always available)
appraised_value	INSURED_ASSET	Any insured asset (always available)
last_inspection	INSURED_ASSET	Any insured asset (always available)

Tracing Attribute Availability:

When designing queries or application logic, always trace the inheritance path:

Query: Get name and age for all account holders

Logic:
FOR each ACCOUNT_HOLDER ah:
  IF ah.type = 'INDIVIDUAL':
    name = SELECT first_name || ' ' || last_name FROM INDIVIDUAL WHERE ssn = ah.ref_id
    age = CALCULATE_AGE(date_of_birth)
  ELIF ah.type = 'CORPORATION':
    name = SELECT corporate_name FROM CORPORATION WHERE tax_id = ah.ref_id
    age = NULL  -- corporations don't have age in the same sense
  ELIF ah.type = 'TRUST':
    name = SELECT trust_name FROM TRUST WHERE trust_id = ah.ref_id
    age = CALCULATE_AGE(creation_date)  -- trust "age" is its duration

This type-conditional logic is inherent to working with categories. The database schema cannot automatically unify semantically different attributes.

Selective Inheritance of Relationships

Beyond attributes, category instances also selectively inherit relationships from their specific superclass. This has significant implications for navigation and query complexity.

Relationship Inheritance Mechanics:

Each superclass in a category may participate in its own relationships that have nothing to do with the category's purpose. These relationships are inherited only by category instances of that superclass type.

Example: OWNER Category in Property Registration

PERSON                      COMPANY                     GOVERNMENT_AGENCY
├── ssn                     ├── company_id              ├── agency_id
├── name                    ├── company_name            ├── agency_name
│                           │                           │
├──<WORKS_FOR>──EMPLOYER    ├──<HAS_CEO>──PERSON        ├──<MANAGES>──JURISDICTION
│                           │                           │
├──<MARRIED_TO>──PERSON     ├──<HAS_SUBSIDIARY>──COMPANY ├──<REPORTS_TO>──AGENCY
│                           │                           │
└──<HAS_CHILDREN>──PERSON   └──<TRADED_ON>──STOCK_EXCHANGE └──<FUNDED_BY>──BUDGET

            \                    |                    /
             \                   |                   /
              \                  |                  /
               =============(OWNER)===============
                               │
                               │
                          <OWNS>
                               │
                          [PROPERTY]

Inherited vs. Category Relationships

The OWNS relationship between OWNER and PROPERTY is a category-level relationship—all owners participate in it regardless of type. But WORKS_FOR is a PERSON-only relationship. An OWNER who is a COMPANY cannot navigate WORKS_FOR because companies don't 'work for' employers.

Relationship Availability Matrix:

For complex systems, documenting which relationships are available for which category subtypes is essential:

Relationship	PERSON-OWNER	COMPANY-OWNER	GOVT_AGENCY-OWNER
OWNS (Property)	✓	✓	✓
WORKS_FOR (Employer)	✓	✗	✗
MARRIED_TO (Spouse)	✓	✗	✗
HAS_CEO (Executive)	✗	✓	✗
HAS_SUBSIDIARY	✗	✓	✗
MANAGES (Jurisdiction)	✗	✗	✓
REPORTS_TO (Parent Agency)	✗	✗	✓

This matrix becomes crucial for:

Query planning (knowing which joins are valid)
Application logic (avoiding invalid navigation)
API design (exposing appropriate endpoints)
Documentation (explaining system capabilities)

relationship_query_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- INCORRECT: Assumes all owners have employer relationship
SELECT o.owner_id, e.employer_name
FROM owner o
JOIN works_for wf ON o.ref_id = wf.person_ssn
JOIN employer e ON wf.employer_id = e.id;
-- ❌ Fails for COMPANY and GOVERNMENT_AGENCY owners
 
-- CORRECT: Type-aware query
SELECT 
    o.owner_id,
    CASE o.owner_type
        WHEN 'PERSON' THEN (
            SELECT e.employer_name 
            FROM works_for wf
            JOIN employer e ON wf.employer_id = e.id
            WHERE wf.person_ssn = o.ref_id
        )
        WHEN 'COMPANY' THEN 'N/A - Corporate Owner'
        WHEN 'GOVERNMENT_AGENCY' THEN 'N/A - Government Owner'
    END as employer_info
FROM owner o;
-- ✓ Handles all owner types appropriately

Discriminator Attributes

Because a category instance can belong to any of multiple superclasses, there must be a mechanism to determine which superclass applies to each instance. This mechanism is the discriminator attribute (also called type indicator or tag).

Discriminator Implementation:

A discriminator is typically a column in the category table that indicates the superclass type:

OWNER
├── owner_id (PK, surrogate key)
├── owner_type (discriminator: 'PERSON', 'COMPANY', 'GOVERNMENT')
├── ref_id (foreign key to appropriate superclass)
├── owner_since (category attribute)
└── credit_rating (category attribute)

The owner_type discriminator serves several critical functions:

Query routing — Determines which superclass table to join
Constraint enforcement — Ensures ref_id references the correct table
Application logic — Enables type-specific processing
Validation — Prevents invalid attribute access attempts

Referential Integrity Challenge

Standard foreign keys cannot enforce that ref_id points to the correct table based on owner_type. If owner_type='PERSON', ref_id should reference PERSON; if owner_type='COMPANY', it should reference COMPANY. This 'polymorphic foreign key' pattern typically requires triggers or application-level enforcement since declarative SQL FK constraints can only reference a single table.

discriminator_with_trigger.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Category table with discriminator
CREATE TABLE owner (
    owner_id       INT PRIMARY KEY AUTO_INCREMENT,
    owner_type     ENUM('PERSON', 'COMPANY', 'GOVERNMENT') NOT NULL,
    ref_id         VARCHAR(50) NOT NULL,  -- References different tables
    owner_since    DATE NOT NULL,
    credit_rating  CHAR(1) CHECK (credit_rating IN ('A','B','C','D','F'))
);
 
-- Trigger to enforce polymorphic referential integrity
DELIMITER //
CREATE TRIGGER validate_owner_ref
BEFORE INSERT ON owner
FOR EACH ROW
BEGIN
    DECLARE ref_exists INT DEFAULT 0;
    
    IF NEW.owner_type = 'PERSON' THEN
        SELECT COUNT(*) INTO ref_exists FROM person WHERE ssn = NEW.ref_id;
    ELSEIF NEW.owner_type = 'COMPANY' THEN
        SELECT COUNT(*) INTO ref_exists FROM company WHERE company_id = NEW.ref_id;
    ELSEIF NEW.owner_type = 'GOVERNMENT' THEN
        SELECT COUNT(*) INTO ref_exists FROM government_agency WHERE agency_id = NEW.ref_id;
    END IF;
    
    IF ref_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Referenced entity does not exist in expected superclass table';
    END IF;
END //
DELIMITER ;

Discriminator Design Patterns:

Pattern	Structure	Pros	Cons
Enum Column	owner_type ENUM(...)	Simple, readable, validated	Hard to extend, DB-specific
Char Code	owner_type CHAR(1)	Compact, portable	Less readable, needs documentation
Integer Code	owner_type_id INT + lookup table	Fully normalized, extensible	Extra join for type name
Table Name	ref_table_name VARCHAR(50)	Self-documenting	Verbose, validation harder

Best Practice: Use a lookup table for production systems—it's extensible, supports metadata (description, active flag), and enables referential integrity on the type code itself.

Selective Inheritance in Query Construction

Selective inheritance fundamentally affects how queries are constructed against category-based schemas. Unlike single-inheritance hierarchies where a simple JOIN suffices, category queries must account for multiple possible superclass sources.

Query Pattern 1: Type-Specific Query

When you need data for only one superclass type:

-- Get all corporate account holders with their company details
SELECT 
    ah.holder_id,
    ah.holder_since,
    c.corporate_name,
    c.num_employees,
    c.state_of_incorporation
FROM account_holder ah
JOIN corporation c ON ah.ref_id = c.tax_id
WHERE ah.holder_type = 'CORPORATION';

This is straightforward—filter by type, join to that superclass table.

Query Pattern 2: Unified Query Across Types

When you need a unified view of all category instances with their inherited attributes:

unified_category_query.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Unified view of all account holders with name and identifier
SELECT 
    ah.holder_id,
    ah.holder_since,
    ah.credit_rating,
    CASE ah.holder_type
        WHEN 'INDIVIDUAL' THEN i.first_name || ' ' || i.last_name
        WHEN 'CORPORATION' THEN c.corporate_name
        WHEN 'TRUST' THEN t.trust_name
    END AS holder_name,
    CASE ah.holder_type
        WHEN 'INDIVIDUAL' THEN i.ssn
        WHEN 'CORPORATION' THEN c.tax_id
        WHEN 'TRUST' THEN t.trust_id
    END AS holder_identifier,
    ah.holder_type
FROM account_holder ah
LEFT JOIN individual i ON ah.holder_type = 'INDIVIDUAL' AND ah.ref_id = i.ssn
LEFT JOIN corporation c ON ah.holder_type = 'CORPORATION' AND ah.ref_id = c.tax_id
LEFT JOIN trust t ON ah.holder_type = 'TRUST' AND ah.ref_id = t.trust_id;

LEFT JOIN Necessity

The LEFT JOINs are essential because each holder matches only ONE superclass table. An INDIVIDUAL holder has no matching row in CORPORATION or TRUST tables. Using INNER JOIN would eliminate the row entirely. The CASE statements then select from the one matching table.

Query Pattern 3: View-Based Abstraction

For frequently needed unified views, create a database view:

CREATE VIEW v_account_holder_unified AS
SELECT 
    ah.holder_id,
    ah.holder_since,
    ah.credit_rating,
    ah.holder_type,
    COALESCE(
        i.first_name || ' ' || i.last_name,
        c.corporate_name,
        t.trust_name
    ) AS holder_name,
    COALESCE(i.ssn, c.tax_id, t.trust_id) AS holder_identifier,
    -- Type-specific attributes as nullable columns
    i.date_of_birth,        -- NULL for non-individuals
    c.num_employees,        -- NULL for non-corporations
    t.beneficiaries         -- NULL for non-trusts
FROM account_holder ah
LEFT JOIN individual i ON ah.holder_type = 'INDIVIDUAL' AND ah.ref_id = i.ssn
LEFT JOIN corporation c ON ah.holder_type = 'CORPORATION' AND ah.ref_id = c.tax_id
LEFT JOIN trust t ON ah.holder_type = 'TRUST' AND ah.ref_id = t.trust_id;

This view provides:

Unified access without repetitive JOIN logic
All inherited attributes (with NULLs for non-applicable types)
Type discriminator for conditional logic
Foundation for application-layer data access

Selective Inheritance in Application Design

Selective inheritance has direct implications for object-oriented application design. The database category construct maps naturally to certain OOP patterns, but the mapping requires careful consideration.

Interface Pattern for Category Mapping

The category can be modeled as an interface that multiple unrelated classes implement:

interface_pattern.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
// Category as interface
interface AccountHolder {
  holderId: string;
  holderSince: Date;
  creditRating: 'A' | 'B' | 'C' | 'D' | 'F';
  
  // Contract methods that each type implements differently
  getDisplayName(): string;
  getIdentifier(): string;
  getHolderType(): 'INDIVIDUAL' | 'CORPORATION' | 'TRUST';
}
 
// Unrelated classes implementing the category interface
class Individual implements AccountHolder {
  holderId: string;
  holderSince: Date;
  creditRating: 'A' | 'B' | 'C' | 'D' | 'F';
  
  // Individual-specific attributes (inherited from INDIVIDUAL superclass)
  ssn: string;
  firstName: string;
  lastName: string;
  dateOfBirth: Date;
  
  getDisplayName(): string {
    return `${this.firstName} ${this.lastName}`;
  }
  
  getIdentifier(): string {
    return this.ssn;
  }
  
  getHolderType(): 'INDIVIDUAL' {
    return 'INDIVIDUAL';
  }
}
 
class Corporation implements AccountHolder {
  holderId: string;
  holderSince: Date;
  creditRating: 'A' | 'B' | 'C' | 'D' | 'F';
  
  // Corporation-specific attributes (inherited from CORPORATION superclass)
  taxId: string;
  corporateName: string;
  numEmployees: number;
  stateOfIncorporation: string;
  
  getDisplayName(): string {
    return this.corporateName;
  }
  
  getIdentifier(): string {
    return this.taxId;
  }
  
  getHolderType(): 'CORPORATION' {
    return 'CORPORATION';
  }
}

Design and Implementation Implications

Selective inheritance creates several important design and implementation considerations that affect system architecture beyond just database schema.

Key Implications to Consider

•Schema Fragmentation — Inherited attributes are distributed across multiple tables. A complete category instance requires joining to the appropriate superclass table, increasing query complexity.
•NULL Proliferation — Unified views necessarily contain NULL values for type-specific attributes. Applications must handle these appropriately without confusing 'value is null' with 'attribute doesn't apply'.
•Constraint Asymmetry — Type-specific constraints (Individual.date_of_birth NOT NULL) exist only in superclass tables. The category table cannot enforce them since not all types have these attributes.
•Index Strategy Complexity — Indexing strategies must account for discriminator-based access patterns. Queries filtering by type before joining require appropriate composite indexes.
•ORM Mapping Challenges — Object-Relational Mappers may struggle with polymorphic relationships. Custom mapping strategies or inheritance type hints might be necessary.
•API Design Considerations — APIs must decide whether to expose unified endpoints (returning heterogeneous objects) or type-specific endpoints (requiring clients to know types in advance).

Performance Consideration

Unified queries across category types require LEFT JOINs to all superclass tables, even though only one will match per row. For categories with many superclasses, this can impact performance. Consider materialized views or denormalized caching for read-heavy workloads.

Mitigation Strategies:

Materialized Views: Pre-compute unified views for frequently accessed queries
Type-Specific Caching: Cache fully hydrated objects by their category ID
JSONB Columns: Store type-specific attributes as JSON in the category table (trading normalization for query simplicity)
Event Sourcing: Capture changes at superclass level, project unified category views
Selective Loading: Load only category-level attributes initially, lazy-load superclass attributes when accessed

Summary: Selective Inheritance

We've thoroughly examined selective inheritance—the defining characteristic of how category instances inherit from their superclasses. Let's consolidate the key insights:

Key Takeaways

•One superclass per instance — Each category instance inherits from exactly one superclass, never multiple. This is determined per-instance, not at schema time.
•Discriminator is essential — A discriminator attribute tracks which superclass each instance belongs to, enabling proper query routing and application logic.
•Attributes are conditionally available — Superclass-specific attributes exist only for instances of that superclass type. Category-own attributes are universally available.
•Relationships follow the same pattern — Superclass relationships are inherited selectively. A category instance can only navigate relationships that its specific superclass participates in.
•Queries require type-awareness — Unified category queries use LEFT JOINs and CASE expressions to handle heterogeneous superclass sources.
•Application design must match — Object-oriented implementations typically use interfaces, discriminated unions, or visitor patterns to model category polymorphism.

What's Next:

Now that we understand how selective inheritance works, we'll explore partial categories—scenarios where not all superclass instances participate in the category. This distinction affects constraint enforcement and has important semantic implications for the relationships the category represents.

Page Complete

You now understand the mechanics of selective inheritance in categories—how instances inherit from exactly one superclass, how discriminators track this membership, and how queries and applications must account for this selective nature. Next, we'll examine partial vs. total category participation constraints.

2 / 5

Loading learning content...

Database Management SystemCategories (Union Types)

Categories and Union Types in EER Modeling

LevelAdvanced

Duration60 mins

TopicCategories (Union Types)

2 / 5

Selective Inheritance in Categories

The Inheritance Puzzle

What You Will Learn

Understanding Selective Inheritance

Formal Definition:

Given a category C defined by superclasses {S₁, S₂, ..., Sₙ}:

For any instance c ∈ C where c corresponds to an instance of Sᵢ
c inherits all attributes of Sᵢ
c inherits all relationships in which Sᵢ participates
c does NOT inherit attributes or relationships from any Sⱼ where j ≠ i

This is fundamentally different from multiple inheritance in object-oriented programming, where an object might inherit from all parent classes simultaneously.

Inheritance Comparison
Construct	Inheritance Mechanism	What Instance Inherits
Specialization (Single)	Full inheritance from one superclass	All superclass attributes and relationships
Shared Subclass (Multiple)	Full inheritance from all superclasses	All attributes and relationships from all superclasses
Category (Union Type)	Selective inheritance	Attributes and relationships from exactly ONE superclass

The Runtime Selection

Visualizing the Inheritance Path:

Consider the ACCOUNT_HOLDER category with three superclasses:

INDIVIDUAL                      CORPORATION                     TRUST
├── ssn (PK)                    ├── tax_id (PK)                 ├── trust_id (PK)
├── first_name                  ├── corporate_name              ├── trust_name
├── last_name                   ├── incorporation_date          ├── creation_date
├── date_of_birth               ├── state_of_incorporation      ├── trustee_name
├── home_address                ├── headquarters_address        ├── beneficiaries
└── driver_license_no           └── num_employees               └── grantor_name
                     
                      \          |          /
                       \         |         /
                        \        |        /
                         \       |       /
                          (ACCOUNT_HOLDER)
                           └── holder_id
                           └── holder_since
                           └── credit_rating

For a specific ACCOUNT_HOLDER instance:

If it's an INDIVIDUAL: inherits ssn, first_name, last_name, date_of_birth, home_address, driver_license_no
If it's a CORPORATION: inherits tax_id, corporate_name, incorporation_date, state_of_incorporation, headquarters_address, num_employees
If it's a TRUST: inherits trust_id, trust_name, creation_date, trustee_name, beneficiaries, grantor_name

Never a combination. Always exactly one path.

Selective Inheritance of Attributes

Let's examine attribute inheritance in detail, exploring the mechanics and implications of selective inheritance for category design.

Attribute Categories in Union Types:

When working with categories, attributes fall into three distinct groups:

1. Superclass-Specific Attributes These belong to individual superclasses and are inherited selectively:

INDIVIDUAL.date_of_birth → Only ACCOUNT_HOLDERS who are individuals have DOB
CORPORATION.num_employees → Only ACCOUNT_HOLDERS who are corporations have this
TRUST.beneficiaries → Only ACCOUNT_HOLDERS who are trusts have beneficiaries

2. Category-Own Attributes These belong to the category itself and apply to ALL category instances:

ACCOUNT_HOLDER.holder_id → Every account holder has this
ACCOUNT_HOLDER.holder_since → Every account holder has this
ACCOUNT_HOLDER.credit_rating → Every account holder has this

3. Derived/Computed Attributes These might be computed differently based on superclass type:

ACCOUNT_HOLDER.display_name → 
  IF INDIVIDUAL: first_name + last_name
  IF CORPORATION: corporate_name
  IF TRUST: trust_name

The Attribute Visibility Problem

Attribute Inheritance Example: Insurance System
Attribute	Belongs To	Available When INSURED_ASSET Is...
vin	VEHICLE	A vehicle (car, truck, motorcycle)
make, model, year	VEHICLE	A vehicle
property_id	REAL_ESTATE	Real estate (house, building, land)
square_feet, lot_size	REAL_ESTATE	Real estate
hull_id	WATERCRAFT	A watercraft (boat, yacht)
boat_length, engine_type	WATERCRAFT	A watercraft
tail_number	AIRCRAFT	An aircraft
max_altitude, range_miles	AIRCRAFT	An aircraft
asset_id (surrogate)	INSURED_ASSET	Any insured asset (always available)
appraised_value	INSURED_ASSET	Any insured asset (always available)
last_inspection	INSURED_ASSET	Any insured asset (always available)

Tracing Attribute Availability:

When designing queries or application logic, always trace the inheritance path:

Query: Get name and age for all account holders

Logic:
FOR each ACCOUNT_HOLDER ah:
  IF ah.type = 'INDIVIDUAL':
    name = SELECT first_name || ' ' || last_name FROM INDIVIDUAL WHERE ssn = ah.ref_id
    age = CALCULATE_AGE(date_of_birth)
  ELIF ah.type = 'CORPORATION':
    name = SELECT corporate_name FROM CORPORATION WHERE tax_id = ah.ref_id
    age = NULL  -- corporations don't have age in the same sense
  ELIF ah.type = 'TRUST':
    name = SELECT trust_name FROM TRUST WHERE trust_id = ah.ref_id
    age = CALCULATE_AGE(creation_date)  -- trust "age" is its duration

This type-conditional logic is inherent to working with categories. The database schema cannot automatically unify semantically different attributes.

Selective Inheritance of Relationships

Beyond attributes, category instances also selectively inherit relationships from their specific superclass. This has significant implications for navigation and query complexity.

Relationship Inheritance Mechanics:

Example: OWNER Category in Property Registration

PERSON                      COMPANY                     GOVERNMENT_AGENCY
├── ssn                     ├── company_id              ├── agency_id
├── name                    ├── company_name            ├── agency_name
│                           │                           │
├──<WORKS_FOR>──EMPLOYER    ├──<HAS_CEO>──PERSON        ├──<MANAGES>──JURISDICTION
│                           │                           │
├──<MARRIED_TO>──PERSON     ├──<HAS_SUBSIDIARY>──COMPANY ├──<REPORTS_TO>──AGENCY
│                           │                           │
└──<HAS_CHILDREN>──PERSON   └──<TRADED_ON>──STOCK_EXCHANGE └──<FUNDED_BY>──BUDGET

            \                    |                    /
             \                   |                   /
              \                  |                  /
               =============(OWNER)===============
                               │
                               │
                          <OWNS>
                               │
                          [PROPERTY]

Inherited vs. Category Relationships

Relationship Availability Matrix:

For complex systems, documenting which relationships are available for which category subtypes is essential:

Relationship	PERSON-OWNER	COMPANY-OWNER	GOVT_AGENCY-OWNER
OWNS (Property)	✓	✓	✓
WORKS_FOR (Employer)	✓	✗	✗
MARRIED_TO (Spouse)	✓	✗	✗
HAS_CEO (Executive)	✗	✓	✗
HAS_SUBSIDIARY	✗	✓	✗
MANAGES (Jurisdiction)	✗	✗	✓
REPORTS_TO (Parent Agency)	✗	✗	✓

This matrix becomes crucial for:

Query planning (knowing which joins are valid)
Application logic (avoiding invalid navigation)
API design (exposing appropriate endpoints)
Documentation (explaining system capabilities)

relationship_query_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- INCORRECT: Assumes all owners have employer relationship
SELECT o.owner_id, e.employer_name
FROM owner o
JOIN works_for wf ON o.ref_id = wf.person_ssn
JOIN employer e ON wf.employer_id = e.id;
-- ❌ Fails for COMPANY and GOVERNMENT_AGENCY owners
 
-- CORRECT: Type-aware query
SELECT 
    o.owner_id,
    CASE o.owner_type
        WHEN 'PERSON' THEN (
            SELECT e.employer_name 
            FROM works_for wf
            JOIN employer e ON wf.employer_id = e.id
            WHERE wf.person_ssn = o.ref_id
        )
        WHEN 'COMPANY' THEN 'N/A - Corporate Owner'
        WHEN 'GOVERNMENT_AGENCY' THEN 'N/A - Government Owner'
    END as employer_info
FROM owner o;
-- ✓ Handles all owner types appropriately

Discriminator Attributes

Discriminator Implementation:

A discriminator is typically a column in the category table that indicates the superclass type:

OWNER
├── owner_id (PK, surrogate key)
├── owner_type (discriminator: 'PERSON', 'COMPANY', 'GOVERNMENT')
├── ref_id (foreign key to appropriate superclass)
├── owner_since (category attribute)
└── credit_rating (category attribute)

The owner_type discriminator serves several critical functions:

Query routing — Determines which superclass table to join
Constraint enforcement — Ensures ref_id references the correct table
Application logic — Enables type-specific processing
Validation — Prevents invalid attribute access attempts

Referential Integrity Challenge

discriminator_with_trigger.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Category table with discriminator
CREATE TABLE owner (
    owner_id       INT PRIMARY KEY AUTO_INCREMENT,
    owner_type     ENUM('PERSON', 'COMPANY', 'GOVERNMENT') NOT NULL,
    ref_id         VARCHAR(50) NOT NULL,  -- References different tables
    owner_since    DATE NOT NULL,
    credit_rating  CHAR(1) CHECK (credit_rating IN ('A','B','C','D','F'))
);
 
-- Trigger to enforce polymorphic referential integrity
DELIMITER //
CREATE TRIGGER validate_owner_ref
BEFORE INSERT ON owner
FOR EACH ROW
BEGIN
    DECLARE ref_exists INT DEFAULT 0;
    
    IF NEW.owner_type = 'PERSON' THEN
        SELECT COUNT(*) INTO ref_exists FROM person WHERE ssn = NEW.ref_id;
    ELSEIF NEW.owner_type = 'COMPANY' THEN
        SELECT COUNT(*) INTO ref_exists FROM company WHERE company_id = NEW.ref_id;
    ELSEIF NEW.owner_type = 'GOVERNMENT' THEN
        SELECT COUNT(*) INTO ref_exists FROM government_agency WHERE agency_id = NEW.ref_id;
    END IF;
    
    IF ref_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Referenced entity does not exist in expected superclass table';
    END IF;
END //
DELIMITER ;

Discriminator Design Patterns:

Pattern	Structure	Pros	Cons
Enum Column	owner_type ENUM(...)	Simple, readable, validated	Hard to extend, DB-specific
Char Code	owner_type CHAR(1)	Compact, portable	Less readable, needs documentation
Integer Code	owner_type_id INT + lookup table	Fully normalized, extensible	Extra join for type name
Table Name	ref_table_name VARCHAR(50)	Self-documenting	Verbose, validation harder

Best Practice: Use a lookup table for production systems—it's extensible, supports metadata (description, active flag), and enables referential integrity on the type code itself.

Selective Inheritance in Query Construction

Query Pattern 1: Type-Specific Query

When you need data for only one superclass type:

-- Get all corporate account holders with their company details
SELECT 
    ah.holder_id,
    ah.holder_since,
    c.corporate_name,
    c.num_employees,
    c.state_of_incorporation
FROM account_holder ah
JOIN corporation c ON ah.ref_id = c.tax_id
WHERE ah.holder_type = 'CORPORATION';

This is straightforward—filter by type, join to that superclass table.

Query Pattern 2: Unified Query Across Types

When you need a unified view of all category instances with their inherited attributes:

unified_category_query.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Unified view of all account holders with name and identifier
SELECT 
    ah.holder_id,
    ah.holder_since,
    ah.credit_rating,
    CASE ah.holder_type
        WHEN 'INDIVIDUAL' THEN i.first_name || ' ' || i.last_name
        WHEN 'CORPORATION' THEN c.corporate_name
        WHEN 'TRUST' THEN t.trust_name
    END AS holder_name,
    CASE ah.holder_type
        WHEN 'INDIVIDUAL' THEN i.ssn
        WHEN 'CORPORATION' THEN c.tax_id
        WHEN 'TRUST' THEN t.trust_id
    END AS holder_identifier,
    ah.holder_type
FROM account_holder ah
LEFT JOIN individual i ON ah.holder_type = 'INDIVIDUAL' AND ah.ref_id = i.ssn
LEFT JOIN corporation c ON ah.holder_type = 'CORPORATION' AND ah.ref_id = c.tax_id
LEFT JOIN trust t ON ah.holder_type = 'TRUST' AND ah.ref_id = t.trust_id;

LEFT JOIN Necessity

Query Pattern 3: View-Based Abstraction

For frequently needed unified views, create a database view:

CREATE VIEW v_account_holder_unified AS
SELECT 
    ah.holder_id,
    ah.holder_since,
    ah.credit_rating,
    ah.holder_type,
    COALESCE(
        i.first_name || ' ' || i.last_name,
        c.corporate_name,
        t.trust_name
    ) AS holder_name,
    COALESCE(i.ssn, c.tax_id, t.trust_id) AS holder_identifier,
    -- Type-specific attributes as nullable columns
    i.date_of_birth,        -- NULL for non-individuals
    c.num_employees,        -- NULL for non-corporations
    t.beneficiaries         -- NULL for non-trusts
FROM account_holder ah
LEFT JOIN individual i ON ah.holder_type = 'INDIVIDUAL' AND ah.ref_id = i.ssn
LEFT JOIN corporation c ON ah.holder_type = 'CORPORATION' AND ah.ref_id = c.tax_id
LEFT JOIN trust t ON ah.holder_type = 'TRUST' AND ah.ref_id = t.trust_id;

This view provides:

Unified access without repetitive JOIN logic
All inherited attributes (with NULLs for non-applicable types)
Type discriminator for conditional logic
Foundation for application-layer data access

Selective Inheritance in Application Design

Interface Pattern for Category Mapping

The category can be modeled as an interface that multiple unrelated classes implement:

interface_pattern.ts
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
// Category as interface
interface AccountHolder {
  holderId: string;
  holderSince: Date;
  creditRating: 'A' | 'B' | 'C' | 'D' | 'F';
  
  // Contract methods that each type implements differently
  getDisplayName(): string;
  getIdentifier(): string;
  getHolderType(): 'INDIVIDUAL' | 'CORPORATION' | 'TRUST';
}
 
// Unrelated classes implementing the category interface
class Individual implements AccountHolder {
  holderId: string;
  holderSince: Date;
  creditRating: 'A' | 'B' | 'C' | 'D' | 'F';
  
  // Individual-specific attributes (inherited from INDIVIDUAL superclass)
  ssn: string;
  firstName: string;
  lastName: string;
  dateOfBirth: Date;
  
  getDisplayName(): string {
    return `${this.firstName} ${this.lastName}`;
  }
  
  getIdentifier(): string {
    return this.ssn;
  }
  
  getHolderType(): 'INDIVIDUAL' {
    return 'INDIVIDUAL';
  }
}
 
class Corporation implements AccountHolder {
  holderId: string;
  holderSince: Date;
  creditRating: 'A' | 'B' | 'C' | 'D' | 'F';
  
  // Corporation-specific attributes (inherited from CORPORATION superclass)
  taxId: string;
  corporateName: string;
  numEmployees: number;
  stateOfIncorporation: string;
  
  getDisplayName(): string {
    return this.corporateName;
  }
  
  getIdentifier(): string {
    return this.taxId;
  }
  
  getHolderType(): 'CORPORATION' {
    return 'CORPORATION';
  }
}

Design and Implementation Implications

Selective inheritance creates several important design and implementation considerations that affect system architecture beyond just database schema.

Key Implications to Consider

•Schema Fragmentation — Inherited attributes are distributed across multiple tables. A complete category instance requires joining to the appropriate superclass table, increasing query complexity.
•NULL Proliferation — Unified views necessarily contain NULL values for type-specific attributes. Applications must handle these appropriately without confusing 'value is null' with 'attribute doesn't apply'.
•Constraint Asymmetry — Type-specific constraints (Individual.date_of_birth NOT NULL) exist only in superclass tables. The category table cannot enforce them since not all types have these attributes.
•Index Strategy Complexity — Indexing strategies must account for discriminator-based access patterns. Queries filtering by type before joining require appropriate composite indexes.
•ORM Mapping Challenges — Object-Relational Mappers may struggle with polymorphic relationships. Custom mapping strategies or inheritance type hints might be necessary.
•API Design Considerations — APIs must decide whether to expose unified endpoints (returning heterogeneous objects) or type-specific endpoints (requiring clients to know types in advance).

Performance Consideration

Mitigation Strategies:

Materialized Views: Pre-compute unified views for frequently accessed queries
Type-Specific Caching: Cache fully hydrated objects by their category ID
JSONB Columns: Store type-specific attributes as JSON in the category table (trading normalization for query simplicity)
Event Sourcing: Capture changes at superclass level, project unified category views
Selective Loading: Load only category-level attributes initially, lazy-load superclass attributes when accessed

Summary: Selective Inheritance

We've thoroughly examined selective inheritance—the defining characteristic of how category instances inherit from their superclasses. Let's consolidate the key insights:

Key Takeaways

•One superclass per instance — Each category instance inherits from exactly one superclass, never multiple. This is determined per-instance, not at schema time.
•Discriminator is essential — A discriminator attribute tracks which superclass each instance belongs to, enabling proper query routing and application logic.
•Attributes are conditionally available — Superclass-specific attributes exist only for instances of that superclass type. Category-own attributes are universally available.
•Relationships follow the same pattern — Superclass relationships are inherited selectively. A category instance can only navigate relationships that its specific superclass participates in.
•Queries require type-awareness — Unified category queries use LEFT JOINs and CASE expressions to handle heterogeneous superclass sources.
•Application design must match — Object-oriented implementations typically use interfaces, discriminated unions, or visitor patterns to model category polymorphism.

What's Next:

Page Complete

2 / 5