Subtype Creation: Building… — Specialization in… | OneNoughtOne

0/278

Database Management SystemsSpecialization

Specialization in Enhanced ER Modeling

LevelIntermediate

Duration55 mins

TopicSpecialization

2 / 5

Subtype Creation: Building Specialization Hierarchies

From Concept to Construction

Understanding the concept of specialization is one thing; constructing effective specialization hierarchies is quite another. The practical work of subtype creation involves critical decisions: What distinguishes one subtype from another? How do you formalize the membership criteria? How deep should the hierarchy go? When should you stop specializing? These questions don't have mechanical answers—they require judgment informed by domain understanding, anticipated query patterns, and application requirements. This page equips you with the systematic approach to make these decisions well.

What You Will Learn

By the end of this page, you will master the practical mechanics of subtype creation: selecting discriminators, defining membership predicates, constructing multi-level hierarchies, and recognizing when specialization is complete. You'll develop the judgment to build hierarchies that are semantically precise, practically useful, and maintainable over time.

The Subtype Creation Process

Creating subtypes is a structured process that balances formality with practical domain analysis. Here is a systematic approach: Step 1: Identify the Supertype Begin with a well-defined entity type that exhibits categorical variation. This entity should have: A clear identity (primary key) Shared attributes common to all instances Evidence that instances can be meaningfully categorized Step 2: Discover Distinguishing Characteristics Analyze what differentiates categories of instances. Ask: Do different categories have different attributes? Do different categories participate in different relationships? Do business rules vary by category? Are there different lifecycle behaviors? Step 3: Define the Discriminator Formalize what distinguishes subtypes—the discriminator (or defining predicate). This can be: An explicit attribute whose values map to subtypes An implicit semantic category A derived condition on attribute values Step 4: Specify Subtype Definitions For each subtype, define: The membership criterion (predicate) Local attributes (specific to this subtype) Local relationships (specific to this subtype) Additional constraints (applicable only to this subtype) Step 5: Determine Constraints Decide on specialization constraints: Disjoint vs. Overlapping : Can an entity belong to multiple subtypes? Total vs. Partial : Must every entity belong to some subtype?

Converting Mermaid diagram...

Discriminator Selection Strategies

The discriminator is the attribute or condition that determines which subtype(s) an entity belongs to. Choosing the right discriminator is crucial—it affects schema clarity, query efficiency, and maintainability. Types of Discriminators:

Approach : A single attribute in the superclass explicitly indicates subtype membership. Example : EMPLOYEE with attribute job_category having values 'MANAGER', 'ENGINEER', 'SALES' EMPLOYEE(emp_id PK, name, salary, job_category) ↓ discriminator = job_category ├── MANAGER (job_category = 'MANAGER') ├── ENGINEER (job_category = 'ENGINEER') └── SALESPERSON (job_category = 'SALES') Advantages: Direct, simple membership testing Self-documenting schema Easy evolution (add new subtype by adding new discriminator value) Efficient indexing on discriminator column Considerations: Requires maintaining value in sync with actual subtype Overlapping subtypes need multi-value support (array or separate flags)

Discriminator Best Practices

Choose explicit attribute discriminators when subtypes are stable categories that don't change based on data values. Choose condition-based discriminators when subtype membership genuinely derives from attribute values. Avoid implicit discriminators unless the conceptual model specifically benefits from abstraction—physical design will add them anyway.

Defining Subtype Predicates

Every subtype has a defining predicate —a logical condition that specifies which supertype entities belong to this subtype. Well-crafted predicates are essential for: Schema clarity (what does this subtype mean?) Constraint enforcement (can we validate membership?) Query optimization (efficient subtype filtering) Application logic (correct routing of entities) Predicate Components: A defining predicate typically has three components: Base reference : Reference to the supertype (e.g., "For an Employee e...") Membership condition : The logical condition (e.g., "...where e.job_category = 'MANAGER'...") Scope : Whether the condition is invariant or can change over entity lifecycle

defining-predicates.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Formal Predicate Definitions
 
-- Simple Value-Based Predicate
MANAGER ⊆ EMPLOYEE where job_category = 'MANAGER'
 
-- Range-Based Predicate  
PREMIUM_ACCOUNT ⊆ ACCOUNT where balance >= 100000 AND balance < 1000000
 
-- Multi-Condition Predicate
ACTIVE_PREMIUM_CUSTOMER ⊆ CUSTOMER 
    where account_type = 'PREMIUM' 
    AND last_activity_date > CURRENT_DATE - INTERVAL '90 days'
    AND status = 'ACTIVE'
 
-- Relationship-Based Predicate (subtype defined by participation in relationship)
SUPERVISING_EMPLOYEE ⊆ EMPLOYEE 
    where EXISTS (SELECT 1 FROM SUPERVISION WHERE supervisor_id = employee_id)
 
-- Derived/Computed Predicate (subtype based on aggregate or computed value)
HIGH_VOLUME_CUSTOMER ⊆ CUSTOMER
    where (SELECT COUNT(*) FROM ORDERS WHERE customer_id = id) > 100

Predicate Quality Criteria: Well-designed predicates satisfy these properties:

Predicate Quality Checklist

•Decidable — Given any supertype entity, you can definitively determine membership (no ambiguity)
•Efficient — The condition can be evaluated efficiently against the database (indexable, not requiring full scans)
•Stable — Membership doesn't change unexpectedly due to unrelated data modifications
•Meaningful — The predicate reflects genuine domain semantics, not arbitrary technical divisions
•Enforceable — The database can validate the predicate (constraints can be written for it)

Beware of Unstable Predicates

Condition-based predicates can create unstable subtype membership where entities silently move between subtypes as attribute values change. If PREMIUM_ACCOUNT is defined by balance >= 100000, a withdrawal below that threshold instantly changes the entity's type. Ensure your application logic handles such changes gracefully, or choose more stable discriminators.

Constructing Multi-Level Hierarchies

Real-world domains often require multi-level specialization hierarchies where subtypes are themselves further specialized. Building these hierarchies requires careful attention to inheritance chains, discriminator consistency, and semantic coherence. Example: Academic Institution Personnel Hierarchy Consider modeling personnel at a university:

Converting Mermaid diagram...

Hierarchy Design Principles:

•Semantic coherence at each level — Each level of specialization should reflect meaningful domain distinctions. Don't add levels just for structural convenience.
•Consistent discriminator basis — Within a single specialization (siblings), the discriminator should be consistent. FACULTY specializes by 'faculty_type' (Professor/Instructor), PROFESSOR specializes by 'tenure_status'.
•Attribute accumulation — Each level adds local attributes while inheriting all ancestor attributes. TENURED_PROFESSOR has: tenure_date (local) + tenure_status, publications (from Professor) + rank, department (from Faculty) + hire_date, salary (from Employee) + SSN, name, address, DOB (from Person).
•Relationship accumulation — Similarly, relationships are inherited down the hierarchy. If Person LIVES-AT Address, all descendants participate in this relationship.
•Constraint refinement — Subtypes can add constraints but shouldn't contradict supertype constraints. If Employee.salary > 0, all Faculty and Staff must satisfy this.

Depth Considerations

While there's no absolute limit, hierarchies deeper than 4-5 levels often indicate over-specialization. Each level adds schema complexity, query join depth (in some mapping strategies), and cognitive load for developers. Balance semantic accuracy against practical manageability.

When to Stop Specializing

Specialization is a powerful tool, but over-application leads to schema bloat, unnecessary complexity, and maintenance burden. Knowing when to stop specializing is as important as knowing when to start . Stop Specializing When:

Signs to Stop

•No local attributes — Subtype has no attributes beyond inherited ones. Just use a discriminator attribute on supertype.
•No local relationships — Subtype doesn't participate in unique relationships. The distinction is purely nominal.
•No distinct constraints — No business rules specific to this subtype. The categorization is informational, not structural.
•Single instance expected — If only one entity will ever be in this subtype, model it as an attribute, not a subtype.
•Volatile categorization — If the categorization will change frequently or is tentative, an attribute is more flexible than schema structure.

Signs to Continue

•Distinct local attributes — Subtype has meaningful attributes that don't apply to siblings.
•Distinct relationships — Subtype participates in relationships that siblings don't.
•Different constraints — Subtype-specific business rules require enforcement.
•Different behaviors — Applications treat this subtype differently (different UI, different processing).
•Query patterns — Users frequently query for 'all X-type entities' as a distinct set.

The Attribute vs. Subtype Decision: Consider this scenario: You have PRODUCT entity and want to track whether products are perishable. Option A: Attribute PRODUCT(product_id, name, price, is_perishable BOOLEAN) Option B: Subtype PRODUCT(product_id, name, price) ├── PERISHABLE_PRODUCT(expiry_tracking_method, shelf_life_days, requires_refrigeration) └── NON_PERISHABLE_PRODUCT(storage_requirements) Which is correct? It depends: If perishable products need extra attributes (expiry tracking, refrigeration requirements) and non-perishable products have their own attributes (different storage), use subtypes . If 'perishable' is just a flag with no structural implications, use an attribute . If you're unsure, start with an attribute and refactor to subtypes if structural distinctions emerge.

The YAGNI Principle

Apply You Aren't Gonna Need It to specialization. Don't create subtypes for hypothetical future distinctions. Create them when the current requirements demonstrate clear structural differences. Adding a subtype later is straightforward; removing an unnecessary one is more disruptive.

Practical Example: Banking Account Hierarchy

Let's walk through a complete subtype creation exercise for a banking domain. This example demonstrates the full process from requirements to complete hierarchy definition.

Requirements

A bank manages various account types. All accounts have an account number, holder information, balance, and open date. Savings accounts earn interest and have withdrawal limits. Checking accounts have overdraft protection options and check-writing capability. Investment accounts hold portfolios with associated risk levels and investment strategies. Some investment accounts are retirement accounts with contribution limits and tax advantages; others are brokerage accounts with margin trading options.

Step 1: Identify Supertype ACCOUNT is the clear supertype with shared attributes: account_number, holder_id, balance, open_date, status. Step 2: Discover Distinguishing Characteristics Savings: interest_rate, withdrawal_limit, compound_frequency Checking: overdraft_limit, overdraft_enabled, check_number_sequence Investment: portfolio_id, risk_level, investment_strategy Step 3: Define Discriminator Explicit attribute: account_type ∈ {'SAVINGS', 'CHECKING', 'INVESTMENT'} Step 4: Second-Level Specialization (Investment) Retirement: contribution_limit, tax_advantage_type, early_withdrawal_penalty Brokerage: margin_enabled, margin_limit, settlement_period Sub-discriminator: investment_type ∈ {'RETIREMENT', 'BROKERAGE'}

Converting Mermaid diagram...

banking-hierarchy-predicates.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Formal Predicate Definitions for Banking Hierarchy
 
-- Level 1: Account specialization
SAVINGS_ACCOUNT    ⊆ ACCOUNT where account_type = 'SAVINGS'
CHECKING_ACCOUNT   ⊆ ACCOUNT where account_type = 'CHECKING'
INVESTMENT_ACCOUNT ⊆ ACCOUNT where account_type = 'INVESTMENT'
 
-- Level 2: Investment Account specialization
RETIREMENT_ACCOUNT ⊆ INVESTMENT_ACCOUNT where investment_type = 'RETIREMENT'
BROKERAGE_ACCOUNT  ⊆ INVESTMENT_ACCOUNT where investment_type = 'BROKERAGE'
 
-- Constraint: Level 1 is disjoint and total
-- Every account must be exactly one of: SAVINGS, CHECKING, or INVESTMENT
-- account_type IN ('SAVINGS', 'CHECKING', 'INVESTMENT') -- exhaustive
-- account_type is single-valued -- disjoint
 
-- Constraint: Level 2 is disjoint and total (for INVESTMENT accounts)
-- Every investment account must be either RETIREMENT or BROKERAGE
-- investment_type IN ('RETIREMENT', 'BROKERAGE') -- exhaustive within investment
-- investment_type is single-valued -- disjoint

Common Subtype Creation Errors

Effective subtype creation requires avoiding common pitfalls. Understanding these errors helps you review and improve your designs.

Common Subtype Creation Errors and Corrections
Error	Problem	Correction
Over-Specialization	Creating subtypes for every possible distinction, resulting in hundreds of types with minimal structural differences.	Apply the 'local attributes test': no local attributes or relationships = no separate subtype.
Under-Specialization	Forcing all variations into a single entity with many nullable attributes, losing semantic clarity.	If different instances need genuinely different attributes, model as subtypes.
Mixing Discriminator Bases	Using different criteria for sibling subtypes (one by role, another by status).	Siblings should be distinguished by the same discriminator type.
Temporal Confusion	Modeling lifecycle states as permanent subtypes when entities move between states.	Use state attributes or state machines for temporal categories; use subtypes for permanent categories.
Identity Fragmentation	Making the same real-world entity different entities in different subtypes.	Subtypes share supertype identity. A MANAGER and ENGINEER can be the same EMPLOYEE.
Inconsistent Depth	Some branches of hierarchy go many levels deep while siblings remain shallow without justification.	Hierarchy depth should reflect domain complexity, not arbitrary structure.

The Temporal Trap

A particularly common error is modeling states as types . Consider: Should ORDER have subtypes PENDING_ORDER, SHIPPED_ORDER, COMPLETED_ORDER? Usually NO—the order doesn't change identity when shipped; it changes state. Use a status attribute unless state transitions involve structural changes (different attributes needed in different states).

Database Management SystemsSpecialization

Specialization in Enhanced ER Modeling

LevelIntermediate

Duration55 mins

TopicSpecialization

2 / 5

Subtype Creation: Building Specialization Hierarchies

From Concept to Construction

Understanding the concept of specialization is one thing; constructing effective specialization hierarchies is quite another. The practical work of subtype creation involves critical decisions: What distinguishes one subtype from another? How do you formalize the membership criteria? How deep should the hierarchy go? When should you stop specializing? These questions don't have mechanical answers—they require judgment informed by domain understanding, anticipated query patterns, and application requirements. This page equips you with the systematic approach to make these decisions well.

What You Will Learn

By the end of this page, you will master the practical mechanics of subtype creation: selecting discriminators, defining membership predicates, constructing multi-level hierarchies, and recognizing when specialization is complete. You'll develop the judgment to build hierarchies that are semantically precise, practically useful, and maintainable over time.

The Subtype Creation Process

Creating subtypes is a structured process that balances formality with practical domain analysis. Here is a systematic approach: Step 1: Identify the Supertype Begin with a well-defined entity type that exhibits categorical variation. This entity should have: A clear identity (primary key) Shared attributes common to all instances Evidence that instances can be meaningfully categorized Step 2: Discover Distinguishing Characteristics Analyze what differentiates categories of instances. Ask: Do different categories have different attributes? Do different categories participate in different relationships? Do business rules vary by category? Are there different lifecycle behaviors? Step 3: Define the Discriminator Formalize what distinguishes subtypes—the discriminator (or defining predicate). This can be: An explicit attribute whose values map to subtypes An implicit semantic category A derived condition on attribute values Step 4: Specify Subtype Definitions For each subtype, define: The membership criterion (predicate) Local attributes (specific to this subtype) Local relationships (specific to this subtype) Additional constraints (applicable only to this subtype) Step 5: Determine Constraints Decide on specialization constraints: Disjoint vs. Overlapping : Can an entity belong to multiple subtypes? Total vs. Partial : Must every entity belong to some subtype?

Converting Mermaid diagram...

Discriminator Selection Strategies

The discriminator is the attribute or condition that determines which subtype(s) an entity belongs to. Choosing the right discriminator is crucial—it affects schema clarity, query efficiency, and maintainability. Types of Discriminators:

Approach : A single attribute in the superclass explicitly indicates subtype membership. Example : EMPLOYEE with attribute job_category having values 'MANAGER', 'ENGINEER', 'SALES' EMPLOYEE(emp_id PK, name, salary, job_category) ↓ discriminator = job_category ├── MANAGER (job_category = 'MANAGER') ├── ENGINEER (job_category = 'ENGINEER') └── SALESPERSON (job_category = 'SALES') Advantages: Direct, simple membership testing Self-documenting schema Easy evolution (add new subtype by adding new discriminator value) Efficient indexing on discriminator column Considerations: Requires maintaining value in sync with actual subtype Overlapping subtypes need multi-value support (array or separate flags)

Discriminator Best Practices

Choose explicit attribute discriminators when subtypes are stable categories that don't change based on data values. Choose condition-based discriminators when subtype membership genuinely derives from attribute values. Avoid implicit discriminators unless the conceptual model specifically benefits from abstraction—physical design will add them anyway.

Defining Subtype Predicates

Every subtype has a defining predicate —a logical condition that specifies which supertype entities belong to this subtype. Well-crafted predicates are essential for: Schema clarity (what does this subtype mean?) Constraint enforcement (can we validate membership?) Query optimization (efficient subtype filtering) Application logic (correct routing of entities) Predicate Components: A defining predicate typically has three components: Base reference : Reference to the supertype (e.g., "For an Employee e...") Membership condition : The logical condition (e.g., "...where e.job_category = 'MANAGER'...") Scope : Whether the condition is invariant or can change over entity lifecycle

defining-predicates.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Formal Predicate Definitions
 
-- Simple Value-Based Predicate
MANAGER ⊆ EMPLOYEE where job_category = 'MANAGER'
 
-- Range-Based Predicate  
PREMIUM_ACCOUNT ⊆ ACCOUNT where balance >= 100000 AND balance < 1000000
 
-- Multi-Condition Predicate
ACTIVE_PREMIUM_CUSTOMER ⊆ CUSTOMER 
    where account_type = 'PREMIUM' 
    AND last_activity_date > CURRENT_DATE - INTERVAL '90 days'
    AND status = 'ACTIVE'
 
-- Relationship-Based Predicate (subtype defined by participation in relationship)
SUPERVISING_EMPLOYEE ⊆ EMPLOYEE 
    where EXISTS (SELECT 1 FROM SUPERVISION WHERE supervisor_id = employee_id)
 
-- Derived/Computed Predicate (subtype based on aggregate or computed value)
HIGH_VOLUME_CUSTOMER ⊆ CUSTOMER
    where (SELECT COUNT(*) FROM ORDERS WHERE customer_id = id) > 100

Predicate Quality Criteria: Well-designed predicates satisfy these properties:

Predicate Quality Checklist

•Decidable — Given any supertype entity, you can definitively determine membership (no ambiguity)
•Efficient — The condition can be evaluated efficiently against the database (indexable, not requiring full scans)
•Stable — Membership doesn't change unexpectedly due to unrelated data modifications
•Meaningful — The predicate reflects genuine domain semantics, not arbitrary technical divisions
•Enforceable — The database can validate the predicate (constraints can be written for it)

Beware of Unstable Predicates

Condition-based predicates can create unstable subtype membership where entities silently move between subtypes as attribute values change. If PREMIUM_ACCOUNT is defined by balance >= 100000, a withdrawal below that threshold instantly changes the entity's type. Ensure your application logic handles such changes gracefully, or choose more stable discriminators.

Constructing Multi-Level Hierarchies

Real-world domains often require multi-level specialization hierarchies where subtypes are themselves further specialized. Building these hierarchies requires careful attention to inheritance chains, discriminator consistency, and semantic coherence. Example: Academic Institution Personnel Hierarchy Consider modeling personnel at a university:

Converting Mermaid diagram...

Hierarchy Design Principles:

•Semantic coherence at each level — Each level of specialization should reflect meaningful domain distinctions. Don't add levels just for structural convenience.
•Consistent discriminator basis — Within a single specialization (siblings), the discriminator should be consistent. FACULTY specializes by 'faculty_type' (Professor/Instructor), PROFESSOR specializes by 'tenure_status'.
•Attribute accumulation — Each level adds local attributes while inheriting all ancestor attributes. TENURED_PROFESSOR has: tenure_date (local) + tenure_status, publications (from Professor) + rank, department (from Faculty) + hire_date, salary (from Employee) + SSN, name, address, DOB (from Person).
•Relationship accumulation — Similarly, relationships are inherited down the hierarchy. If Person LIVES-AT Address, all descendants participate in this relationship.
•Constraint refinement — Subtypes can add constraints but shouldn't contradict supertype constraints. If Employee.salary > 0, all Faculty and Staff must satisfy this.

Depth Considerations

While there's no absolute limit, hierarchies deeper than 4-5 levels often indicate over-specialization. Each level adds schema complexity, query join depth (in some mapping strategies), and cognitive load for developers. Balance semantic accuracy against practical manageability.

When to Stop Specializing

Specialization is a powerful tool, but over-application leads to schema bloat, unnecessary complexity, and maintenance burden. Knowing when to stop specializing is as important as knowing when to start . Stop Specializing When:

Signs to Stop

•No local attributes — Subtype has no attributes beyond inherited ones. Just use a discriminator attribute on supertype.
•No local relationships — Subtype doesn't participate in unique relationships. The distinction is purely nominal.
•No distinct constraints — No business rules specific to this subtype. The categorization is informational, not structural.
•Single instance expected — If only one entity will ever be in this subtype, model it as an attribute, not a subtype.
•Volatile categorization — If the categorization will change frequently or is tentative, an attribute is more flexible than schema structure.

Signs to Continue

•Distinct local attributes — Subtype has meaningful attributes that don't apply to siblings.
•Distinct relationships — Subtype participates in relationships that siblings don't.
•Different constraints — Subtype-specific business rules require enforcement.
•Different behaviors — Applications treat this subtype differently (different UI, different processing).
•Query patterns — Users frequently query for 'all X-type entities' as a distinct set.

The Attribute vs. Subtype Decision: Consider this scenario: You have PRODUCT entity and want to track whether products are perishable. Option A: Attribute PRODUCT(product_id, name, price, is_perishable BOOLEAN) Option B: Subtype PRODUCT(product_id, name, price) ├── PERISHABLE_PRODUCT(expiry_tracking_method, shelf_life_days, requires_refrigeration) └── NON_PERISHABLE_PRODUCT(storage_requirements) Which is correct? It depends: If perishable products need extra attributes (expiry tracking, refrigeration requirements) and non-perishable products have their own attributes (different storage), use subtypes . If 'perishable' is just a flag with no structural implications, use an attribute . If you're unsure, start with an attribute and refactor to subtypes if structural distinctions emerge.

The YAGNI Principle

Apply You Aren't Gonna Need It to specialization. Don't create subtypes for hypothetical future distinctions. Create them when the current requirements demonstrate clear structural differences. Adding a subtype later is straightforward; removing an unnecessary one is more disruptive.

Practical Example: Banking Account Hierarchy

Let's walk through a complete subtype creation exercise for a banking domain. This example demonstrates the full process from requirements to complete hierarchy definition.

Requirements

A bank manages various account types. All accounts have an account number, holder information, balance, and open date. Savings accounts earn interest and have withdrawal limits. Checking accounts have overdraft protection options and check-writing capability. Investment accounts hold portfolios with associated risk levels and investment strategies. Some investment accounts are retirement accounts with contribution limits and tax advantages; others are brokerage accounts with margin trading options.

Step 1: Identify Supertype ACCOUNT is the clear supertype with shared attributes: account_number, holder_id, balance, open_date, status. Step 2: Discover Distinguishing Characteristics Savings: interest_rate, withdrawal_limit, compound_frequency Checking: overdraft_limit, overdraft_enabled, check_number_sequence Investment: portfolio_id, risk_level, investment_strategy Step 3: Define Discriminator Explicit attribute: account_type ∈ {'SAVINGS', 'CHECKING', 'INVESTMENT'} Step 4: Second-Level Specialization (Investment) Retirement: contribution_limit, tax_advantage_type, early_withdrawal_penalty Brokerage: margin_enabled, margin_limit, settlement_period Sub-discriminator: investment_type ∈ {'RETIREMENT', 'BROKERAGE'}

Converting Mermaid diagram...

banking-hierarchy-predicates.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Formal Predicate Definitions for Banking Hierarchy
 
-- Level 1: Account specialization
SAVINGS_ACCOUNT    ⊆ ACCOUNT where account_type = 'SAVINGS'
CHECKING_ACCOUNT   ⊆ ACCOUNT where account_type = 'CHECKING'
INVESTMENT_ACCOUNT ⊆ ACCOUNT where account_type = 'INVESTMENT'
 
-- Level 2: Investment Account specialization
RETIREMENT_ACCOUNT ⊆ INVESTMENT_ACCOUNT where investment_type = 'RETIREMENT'
BROKERAGE_ACCOUNT  ⊆ INVESTMENT_ACCOUNT where investment_type = 'BROKERAGE'
 
-- Constraint: Level 1 is disjoint and total
-- Every account must be exactly one of: SAVINGS, CHECKING, or INVESTMENT
-- account_type IN ('SAVINGS', 'CHECKING', 'INVESTMENT') -- exhaustive
-- account_type is single-valued -- disjoint
 
-- Constraint: Level 2 is disjoint and total (for INVESTMENT accounts)
-- Every investment account must be either RETIREMENT or BROKERAGE
-- investment_type IN ('RETIREMENT', 'BROKERAGE') -- exhaustive within investment
-- investment_type is single-valued -- disjoint

Common Subtype Creation Errors

Effective subtype creation requires avoiding common pitfalls. Understanding these errors helps you review and improve your designs.

Common Subtype Creation Errors and Corrections
Error	Problem	Correction
Over-Specialization	Creating subtypes for every possible distinction, resulting in hundreds of types with minimal structural differences.	Apply the 'local attributes test': no local attributes or relationships = no separate subtype.
Under-Specialization	Forcing all variations into a single entity with many nullable attributes, losing semantic clarity.	If different instances need genuinely different attributes, model as subtypes.
Mixing Discriminator Bases	Using different criteria for sibling subtypes (one by role, another by status).	Siblings should be distinguished by the same discriminator type.
Temporal Confusion	Modeling lifecycle states as permanent subtypes when entities move between states.	Use state attributes or state machines for temporal categories; use subtypes for permanent categories.
Identity Fragmentation	Making the same real-world entity different entities in different subtypes.	Subtypes share supertype identity. A MANAGER and ENGINEER can be the same EMPLOYEE.
Inconsistent Depth	Some branches of hierarchy go many levels deep while siblings remain shallow without justification.	Hierarchy depth should reflect domain complexity, not arbitrary structure.

The Temporal Trap

A particularly common error is modeling states as types . Consider: Should ORDER have subtypes PENDING_ORDER, SHIPPED_ORDER, COMPLETED_ORDER? Usually NO—the order doesn't change identity when shipped; it changes state. Use a status attribute unless state transitions involve structural changes (different attributes needed in different states).

Subtype Creation: Building Specialization Hierarchies

From Concept to Construction

The Subtype Creation Process

Discriminator Selection Strategies

Defining Subtype Predicates

Constructing Multi-Level Hierarchies

When to Stop Specializing

Practical Example: Banking Account Hierarchy

Common Subtype Creation Errors

There’s more to understand.

Subtype Creation: Building Specialization Hierarchies

From Concept to Construction

The Subtype Creation Process

Discriminator Selection Strategies

Defining Subtype Predicates

Constructing Multi-Level Hierarchies

When to Stop Specializing

Practical Example: Banking Account Hierarchy

Common Subtype Creation Errors

There’s more to understand.