Database Management SystemCategories (Union Types)

Categories and Union Types in EER Modeling

LevelAdvanced

Duration60 mins

TopicCategories (Union Types)

5 / 5

Mapping Categories to Relational Schemas

From Concept to Implementation

We've now thoroughly explored categories (union types) at the conceptual level—what they are, how selective inheritance works, and when to use partial versus total participation. But conceptual models don't run on servers. The final, critical step is mapping these EER constructs to actual relational database schemas.

Mapping categories presents unique challenges that don't arise with regular specialization/generalization:

Multiple superclass tables with potentially different primary key types
Polymorphic foreign key references from the category to various superclasses
Type discrimination to determine which superclass each instance belongs to
Query complexity when joining across the heterogeneous structure

This page provides a complete guide to category mapping strategies, their trade-offs, and implementation patterns used in production systems.

What You Will Learn

By the end of this page, you will understand the primary category mapping strategies, how to implement polymorphic references, trade-offs between approaches, and best practices for real-world category implementations.

Category Mapping Overview

Mapping categories to relational schemas follows a different process than mapping regular specialization. The key challenges are:

1. Heterogeneous Primary Keys Superclass tables often have different primary key structures:

PERSON uses SSN (CHAR(11))
COMPANY uses company_id (INT AUTO_INCREMENT)
GOVERNMENT_AGENCY uses agency_code (VARCHAR(10))

2. Polymorphic References The category table must reference ANY of its superclass tables—but standard foreign keys can only reference ONE table.

3. Uniform Access Requirement Relationships TO the category (e.g., VEHICLE.owner_id → OWNER) need a single, uniform key to reference.

The Fundamental Mapping Approach:

Unlike specialization (which can use shared primary keys), categories typically require:

Surrogate Key — The category table has its own primary key, independent of superclass keys
Type Discriminator — A column indicating which superclass the instance belongs to
Reference Column(s) — Column(s) storing the foreign key to the appropriate superclass

Category Table Structure:
┌────────────────────────────────────────────────────────┐
│ OWNER                                                  │
├────────────────────────────────────────────────────────┤
│ owner_id      (PK) - Surrogate key                     │
│ owner_type    - Discriminator ('PERSON', 'COMPANY'...) │
│ ref_id        - FK to superclass (type-dependent)      │
│ [category-own attributes...]                           │
└────────────────────────────────────────────────────────┘

Why Surrogate Keys?

Surrogate keys solve the heterogeneous PK problem. Whether the underlying superclass uses SSN, INT, or UUID, the category's surrogate key provides a uniform reference point. Relationships like VEHICLE.owner_id always reference the category's owner_id, never directly to superclass PKs.

Standard Category Mapping Strategy

The standard category mapping strategy creates a dedicated table for the category with discriminator and polymorphic reference columns. This is the most widely used approach.

standard_category_mapping.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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- ============================================
-- SUPERCLASS TABLES (Independent existence)
-- ============================================
 
CREATE TABLE person (
    ssn             CHAR(11) PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    date_of_birth   DATE NOT NULL,
    email           VARCHAR(100),
    phone           VARCHAR(20)
);
 
CREATE TABLE company (
    company_id      INT PRIMARY KEY AUTO_INCREMENT,
    company_name    VARCHAR(100) NOT NULL,
    tax_id          CHAR(10) UNIQUE NOT NULL,
    founded_date    DATE,
    industry        VARCHAR(50),
    employee_count  INT
);
 
CREATE TABLE government_agency (
    agency_id       INT PRIMARY KEY AUTO_INCREMENT,
    agency_name     VARCHAR(100) NOT NULL,
    agency_code     VARCHAR(10) UNIQUE NOT NULL,
    jurisdiction    ENUM('FEDERAL', 'STATE', 'LOCAL') NOT NULL,
    parent_agency_id INT,
    FOREIGN KEY (parent_agency_id) REFERENCES government_agency(agency_id)
);
 
-- ============================================
-- CATEGORY TABLE
-- ============================================
 
CREATE TABLE owner (
    -- Surrogate Primary Key
    owner_id        INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Type Discriminator
    owner_type      ENUM('PERSON', 'COMPANY', 'GOVERNMENT_AGENCY') NOT NULL,
    
    -- Polymorphic Reference (stored as string for flexibility)
    ref_key         VARCHAR(50) NOT NULL,
    
    -- Category-own attributes
    registration_date   DATE NOT NULL DEFAULT (CURRENT_DATE),
    credit_score        INT CHECK (credit_score BETWEEN 300 AND 850),
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Composite unique constraint: no duplicate references per type
    UNIQUE (owner_type, ref_key),
    
    -- Index for efficient lookup by type
    INDEX idx_owner_type (owner_type),
    INDEX idx_owner_ref (owner_type, ref_key)
);
 
-- ============================================
-- RELATIONSHIP USING CATEGORY
-- ============================================
 
CREATE TABLE vehicle (
    vin             CHAR(17) PRIMARY KEY,
    make            VARCHAR(50) NOT NULL,
    model           VARCHAR(50) NOT NULL,
    year            YEAR NOT NULL,
    color           VARCHAR(30),
    
    -- Reference to CATEGORY (not directly to superclasses)
    owner_id        INT NOT NULL,
    purchase_date   DATE,
    
    FOREIGN KEY (owner_id) REFERENCES owner(owner_id) ON DELETE RESTRICT
);

Key Elements of This Mapping:

Element	Purpose
`owner_id` (surrogate PK)	Provides uniform reference for all category instances
`owner_type` (discriminator)	Identifies which superclass table to join
`ref_key` (polymorphic FK)	Stores the actual superclass primary key value
`UNIQUE (owner_type, ref_key)`	Prevents duplicate registrations of same superclass instance
Category-own attributes	Attributes that apply to all owners regardless of type

Polymorphic Reference Strategies

The polymorphic reference (how the category points to its superclass) can be implemented in several ways, each with trade-offs.

Strategy 1: Single Polymorphic Column

Store the superclass key in a single, type-flexible column:

single_column_ref.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE owner (
    owner_id        INT PRIMARY KEY AUTO_INCREMENT,
    owner_type      ENUM('PERSON', 'COMPANY', 'GOVERNMENT_AGENCY') NOT NULL,
    
    -- Single column stores any PK type (as string)
    ref_key         VARCHAR(50) NOT NULL,
    
    -- ... other columns
    UNIQUE (owner_type, ref_key)
);
 
-- Query joining to PERSON superclass
SELECT o.owner_id, p.first_name, p.last_name
FROM owner o
JOIN person p ON o.owner_type = 'PERSON' AND o.ref_key = p.ssn;
 
-- Query joining to COMPANY superclass  
SELECT o.owner_id, c.company_name
FROM owner o
JOIN company c ON o.owner_type = 'COMPANY' AND o.ref_key = CAST(c.company_id AS CHAR);

Pros:

Simple schema (one column for all types)
Easy to add new superclass types

Cons:

Type conversion may be needed (INT to VARCHAR)
No declarative foreign key constraint possible
String comparison for numeric keys less efficient

Recommendation

For most scenarios, the Multiple Nullable Columns strategy provides the best balance of referential integrity and query simplicity. Use Single Column when you have many superclass types or frequently add new ones. Use Junction Tables when you need junction-specific attributes or maximum normalization.

Referential Integrity Enforcement

The single-column polymorphic reference strategy cannot use standard FK constraints. Referential integrity must be enforced through triggers or application logic.

category_ri_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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- ============================================
-- Trigger to enforce polymorphic referential integrity
-- ============================================
 
DELIMITER //
 
CREATE TRIGGER owner_ref_integrity_insert
BEFORE INSERT ON owner
FOR EACH ROW
BEGIN
    DECLARE ref_exists INT DEFAULT 0;
    DECLARE error_msg VARCHAR(200);
    
    -- Check reference based on type
    CASE NEW.owner_type
        WHEN 'PERSON' THEN
            SELECT COUNT(*) INTO ref_exists 
            FROM person WHERE ssn = NEW.ref_key;
            SET error_msg = CONCAT('Person SSN ', NEW.ref_key, ' does not exist');
            
        WHEN 'COMPANY' THEN
            SELECT COUNT(*) INTO ref_exists 
            FROM company WHERE company_id = CAST(NEW.ref_key AS UNSIGNED);
            SET error_msg = CONCAT('Company ID ', NEW.ref_key, ' does not exist');
            
        WHEN 'GOVERNMENT_AGENCY' THEN
            SELECT COUNT(*) INTO ref_exists 
            FROM government_agency WHERE agency_id = CAST(NEW.ref_key AS UNSIGNED);
            SET error_msg = CONCAT('Government Agency ID ', NEW.ref_key, ' does not exist');
    END CASE;
    
    IF ref_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_msg;
    END IF;
END //
 
-- Also trigger on UPDATE
CREATE TRIGGER owner_ref_integrity_update
BEFORE UPDATE ON owner
FOR EACH ROW
BEGIN
    DECLARE ref_exists INT DEFAULT 0;
    
    -- Only check if ref_key or owner_type changed
    IF NEW.ref_key != OLD.ref_key OR NEW.owner_type != OLD.owner_type THEN
        CASE NEW.owner_type
            WHEN 'PERSON' THEN
                SELECT COUNT(*) INTO ref_exists FROM person WHERE ssn = NEW.ref_key;
            WHEN 'COMPANY' THEN
                SELECT COUNT(*) INTO ref_exists FROM company WHERE company_id = CAST(NEW.ref_key AS UNSIGNED);
            WHEN 'GOVERNMENT_AGENCY' THEN
                SELECT COUNT(*) INTO ref_exists FROM government_agency WHERE agency_id = CAST(NEW.ref_key AS UNSIGNED);
        END CASE;
        
        IF ref_exists = 0 THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Referenced entity does not exist';
        END IF;
    END IF;
END //
 
-- Prevent superclass deletion if referenced by owner
CREATE TRIGGER person_delete_check
BEFORE DELETE ON person
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM owner WHERE owner_type = 'PERSON' AND ref_key = OLD.ssn) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete person: referenced as owner';
    END IF;
END //
 
CREATE TRIGGER company_delete_check
BEFORE DELETE ON company
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM owner WHERE owner_type = 'COMPANY' AND ref_key = CAST(OLD.company_id AS CHAR)) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete company: referenced as owner';
    END IF;
END //
 
DELIMITER ;

Trigger Maintenance Burden

Each new superclass requires new trigger logic or trigger updates. When using triggers for RI, document them thoroughly and include them in code reviews. Forgotten trigger updates when adding superclasses cause subtle data integrity issues.

Query Patterns for Mapped Categories

Working with mapped categories requires understanding common query patterns for accessing data across the heterogeneous structure.

category_query_patterns.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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
-- ============================================
-- PATTERN 1: Get all category instances with resolved names
-- ============================================
SELECT 
    o.owner_id,
    o.owner_type,
    o.registration_date,
    CASE o.owner_type
        WHEN 'PERSON' THEN (
            SELECT CONCAT(first_name, ' ', last_name) 
            FROM person WHERE ssn = o.ref_key
        )
        WHEN 'COMPANY' THEN (
            SELECT company_name 
            FROM company WHERE company_id = CAST(o.ref_key AS UNSIGNED)
        )
        WHEN 'GOVERNMENT_AGENCY' THEN (
            SELECT agency_name 
            FROM government_agency WHERE agency_id = CAST(o.ref_key AS UNSIGNED)
        )
    END AS owner_name
FROM owner o
WHERE o.is_active = TRUE;
 
-- ============================================
-- PATTERN 2: Unified view for common access
-- ============================================
CREATE VIEW v_owner_unified AS
SELECT 
    o.owner_id,
    o.owner_type,
    o.ref_key,
    o.registration_date,
    o.credit_score,
    o.is_active,
    -- Unified name column
    COALESCE(
        CONCAT(p.first_name, ' ', p.last_name),
        c.company_name,
        g.agency_name
    ) AS display_name,
    -- Type-specific columns (null for non-matching types)
    p.email AS person_email,
    p.date_of_birth AS person_dob,
    c.tax_id AS company_tax_id,
    c.employee_count AS company_employees,
    g.jurisdiction AS agency_jurisdiction
FROM owner o
LEFT JOIN person p ON o.owner_type = 'PERSON' AND o.ref_key = p.ssn
LEFT JOIN company c ON o.owner_type = 'COMPANY' AND o.ref_key = CAST(c.company_id AS CHAR)
LEFT JOIN government_agency g ON o.owner_type = 'GOVERNMENT_AGENCY' AND o.ref_key = CAST(g.agency_id AS CHAR);
 
-- ============================================
-- PATTERN 3: Type-filtered queries
-- ============================================
 
-- All corporate owners with their details
SELECT 
    o.owner_id,
    c.company_name,
    c.tax_id,
    c.employee_count,
    COUNT(v.vin) AS vehicles_owned
FROM owner o
JOIN company c ON o.ref_key = CAST(c.company_id AS CHAR)
LEFT JOIN vehicle v ON v.owner_id = o.owner_id
WHERE o.owner_type = 'COMPANY' AND o.is_active = TRUE
GROUP BY o.owner_id, c.company_name, c.tax_id, c.employee_count;
 
-- ============================================
-- PATTERN 4: Navigation through category in relationships
-- ============================================
 
-- All vehicles with owner information (any type)
SELECT 
    v.vin,
    v.make,
    v.model,
    v.year,
    vu.display_name AS owner_name,
    vu.owner_type
FROM vehicle v
JOIN v_owner_unified vu ON v.owner_id = vu.owner_id;
 
-- ============================================
-- PATTERN 5: Existence checks across types
-- ============================================
 
-- Find persons who are NOT vehicle owners
SELECT p.ssn, p.first_name, p.last_name
FROM person p
WHERE NOT EXISTS (
    SELECT 1 FROM owner o 
    WHERE o.owner_type = 'PERSON' AND o.ref_key = p.ssn
);
 
-- Find any superclass instance that IS an owner
SELECT 'PERSON' AS type, p.ssn AS id, CONCAT(p.first_name, ' ', p.last_name) AS name
FROM person p
WHERE EXISTS (SELECT 1 FROM owner o WHERE o.owner_type = 'PERSON' AND o.ref_key = p.ssn)
UNION ALL
SELECT 'COMPANY', CAST(c.company_id AS CHAR), c.company_name
FROM company c
WHERE EXISTS (SELECT 1 FROM owner o WHERE o.owner_type = 'COMPANY' AND o.ref_key = CAST(c.company_id AS CHAR));

Unified View Best Practice

Create a unified VIEW (like v_owner_unified) for your category. Application code references the view for common operations, hiding the join complexity. Type-specific queries can still access underlying tables directly when needed.

Mapping Strategy Comparison

Choosing the right mapping strategy depends on your specific requirements. Here's a comprehensive comparison to guide your decision.

Mapping Strategy Trade-offs
Criteria	Single Column	Multiple Columns	Junction Tables
Schema Simplicity	✓✓ Simple	✓ Moderate	✗ Complex
Referential Integrity	✗ Triggers only	✓✓ Native FKs	✓✓ Native FKs
Query Complexity	✓ Moderate	✓✓ Simple	✗ Complex
Adding Superclasses	✓✓ No schema change	✗ Add column + FK	✗ Add table
Storage Efficiency	✓✓ Minimal	✓ Some NULLs	✓ Normalized
Type-Safe Operations	✗ String casting	✓✓ Native types	✓✓ Native types
ORM Support	✓ Moderate	✓✓ Good	✓ Moderate

Selection Guidelines

•Use Single Column when: You have many superclass types (5+), frequently add new types, or superclass PKs have similar data types
•Use Multiple Columns when: You have few, stable superclass types (2-4), need declarative referential integrity, and have ORM requirements
•Use Junction Tables when: You need junction-specific attributes, maximum normalization, or complex superclass-to-category relationships
•Consider Hybrid when: Different superclasses have vastly different requirements (e.g., high-volume type uses single column, others use FKs)

Production Implementation Patterns

Real-world category implementations often combine strategies and include additional production concerns. Here are patterns used in enterprise systems.

Pattern: Externalized Type Definition

Instead of ENUM, use a lookup table for owner types. This enables metadata and extensibility:

type_lookup_pattern.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
32
33
34
35
36
37
38
39
40
-- Type lookup table for extensibility
CREATE TABLE owner_type (
    type_code       VARCHAR(20) PRIMARY KEY,
    display_name    VARCHAR(50) NOT NULL,
    table_name      VARCHAR(50) NOT NULL,   -- Superclass table
    pk_column       VARCHAR(50) NOT NULL,   -- PK column in superclass
    pk_type         VARCHAR(20) NOT NULL,   -- 'INT', 'CHAR', 'UUID'
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO owner_type VALUES
('PERSON', 'Individual Person', 'person', 'ssn', 'CHAR', TRUE, NOW()),
('COMPANY', 'Business Entity', 'company', 'company_id', 'INT', TRUE, NOW()),
('GOVT_AGENCY', 'Government Agency', 'government_agency', 'agency_id', 'INT', TRUE, NOW());
 
CREATE TABLE owner (
    owner_id        INT PRIMARY KEY AUTO_INCREMENT,
    owner_type_code VARCHAR(20) NOT NULL,
    ref_key         VARCHAR(50) NOT NULL,
    -- ... other columns
    FOREIGN KEY (owner_type_code) REFERENCES owner_type(type_code),
    UNIQUE (owner_type_code, ref_key)
);
 
-- Dynamic query generation using type metadata
SELECT CONCAT(
    'SELECT o.owner_id, ',
    't.display_name, ',
    's.', t.pk_column, ' AS source_key ',
    'FROM owner o ',
    'JOIN owner_type t ON o.owner_type_code = t.type_code ',
    'JOIN ', t.table_name, ' s ON o.ref_key = ',
    CASE t.pk_type 
        WHEN 'INT' THEN CONCAT('CAST(s.', t.pk_column, ' AS CHAR)')
        ELSE CONCAT('s.', t.pk_column)
    END
) AS generated_query
FROM owner_type t
WHERE t.type_code = 'PERSON';

Summary: Mapping Categories

We've completed our exploration of category mapping—the final step in translating EER categories to working database implementations. Let's consolidate the key insights:

Key Takeaways

•Surrogate keys solve heterogeneity — Use a surrogate PK in the category table to provide uniform references regardless of superclass key types.
•Discriminator is essential — A type column indicates which superclass each row references, enabling correct query routing.
•Three polymorphic reference strategies — Single column (simple, no FK), multiple columns (FKs but NULLs), junction tables (normalized but complex).
•Referential integrity requires care — Single-column references need triggers; multiple-column and junction approaches support declarative FKs.
•Views simplify access — Create unified views that hide join complexity for common access patterns.
•Production needs auditing — Consider audit trails, soft deletes, and type metadata tables for enterprise implementations.

Module Complete: Categories (Union Types)

Congratulations! You've completed the comprehensive study of categories in EER modeling. You now understand: what categories are and when to use them, how selective inheritance works, the difference between partial and total participation, and how to map categories to relational schemas. This knowledge enables you to model complex real-world scenarios where entities from different types need to play unified roles.

Categories in the Broader EER Context:

Categories complete our coverage of EER's advanced modeling constructs:

Construct	Purpose	Key Insight
Specialization	Refine superclass into subtypes	Top-down, attribute refinement
Generalization	Abstract subtypes into superclass	Bottom-up, common abstraction
Inheritance	Share attributes/relationships	Single path (specialization) or selective (category)
Categories	Unify unrelated types into role	Multiple superclasses, mutual exclusivity

Together, these constructs enable sophisticated semantic modeling that captures real-world complexity far beyond basic ER diagrams.

Next Steps:

With EER modeling mastered, you're prepared to:

Design complex database schemas for enterprise applications
Evaluate existing schemas for proper use of EER constructs
Map conceptual models to physical implementations
Make informed decisions about specialization vs. category trade-offs

The conceptual modeling skills from this chapter serve as the foundation for all subsequent database design work.

5 / 5

Loading learning content...

Database Management SystemCategories (Union Types)

Categories and Union Types in EER Modeling

LevelAdvanced

Duration60 mins

TopicCategories (Union Types)

5 / 5

Mapping Categories to Relational Schemas

From Concept to Implementation

Mapping categories presents unique challenges that don't arise with regular specialization/generalization:

Multiple superclass tables with potentially different primary key types
Polymorphic foreign key references from the category to various superclasses
Type discrimination to determine which superclass each instance belongs to
Query complexity when joining across the heterogeneous structure

This page provides a complete guide to category mapping strategies, their trade-offs, and implementation patterns used in production systems.

What You Will Learn

Category Mapping Overview

Mapping categories to relational schemas follows a different process than mapping regular specialization. The key challenges are:

1. Heterogeneous Primary Keys Superclass tables often have different primary key structures:

PERSON uses SSN (CHAR(11))
COMPANY uses company_id (INT AUTO_INCREMENT)
GOVERNMENT_AGENCY uses agency_code (VARCHAR(10))

2. Polymorphic References The category table must reference ANY of its superclass tables—but standard foreign keys can only reference ONE table.

3. Uniform Access Requirement Relationships TO the category (e.g., VEHICLE.owner_id → OWNER) need a single, uniform key to reference.

The Fundamental Mapping Approach:

Unlike specialization (which can use shared primary keys), categories typically require:

Surrogate Key — The category table has its own primary key, independent of superclass keys
Type Discriminator — A column indicating which superclass the instance belongs to
Reference Column(s) — Column(s) storing the foreign key to the appropriate superclass

Category Table Structure:
┌────────────────────────────────────────────────────────┐
│ OWNER                                                  │
├────────────────────────────────────────────────────────┤
│ owner_id      (PK) - Surrogate key                     │
│ owner_type    - Discriminator ('PERSON', 'COMPANY'...) │
│ ref_id        - FK to superclass (type-dependent)      │
│ [category-own attributes...]                           │
└────────────────────────────────────────────────────────┘

Why Surrogate Keys?

Standard Category Mapping Strategy

The standard category mapping strategy creates a dedicated table for the category with discriminator and polymorphic reference columns. This is the most widely used approach.

standard_category_mapping.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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- ============================================
-- SUPERCLASS TABLES (Independent existence)
-- ============================================
 
CREATE TABLE person (
    ssn             CHAR(11) PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    date_of_birth   DATE NOT NULL,
    email           VARCHAR(100),
    phone           VARCHAR(20)
);
 
CREATE TABLE company (
    company_id      INT PRIMARY KEY AUTO_INCREMENT,
    company_name    VARCHAR(100) NOT NULL,
    tax_id          CHAR(10) UNIQUE NOT NULL,
    founded_date    DATE,
    industry        VARCHAR(50),
    employee_count  INT
);
 
CREATE TABLE government_agency (
    agency_id       INT PRIMARY KEY AUTO_INCREMENT,
    agency_name     VARCHAR(100) NOT NULL,
    agency_code     VARCHAR(10) UNIQUE NOT NULL,
    jurisdiction    ENUM('FEDERAL', 'STATE', 'LOCAL') NOT NULL,
    parent_agency_id INT,
    FOREIGN KEY (parent_agency_id) REFERENCES government_agency(agency_id)
);
 
-- ============================================
-- CATEGORY TABLE
-- ============================================
 
CREATE TABLE owner (
    -- Surrogate Primary Key
    owner_id        INT PRIMARY KEY AUTO_INCREMENT,
    
    -- Type Discriminator
    owner_type      ENUM('PERSON', 'COMPANY', 'GOVERNMENT_AGENCY') NOT NULL,
    
    -- Polymorphic Reference (stored as string for flexibility)
    ref_key         VARCHAR(50) NOT NULL,
    
    -- Category-own attributes
    registration_date   DATE NOT NULL DEFAULT (CURRENT_DATE),
    credit_score        INT CHECK (credit_score BETWEEN 300 AND 850),
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Composite unique constraint: no duplicate references per type
    UNIQUE (owner_type, ref_key),
    
    -- Index for efficient lookup by type
    INDEX idx_owner_type (owner_type),
    INDEX idx_owner_ref (owner_type, ref_key)
);
 
-- ============================================
-- RELATIONSHIP USING CATEGORY
-- ============================================
 
CREATE TABLE vehicle (
    vin             CHAR(17) PRIMARY KEY,
    make            VARCHAR(50) NOT NULL,
    model           VARCHAR(50) NOT NULL,
    year            YEAR NOT NULL,
    color           VARCHAR(30),
    
    -- Reference to CATEGORY (not directly to superclasses)
    owner_id        INT NOT NULL,
    purchase_date   DATE,
    
    FOREIGN KEY (owner_id) REFERENCES owner(owner_id) ON DELETE RESTRICT
);

Key Elements of This Mapping:

Element	Purpose
`owner_id` (surrogate PK)	Provides uniform reference for all category instances
`owner_type` (discriminator)	Identifies which superclass table to join
`ref_key` (polymorphic FK)	Stores the actual superclass primary key value
`UNIQUE (owner_type, ref_key)`	Prevents duplicate registrations of same superclass instance
Category-own attributes	Attributes that apply to all owners regardless of type

Polymorphic Reference Strategies

The polymorphic reference (how the category points to its superclass) can be implemented in several ways, each with trade-offs.

Strategy 1: Single Polymorphic Column

Store the superclass key in a single, type-flexible column:

single_column_ref.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE owner (
    owner_id        INT PRIMARY KEY AUTO_INCREMENT,
    owner_type      ENUM('PERSON', 'COMPANY', 'GOVERNMENT_AGENCY') NOT NULL,
    
    -- Single column stores any PK type (as string)
    ref_key         VARCHAR(50) NOT NULL,
    
    -- ... other columns
    UNIQUE (owner_type, ref_key)
);
 
-- Query joining to PERSON superclass
SELECT o.owner_id, p.first_name, p.last_name
FROM owner o
JOIN person p ON o.owner_type = 'PERSON' AND o.ref_key = p.ssn;
 
-- Query joining to COMPANY superclass  
SELECT o.owner_id, c.company_name
FROM owner o
JOIN company c ON o.owner_type = 'COMPANY' AND o.ref_key = CAST(c.company_id AS CHAR);

Pros:

Simple schema (one column for all types)
Easy to add new superclass types

Cons:

Type conversion may be needed (INT to VARCHAR)
No declarative foreign key constraint possible
String comparison for numeric keys less efficient

Recommendation

Referential Integrity Enforcement

The single-column polymorphic reference strategy cannot use standard FK constraints. Referential integrity must be enforced through triggers or application logic.

category_ri_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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- ============================================
-- Trigger to enforce polymorphic referential integrity
-- ============================================
 
DELIMITER //
 
CREATE TRIGGER owner_ref_integrity_insert
BEFORE INSERT ON owner
FOR EACH ROW
BEGIN
    DECLARE ref_exists INT DEFAULT 0;
    DECLARE error_msg VARCHAR(200);
    
    -- Check reference based on type
    CASE NEW.owner_type
        WHEN 'PERSON' THEN
            SELECT COUNT(*) INTO ref_exists 
            FROM person WHERE ssn = NEW.ref_key;
            SET error_msg = CONCAT('Person SSN ', NEW.ref_key, ' does not exist');
            
        WHEN 'COMPANY' THEN
            SELECT COUNT(*) INTO ref_exists 
            FROM company WHERE company_id = CAST(NEW.ref_key AS UNSIGNED);
            SET error_msg = CONCAT('Company ID ', NEW.ref_key, ' does not exist');
            
        WHEN 'GOVERNMENT_AGENCY' THEN
            SELECT COUNT(*) INTO ref_exists 
            FROM government_agency WHERE agency_id = CAST(NEW.ref_key AS UNSIGNED);
            SET error_msg = CONCAT('Government Agency ID ', NEW.ref_key, ' does not exist');
    END CASE;
    
    IF ref_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = error_msg;
    END IF;
END //
 
-- Also trigger on UPDATE
CREATE TRIGGER owner_ref_integrity_update
BEFORE UPDATE ON owner
FOR EACH ROW
BEGIN
    DECLARE ref_exists INT DEFAULT 0;
    
    -- Only check if ref_key or owner_type changed
    IF NEW.ref_key != OLD.ref_key OR NEW.owner_type != OLD.owner_type THEN
        CASE NEW.owner_type
            WHEN 'PERSON' THEN
                SELECT COUNT(*) INTO ref_exists FROM person WHERE ssn = NEW.ref_key;
            WHEN 'COMPANY' THEN
                SELECT COUNT(*) INTO ref_exists FROM company WHERE company_id = CAST(NEW.ref_key AS UNSIGNED);
            WHEN 'GOVERNMENT_AGENCY' THEN
                SELECT COUNT(*) INTO ref_exists FROM government_agency WHERE agency_id = CAST(NEW.ref_key AS UNSIGNED);
        END CASE;
        
        IF ref_exists = 0 THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Referenced entity does not exist';
        END IF;
    END IF;
END //
 
-- Prevent superclass deletion if referenced by owner
CREATE TRIGGER person_delete_check
BEFORE DELETE ON person
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM owner WHERE owner_type = 'PERSON' AND ref_key = OLD.ssn) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete person: referenced as owner';
    END IF;
END //
 
CREATE TRIGGER company_delete_check
BEFORE DELETE ON company
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM owner WHERE owner_type = 'COMPANY' AND ref_key = CAST(OLD.company_id AS CHAR)) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete company: referenced as owner';
    END IF;
END //
 
DELIMITER ;

Trigger Maintenance Burden

Query Patterns for Mapped Categories

Working with mapped categories requires understanding common query patterns for accessing data across the heterogeneous structure.

category_query_patterns.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
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
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
-- ============================================
-- PATTERN 1: Get all category instances with resolved names
-- ============================================
SELECT 
    o.owner_id,
    o.owner_type,
    o.registration_date,
    CASE o.owner_type
        WHEN 'PERSON' THEN (
            SELECT CONCAT(first_name, ' ', last_name) 
            FROM person WHERE ssn = o.ref_key
        )
        WHEN 'COMPANY' THEN (
            SELECT company_name 
            FROM company WHERE company_id = CAST(o.ref_key AS UNSIGNED)
        )
        WHEN 'GOVERNMENT_AGENCY' THEN (
            SELECT agency_name 
            FROM government_agency WHERE agency_id = CAST(o.ref_key AS UNSIGNED)
        )
    END AS owner_name
FROM owner o
WHERE o.is_active = TRUE;
 
-- ============================================
-- PATTERN 2: Unified view for common access
-- ============================================
CREATE VIEW v_owner_unified AS
SELECT 
    o.owner_id,
    o.owner_type,
    o.ref_key,
    o.registration_date,
    o.credit_score,
    o.is_active,
    -- Unified name column
    COALESCE(
        CONCAT(p.first_name, ' ', p.last_name),
        c.company_name,
        g.agency_name
    ) AS display_name,
    -- Type-specific columns (null for non-matching types)
    p.email AS person_email,
    p.date_of_birth AS person_dob,
    c.tax_id AS company_tax_id,
    c.employee_count AS company_employees,
    g.jurisdiction AS agency_jurisdiction
FROM owner o
LEFT JOIN person p ON o.owner_type = 'PERSON' AND o.ref_key = p.ssn
LEFT JOIN company c ON o.owner_type = 'COMPANY' AND o.ref_key = CAST(c.company_id AS CHAR)
LEFT JOIN government_agency g ON o.owner_type = 'GOVERNMENT_AGENCY' AND o.ref_key = CAST(g.agency_id AS CHAR);
 
-- ============================================
-- PATTERN 3: Type-filtered queries
-- ============================================
 
-- All corporate owners with their details
SELECT 
    o.owner_id,
    c.company_name,
    c.tax_id,
    c.employee_count,
    COUNT(v.vin) AS vehicles_owned
FROM owner o
JOIN company c ON o.ref_key = CAST(c.company_id AS CHAR)
LEFT JOIN vehicle v ON v.owner_id = o.owner_id
WHERE o.owner_type = 'COMPANY' AND o.is_active = TRUE
GROUP BY o.owner_id, c.company_name, c.tax_id, c.employee_count;
 
-- ============================================
-- PATTERN 4: Navigation through category in relationships
-- ============================================
 
-- All vehicles with owner information (any type)
SELECT 
    v.vin,
    v.make,
    v.model,
    v.year,
    vu.display_name AS owner_name,
    vu.owner_type
FROM vehicle v
JOIN v_owner_unified vu ON v.owner_id = vu.owner_id;
 
-- ============================================
-- PATTERN 5: Existence checks across types
-- ============================================
 
-- Find persons who are NOT vehicle owners
SELECT p.ssn, p.first_name, p.last_name
FROM person p
WHERE NOT EXISTS (
    SELECT 1 FROM owner o 
    WHERE o.owner_type = 'PERSON' AND o.ref_key = p.ssn
);
 
-- Find any superclass instance that IS an owner
SELECT 'PERSON' AS type, p.ssn AS id, CONCAT(p.first_name, ' ', p.last_name) AS name
FROM person p
WHERE EXISTS (SELECT 1 FROM owner o WHERE o.owner_type = 'PERSON' AND o.ref_key = p.ssn)
UNION ALL
SELECT 'COMPANY', CAST(c.company_id AS CHAR), c.company_name
FROM company c
WHERE EXISTS (SELECT 1 FROM owner o WHERE o.owner_type = 'COMPANY' AND o.ref_key = CAST(c.company_id AS CHAR));

Unified View Best Practice

Mapping Strategy Comparison

Choosing the right mapping strategy depends on your specific requirements. Here's a comprehensive comparison to guide your decision.

Mapping Strategy Trade-offs
Criteria	Single Column	Multiple Columns	Junction Tables
Schema Simplicity	✓✓ Simple	✓ Moderate	✗ Complex
Referential Integrity	✗ Triggers only	✓✓ Native FKs	✓✓ Native FKs
Query Complexity	✓ Moderate	✓✓ Simple	✗ Complex
Adding Superclasses	✓✓ No schema change	✗ Add column + FK	✗ Add table
Storage Efficiency	✓✓ Minimal	✓ Some NULLs	✓ Normalized
Type-Safe Operations	✗ String casting	✓✓ Native types	✓✓ Native types
ORM Support	✓ Moderate	✓✓ Good	✓ Moderate

Selection Guidelines

•Use Single Column when: You have many superclass types (5+), frequently add new types, or superclass PKs have similar data types
•Use Multiple Columns when: You have few, stable superclass types (2-4), need declarative referential integrity, and have ORM requirements
•Use Junction Tables when: You need junction-specific attributes, maximum normalization, or complex superclass-to-category relationships
•Consider Hybrid when: Different superclasses have vastly different requirements (e.g., high-volume type uses single column, others use FKs)

Production Implementation Patterns

Real-world category implementations often combine strategies and include additional production concerns. Here are patterns used in enterprise systems.

Pattern: Externalized Type Definition

Instead of ENUM, use a lookup table for owner types. This enables metadata and extensibility:

type_lookup_pattern.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
32
33
34
35
36
37
38
39
40
-- Type lookup table for extensibility
CREATE TABLE owner_type (
    type_code       VARCHAR(20) PRIMARY KEY,
    display_name    VARCHAR(50) NOT NULL,
    table_name      VARCHAR(50) NOT NULL,   -- Superclass table
    pk_column       VARCHAR(50) NOT NULL,   -- PK column in superclass
    pk_type         VARCHAR(20) NOT NULL,   -- 'INT', 'CHAR', 'UUID'
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
INSERT INTO owner_type VALUES
('PERSON', 'Individual Person', 'person', 'ssn', 'CHAR', TRUE, NOW()),
('COMPANY', 'Business Entity', 'company', 'company_id', 'INT', TRUE, NOW()),
('GOVT_AGENCY', 'Government Agency', 'government_agency', 'agency_id', 'INT', TRUE, NOW());
 
CREATE TABLE owner (
    owner_id        INT PRIMARY KEY AUTO_INCREMENT,
    owner_type_code VARCHAR(20) NOT NULL,
    ref_key         VARCHAR(50) NOT NULL,
    -- ... other columns
    FOREIGN KEY (owner_type_code) REFERENCES owner_type(type_code),
    UNIQUE (owner_type_code, ref_key)
);
 
-- Dynamic query generation using type metadata
SELECT CONCAT(
    'SELECT o.owner_id, ',
    't.display_name, ',
    's.', t.pk_column, ' AS source_key ',
    'FROM owner o ',
    'JOIN owner_type t ON o.owner_type_code = t.type_code ',
    'JOIN ', t.table_name, ' s ON o.ref_key = ',
    CASE t.pk_type 
        WHEN 'INT' THEN CONCAT('CAST(s.', t.pk_column, ' AS CHAR)')
        ELSE CONCAT('s.', t.pk_column)
    END
) AS generated_query
FROM owner_type t
WHERE t.type_code = 'PERSON';

Summary: Mapping Categories

We've completed our exploration of category mapping—the final step in translating EER categories to working database implementations. Let's consolidate the key insights:

Key Takeaways

•Surrogate keys solve heterogeneity — Use a surrogate PK in the category table to provide uniform references regardless of superclass key types.
•Discriminator is essential — A type column indicates which superclass each row references, enabling correct query routing.
•Three polymorphic reference strategies — Single column (simple, no FK), multiple columns (FKs but NULLs), junction tables (normalized but complex).
•Referential integrity requires care — Single-column references need triggers; multiple-column and junction approaches support declarative FKs.
•Views simplify access — Create unified views that hide join complexity for common access patterns.
•Production needs auditing — Consider audit trails, soft deletes, and type metadata tables for enterprise implementations.

Module Complete: Categories (Union Types)

Categories in the Broader EER Context:

Categories complete our coverage of EER's advanced modeling constructs:

Construct	Purpose	Key Insight
Specialization	Refine superclass into subtypes	Top-down, attribute refinement
Generalization	Abstract subtypes into superclass	Bottom-up, common abstraction
Inheritance	Share attributes/relationships	Single path (specialization) or selective (category)
Categories	Unify unrelated types into role	Multiple superclasses, mutual exclusivity

Together, these constructs enable sophisticated semantic modeling that captures real-world complexity far beyond basic ER diagrams.

Next Steps:

With EER modeling mastered, you're prepared to:

Design complex database schemas for enterprise applications
Evaluate existing schemas for proper use of EER constructs
Map conceptual models to physical implementations
Make informed decisions about specialization vs. category trade-offs

The conceptual modeling skills from this chapter serve as the foundation for all subsequent database design work.

5 / 5