Mapping Overview - Learning Module

Loading content...

0/252

Common Patterns

Patterns Accelerate Mapping

Experienced database designers recognize that certain structures appear repeatedly across different domains. An employee hierarchy in an HR system resembles a component hierarchy in a manufacturing system. A many-to-many relationship with attributes looks similar whether it's students-courses or products-orders.

These recurring patterns provide templates that accelerate mapping and reduce errors. Rather than reasoning from first principles each time, pattern recognition allows you to apply proven solutions to familiar structures.

This page catalogs the most common ER-to-relational mapping patterns, providing templates, implementation guidance, and domain examples for each. By internalizing these patterns, you'll handle the majority of mapping scenarios with confidence and consistency.

What You Will Learn

By the end of this page, you will recognize common ER patterns, understand their standard relational implementations, know when to apply each pattern, and be able to adapt patterns to domain-specific variations. This pattern library becomes a toolkit for rapid, reliable mapping.

Pattern Categories

Mapping patterns cluster into several categories based on the type of ER construct they address:

Pattern Taxonomy:

Mapping Pattern Categories
Category	Patterns Included	Common Domains
Self-Referential	Hierarchy, Network, Adjacency List	Org charts, BOM, social graphs
Associative Entity	Bridge table with attributes, History tracking	Enrollment, employment, subscriptions
Type/Subtype	Single table, Class table, Concrete table	Products, vehicles, financial instruments
Aggregation	Relationship on relationship	Sponsorship, project oversight
Temporal	Slowly changing, Bi-temporal	Audit, versioning, effective dating
Weak Entity	Dependent identification, Multi-level weakness	Line items, room numbers, component IDs

How to Use Patterns:

Recognition: When analyzing an ER diagram, identify which pattern(s) the structure matches
Template Application: Apply the standard mapping for that pattern
Customization: Adjust for domain-specific requirements (naming, constraints, etc.)
Validation: Verify the mapping meets quality criteria

Patterns are not rigid rules—they're proven starting points. Each domain may require adaptations, but starting from a pattern prevents common errors.

Pattern: Self-Referential Hierarchy

The Pattern:

An entity type has a relationship with itself, creating parent-child or peer relationships within a single entity set. This is also called a recursive relationship or unary relationship.

Common Examples:

Employee supervises Employee (organizational hierarchy)
Category contains Category (product classification)
Part contains Part (bill of materials)
Person refers Person (referral network)
Folder contains Folder (file system structure)

Standard Mapping:

The self-referential relationship maps as a foreign key in the same table referencing its own primary key:

Self-Referential Pattern: Employee Hierarchy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Employee supervises Employee (1:N self-referential)
-- Each employee has at most one supervisor
-- Each supervisor can have many supervisees
 
CREATE TABLE EMPLOYEE (
    EmployeeID    INT          PRIMARY KEY,
    Name          VARCHAR(100) NOT NULL,
    Title         VARCHAR(50),
    HireDate      DATE         NOT NULL,
    
    -- Self-referential FK for supervision hierarchy
    SupervisorID  INT,  -- NULL for top-level (CEO, founders)
    
    FOREIGN KEY (SupervisorID) 
        REFERENCES EMPLOYEE(EmployeeID)
        ON DELETE SET NULL  -- If supervisor leaves, don't delete employees
        ON UPDATE CASCADE
);
 
-- Useful indexes for hierarchical queries
CREATE INDEX idx_employee_supervisor ON EMPLOYEE(SupervisorID);

Variations:

1. M:N Self-Referential (Network):

When entities can have multiple relationships with each other (e.g., social network friendships, component compatibility):

-- Person knows Person (M:N self-referential)
CREATE TABLE PERSON (
    PersonID   INT PRIMARY KEY,
    Name       VARCHAR(100) NOT NULL
);

CREATE TABLE KNOWS (
    Person1ID  INT NOT NULL,
    Person2ID  INT NOT NULL,
    SinceDate  DATE,
    
    PRIMARY KEY (Person1ID, Person2ID),
    FOREIGN KEY (Person1ID) REFERENCES PERSON(PersonID),
    FOREIGN KEY (Person2ID) REFERENCES PERSON(PersonID),
    
    -- Optional: prevent self-relationship
    CHECK (Person1ID <> Person2ID)
);

2. Symmetric vs. Directional:

Symmetric (Person knows Person): Query both directions
Directional (Employee supervises Employee): Parent-child distinction matters

For symmetric relationships, consider storing both directions or using UNION queries.

Hierarchical Query Support

Self-referential tables enable hierarchical queries using recursive CTEs (Common Table Expressions). Most modern RDBMS support WITH RECURSIVE for traversing hierarchies. When designing self-referential schemas, plan for these query patterns.

Hierarchical Query Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Find all employees under a given manager (recursive)
WITH RECURSIVE OrgChart AS (
    -- Base case: the starting manager
    SELECT EmployeeID, Name, SupervisorID, 1 AS Level
    FROM EMPLOYEE
    WHERE EmployeeID = 100  -- Manager to start from
    
    UNION ALL
    
    -- Recursive case: employees supervised by previous level
    SELECT e.EmployeeID, e.Name, e.SupervisorID, oc.Level + 1
    FROM EMPLOYEE e
    JOIN OrgChart oc ON e.SupervisorID = oc.EmployeeID
)
SELECT * FROM OrgChart ORDER BY Level, Name;

Pattern: Associative Entity (Rich Junction)

The Pattern:

An M:N relationship that has its own attributes, making it semantically more than just a link between entities. The relationship itself carries important data.

Common Examples:

Student enrolled in Course (EnrollmentDate, Grade)
Actor stars in Movie (Role, Billing)
Product in Order (Quantity, Price at time of order)
Employee assigned to Project (Hours, StartDate, Role)

Standard Mapping:

The associative entity becomes a full table with its own identity, not just a junction:

Associative Entity Pattern: Course Enrollment
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
CREATE TABLE STUDENT (
    StudentID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL,
    Email       VARCHAR(100) UNIQUE NOT NULL
);
 
CREATE TABLE COURSE (
    CourseID    VARCHAR(10) PRIMARY KEY,
    Title       VARCHAR(100) NOT NULL,
    Credits     INT NOT NULL
);
 
-- ENROLLMENT is an associative entity, not just a junction
CREATE TABLE ENROLLMENT (
    -- Option A: Composite primary key (natural)
    StudentID      INT         NOT NULL,
    CourseID       VARCHAR(10) NOT NULL,
    
    -- Relationship attributes that make this an entity
    EnrollmentDate DATE        NOT NULL DEFAULT CURRENT_DATE,
    Grade          CHAR(2),  -- NULL until graded
    Status         VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    
    PRIMARY KEY (StudentID, CourseID),
    
    FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID)
        ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
        ON DELETE RESTRICT,
    
    CHECK (Status IN ('ACTIVE', 'DROPPED', 'COMPLETED'))
);
 
-- Alternative: Surrogate primary key for flexibility
CREATE TABLE ENROLLMENT_V2 (
    EnrollmentID   INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    StudentID      INT         NOT NULL,
    CourseID       VARCHAR(10) NOT NULL,
    EnrollmentDate DATE        NOT NULL,
    Grade          CHAR(2),
    Status         VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    
    -- Natural key as unique constraint
    UNIQUE (StudentID, CourseID),
    
    FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
    FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
);

When to Use Surrogate Key:

Use composite natural key when:

Relationship is truly unique (student enrolls in course once)
No other tables reference the enrollment
Simple queries suffice

Use surrogate key when:

Multiple enrollments possible (re-enrollment after dropping)
Other entities reference the enrollment (Grade Appeals → Enrollment)
ORM framework prefers single-column keys
History tracking needed (old enrollments kept)

Point-in-Time Attributes

Note that ORDER_ITEM typically stores 'PriceAtPurchase'—the price when the order was placed, not current product price. This is a crucial pattern: associative entities often capture point-in-time snapshots of key values to preserve historical accuracy.

Pattern: Type Hierarchies

The Pattern:

A supertype entity with multiple subtypes, each having specialized attributes or relationships. This models "is-a" relationships.

Common Examples:

Account: CheckingAccount, SavingsAccount, InvestmentAccount
Product: PhysicalProduct, DigitalProduct, SubscriptionProduct
Vehicle: Car, Truck, Motorcycle
Person: Student, Staff, Faculty (in university)
Transaction: Deposit, Withdrawal, Transfer, Payment

Mapping Strategies Compared:

We covered the three strategies in Design Decisions. Here's a practical template for each:

Best for: Few subtypes, similar attributes, frequent polymorphic queries

STI Template: Payment Hierarchy
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
CREATE TABLE PAYMENT (
    PaymentID       INT PRIMARY KEY AUTO_INCREMENT,
    PaymentType     VARCHAR(20) NOT NULL,  -- Discriminator
    Amount          DECIMAL(12,2) NOT NULL,
    PaymentDate     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Credit Card specific (NULL for others)
    CardLastFour    CHAR(4),
    CardExpiry      DATE,
    AuthorizationCode VARCHAR(20),
    
    -- Bank Transfer specific (NULL for others)
    BankAccountNum  VARCHAR(34),
    BankRoutingNum  VARCHAR(20),
    TransferRef     VARCHAR(50),
    
    -- Check specific (NULL for others)
    CheckNumber     VARCHAR(20),
    BankName        VARCHAR(100),
    
    CHECK (PaymentType IN ('CREDIT_CARD', 'BANK_TRANSFER', 'CHECK', 'CASH'))
);
 
-- Type-specific constraints via CHECK
ALTER TABLE PAYMENT ADD CONSTRAINT chk_credit_card 
    CHECK (PaymentType <> 'CREDIT_CARD' OR 
           (CardLastFour IS NOT NULL AND AuthorizationCode IS NOT NULL));

Pattern: Temporal Data (History Tracking)

The Pattern:

An entity's attributes change over time, and the system must track both current and historical values. This extends simple entity mapping with temporal dimensions.

Common Examples:

Employee salary history
Product price changes
Customer address history
Slowly Changing Dimensions (SCD) in data warehousing
Regulatory audit requirements

Temporal Mapping Approaches:

Temporal Data Strategies
Strategy	Description	Use Case
Type 1 (Overwrite)	Update in place, no history	Only current value needed
Type 2 (History Table)	Separate table tracks historical values	Full history required, current access optimized
Type 3 (Previous Value)	Add column for previous value	Only one prior version needed
Type 4 (History Flag)	All versions in same table with current flag	Simple queries for both current and history
Bi-Temporal	Track both valid time and transaction time	Regulatory/audit requirements

Type 2 History Pattern: Employee Salary
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
-- Current values table (optimized for normal operations)
CREATE TABLE EMPLOYEE (
    EmployeeID    INT PRIMARY KEY,
    Name          VARCHAR(100) NOT NULL,
    DepartmentID  INT,
    CurrentSalary DECIMAL(10,2) NOT NULL,
    LastUpdated   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (DepartmentID) REFERENCES DEPARTMENT(DeptID)
);
 
-- History table (all historical salary changes)
CREATE TABLE EMPLOYEE_SALARY_HISTORY (
    HistoryID     INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeID    INT NOT NULL,
    Salary        DECIMAL(10,2) NOT NULL,
    EffectiveFrom DATE NOT NULL,
    EffectiveTo   DATE,  -- NULL = current
    ChangedBy     INT,   -- User who made change
    ChangeReason  VARCHAR(200),
    
    FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
    
    -- Ensure no overlapping periods
    UNIQUE (EmployeeID, EffectiveFrom)
);
 
-- Trigger to auto-populate history on salary change
-- (Pseudo-code - syntax varies by DBMS)
CREATE TRIGGER trg_salary_history
BEFORE UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
    IF OLD.CurrentSalary <> NEW.CurrentSalary THEN
        -- Close current history record
        UPDATE EMPLOYEE_SALARY_HISTORY 
        SET EffectiveTo = CURRENT_DATE
        WHERE EmployeeID = NEW.EmployeeID 
          AND EffectiveTo IS NULL;
        
        -- Insert new history record  
        INSERT INTO EMPLOYEE_SALARY_HISTORY 
            (EmployeeID, Salary, EffectiveFrom)
        VALUES 
            (NEW.EmployeeID, NEW.CurrentSalary, CURRENT_DATE);
    END IF;
END;

SQL:2011 Temporal Tables

Modern databases (PostgreSQL, SQL Server, MariaDB, Oracle) support system-versioned temporal tables per SQL:2011. These automate history tracking at the database engine level: CREATE TABLE ... WITH SYSTEM VERSIONING. Consider using native temporal support when available.

Pattern: Aggregation (Relationship on Relationship)

The Pattern:

A relationship involves not just entities but another relationship. The aggregation construct treats a relationship set as a higher-level entity that can participate in other relationships.

Common Examples:

Company sponsors the (Employee, Project) assignment
Review evaluates the (Student, Course) enrollment
Manager oversees the (Project, Contractor) engagement
Insurance covers the (Patient, Treatment) procedure

Standard Mapping:

Map the inner relationship as a junction table (with its own identity), then the outer relationship references this junction:

Aggregation Pattern: Project Sponsorship
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
-- Base entities
CREATE TABLE EMPLOYEE (
    EmployeeID  INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL
);
 
CREATE TABLE PROJECT (
    ProjectID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL
);
 
CREATE TABLE COMPANY (
    CompanyID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL
);
 
-- The aggregated relationship (Employee works on Project)
-- This becomes an entity that can participate in other relationships
CREATE TABLE PROJECT_ASSIGNMENT (
    AssignmentID  INT PRIMARY KEY AUTO_INCREMENT,  -- Identity for aggregation
    EmployeeID    INT NOT NULL,
    ProjectID     INT NOT NULL,
    StartDate     DATE NOT NULL,
    Role          VARCHAR(50),
    
    UNIQUE (EmployeeID, ProjectID),  -- Natural key
    FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID)
);
 
-- The outer relationship: Company sponsors the Assignment
CREATE TABLE SPONSORSHIP (
    SponsorshipID   INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID       INT NOT NULL,
    AssignmentID    INT NOT NULL,  -- References the aggregated relationship
    FundingAmount   DECIMAL(12,2) NOT NULL,
    SponsorshipDate DATE NOT NULL,
    
    FOREIGN KEY (CompanyID) REFERENCES COMPANY(CompanyID),
    FOREIGN KEY (AssignmentID) REFERENCES PROJECT_ASSIGNMENT(AssignmentID),
    
    UNIQUE (CompanyID, AssignmentID)  -- Each company sponsors each assignment once
);

Why Surrogate Key on Inner Relationship?

The inner relationship (PROJECT_ASSIGNMENT) needs its own single-column key so that the outer relationship (SPONSORSHIP) can reference it easily. Without a surrogate, SPONSORSHIP would need a composite FK (EmployeeID, ProjectID), complicating the schema.

Pattern: Weak Entity Chains

The Pattern:

A weak entity is owned by another weak entity, creating a chain of identification dependencies. Each level's key includes all ancestor keys.

Common Examples:

Building → Floor → Room → Outlet
Country → State → City → Address
Company → Division → Department → Team
Book → Chapter → Section → Paragraph

Weak Entity Chain: Location Hierarchy
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
-- Level 0: Strong entity (root)
CREATE TABLE BUILDING (
    BuildingCode   VARCHAR(10) PRIMARY KEY,
    BuildingName   VARCHAR(100) NOT NULL,
    Address        VARCHAR(200)
);
 
-- Level 1: Weak entity, depends on Building
CREATE TABLE FLOOR (
    BuildingCode   VARCHAR(10) NOT NULL,
    FloorNumber    INT NOT NULL,
    FloorName      VARCHAR(50),  -- "Ground", "Mezzanine", etc.
    
    PRIMARY KEY (BuildingCode, FloorNumber),
    FOREIGN KEY (BuildingCode) 
        REFERENCES BUILDING(BuildingCode)
        ON DELETE CASCADE
);
 
-- Level 2: Weak entity, depends on Floor
CREATE TABLE ROOM (
    BuildingCode   VARCHAR(10) NOT NULL,
    FloorNumber    INT NOT NULL,
    RoomNumber     VARCHAR(10) NOT NULL,
    RoomType       VARCHAR(50),  -- "Office", "Conference", "Lab"
    Capacity       INT,
    
    PRIMARY KEY (BuildingCode, FloorNumber, RoomNumber),
    FOREIGN KEY (BuildingCode, FloorNumber) 
        REFERENCES FLOOR(BuildingCode, FloorNumber)
        ON DELETE CASCADE
);
 
-- Level 3: Weak entity, depends on Room
CREATE TABLE OUTLET (
    BuildingCode   VARCHAR(10) NOT NULL,
    FloorNumber    INT NOT NULL,
    RoomNumber     VARCHAR(10) NOT NULL,
    OutletID       VARCHAR(10) NOT NULL,  -- Discriminator at this level
    OutletType     VARCHAR(20) NOT NULL,  -- "POWER", "NETWORK", "PHONE"
    Location       VARCHAR(50),  -- "North wall", "Under desk 3"
    
    PRIMARY KEY (BuildingCode, FloorNumber, RoomNumber, OutletID),
    FOREIGN KEY (BuildingCode, FloorNumber, RoomNumber)
        REFERENCES ROOM(BuildingCode, FloorNumber, RoomNumber)
        ON DELETE CASCADE
);

Key Width Explosion

Weak entity chains create increasingly wide composite keys. At 4+ levels, consider whether a surrogate key strategy might simplify the schema. Trade-off: surrogates lose natural identification but simplify FKs and joins.

Surrogate Alternative:

CREATE TABLE OUTLET (
    OutletID       INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    RoomID         INT NOT NULL,  -- FK to ROOM's surrogate
    OutletCode     VARCHAR(10) NOT NULL,  -- Local discriminator
    OutletType     VARCHAR(20) NOT NULL,
    Location       VARCHAR(50),
    
    UNIQUE (RoomID, OutletCode),  -- Natural uniqueness preserved
    FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID)
);

This trades chain visibility for simpler FKs. The full path (Building→Floor→Room→Outlet) now requires joining through the chain rather than being present in each row.

Summary: Common Patterns

Patterns accelerate mapping by providing proven templates for recurring structures. Let's consolidate the key patterns:

Key Takeaways

•Self-Referential Pattern — FK to same table's PK; enables hierarchies with recursive CTEs
•Associative Entity Pattern — Rich junction tables with their own attributes; consider surrogate keys for referenceability
•Type Hierarchy Pattern — STI for simplicity, CTI for normalization, Concrete for independence; match to query patterns
•Temporal Pattern — History tables, Type 2 SCD, or native temporal support for tracking changes over time
•Aggregation Pattern — Give inner relationships identity so outer relationships can reference them
•Weak Entity Chain Pattern — Composite keys grow with depth; surrogates trade visibility for simplicity

Pattern Application Strategy:

Study the catalog — Internalize these patterns so you recognize them quickly
Identify matches — When mapping, check if ER structures match known patterns
Apply templates — Use pattern templates as starting points
Customize — Adapt naming, constraints, and variations for your domain
Validate — Verify the adapted pattern meets quality criteria

Patterns don't replace understanding—they leverage it. By combining pattern recognition with deep knowledge of the mapping algorithm and design decisions, you can map complex schemas efficiently and correctly.

Module Complete

Congratulations! You've completed Module 1: Mapping Overview. You now understand the ER-to-relational process, the systematic mapping algorithm, key design decisions, quality criteria, and common patterns. This foundation prepares you to map any ER diagram to a correct, efficient, and maintainable relational schema. Continue to the next module to explore entity and attribute mapping in greater detail.

Common Patterns

Patterns Accelerate Mapping

What You Will Learn

Pattern Categories

Mapping patterns cluster into several categories based on the type of ER construct they address:

Pattern Taxonomy:

Mapping Pattern Categories
Category	Patterns Included	Common Domains
Self-Referential	Hierarchy, Network, Adjacency List	Org charts, BOM, social graphs
Associative Entity	Bridge table with attributes, History tracking	Enrollment, employment, subscriptions
Type/Subtype	Single table, Class table, Concrete table	Products, vehicles, financial instruments
Aggregation	Relationship on relationship	Sponsorship, project oversight
Temporal	Slowly changing, Bi-temporal	Audit, versioning, effective dating
Weak Entity	Dependent identification, Multi-level weakness	Line items, room numbers, component IDs

How to Use Patterns:

Recognition: When analyzing an ER diagram, identify which pattern(s) the structure matches
Template Application: Apply the standard mapping for that pattern
Customization: Adjust for domain-specific requirements (naming, constraints, etc.)
Validation: Verify the mapping meets quality criteria

Patterns are not rigid rules—they're proven starting points. Each domain may require adaptations, but starting from a pattern prevents common errors.

Pattern: Self-Referential Hierarchy

The Pattern:

An entity type has a relationship with itself, creating parent-child or peer relationships within a single entity set. This is also called a recursive relationship or unary relationship.

Common Examples:

Employee supervises Employee (organizational hierarchy)
Category contains Category (product classification)
Part contains Part (bill of materials)
Person refers Person (referral network)
Folder contains Folder (file system structure)

Standard Mapping:

The self-referential relationship maps as a foreign key in the same table referencing its own primary key:

Self-Referential Pattern: Employee Hierarchy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Employee supervises Employee (1:N self-referential)
-- Each employee has at most one supervisor
-- Each supervisor can have many supervisees
 
CREATE TABLE EMPLOYEE (
    EmployeeID    INT          PRIMARY KEY,
    Name          VARCHAR(100) NOT NULL,
    Title         VARCHAR(50),
    HireDate      DATE         NOT NULL,
    
    -- Self-referential FK for supervision hierarchy
    SupervisorID  INT,  -- NULL for top-level (CEO, founders)
    
    FOREIGN KEY (SupervisorID) 
        REFERENCES EMPLOYEE(EmployeeID)
        ON DELETE SET NULL  -- If supervisor leaves, don't delete employees
        ON UPDATE CASCADE
);
 
-- Useful indexes for hierarchical queries
CREATE INDEX idx_employee_supervisor ON EMPLOYEE(SupervisorID);

Variations:

1. M:N Self-Referential (Network):

When entities can have multiple relationships with each other (e.g., social network friendships, component compatibility):

-- Person knows Person (M:N self-referential)
CREATE TABLE PERSON (
    PersonID   INT PRIMARY KEY,
    Name       VARCHAR(100) NOT NULL
);

CREATE TABLE KNOWS (
    Person1ID  INT NOT NULL,
    Person2ID  INT NOT NULL,
    SinceDate  DATE,
    
    PRIMARY KEY (Person1ID, Person2ID),
    FOREIGN KEY (Person1ID) REFERENCES PERSON(PersonID),
    FOREIGN KEY (Person2ID) REFERENCES PERSON(PersonID),
    
    -- Optional: prevent self-relationship
    CHECK (Person1ID <> Person2ID)
);

2. Symmetric vs. Directional:

Symmetric (Person knows Person): Query both directions
Directional (Employee supervises Employee): Parent-child distinction matters

For symmetric relationships, consider storing both directions or using UNION queries.

Hierarchical Query Support

Hierarchical Query Example
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Find all employees under a given manager (recursive)
WITH RECURSIVE OrgChart AS (
    -- Base case: the starting manager
    SELECT EmployeeID, Name, SupervisorID, 1 AS Level
    FROM EMPLOYEE
    WHERE EmployeeID = 100  -- Manager to start from
    
    UNION ALL
    
    -- Recursive case: employees supervised by previous level
    SELECT e.EmployeeID, e.Name, e.SupervisorID, oc.Level + 1
    FROM EMPLOYEE e
    JOIN OrgChart oc ON e.SupervisorID = oc.EmployeeID
)
SELECT * FROM OrgChart ORDER BY Level, Name;

Pattern: Associative Entity (Rich Junction)

The Pattern:

An M:N relationship that has its own attributes, making it semantically more than just a link between entities. The relationship itself carries important data.

Common Examples:

Student enrolled in Course (EnrollmentDate, Grade)
Actor stars in Movie (Role, Billing)
Product in Order (Quantity, Price at time of order)
Employee assigned to Project (Hours, StartDate, Role)

Standard Mapping:

The associative entity becomes a full table with its own identity, not just a junction:

Associative Entity Pattern: Course Enrollment
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
CREATE TABLE STUDENT (
    StudentID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL,
    Email       VARCHAR(100) UNIQUE NOT NULL
);
 
CREATE TABLE COURSE (
    CourseID    VARCHAR(10) PRIMARY KEY,
    Title       VARCHAR(100) NOT NULL,
    Credits     INT NOT NULL
);
 
-- ENROLLMENT is an associative entity, not just a junction
CREATE TABLE ENROLLMENT (
    -- Option A: Composite primary key (natural)
    StudentID      INT         NOT NULL,
    CourseID       VARCHAR(10) NOT NULL,
    
    -- Relationship attributes that make this an entity
    EnrollmentDate DATE        NOT NULL DEFAULT CURRENT_DATE,
    Grade          CHAR(2),  -- NULL until graded
    Status         VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    
    PRIMARY KEY (StudentID, CourseID),
    
    FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID)
        ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
        ON DELETE RESTRICT,
    
    CHECK (Status IN ('ACTIVE', 'DROPPED', 'COMPLETED'))
);
 
-- Alternative: Surrogate primary key for flexibility
CREATE TABLE ENROLLMENT_V2 (
    EnrollmentID   INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    StudentID      INT         NOT NULL,
    CourseID       VARCHAR(10) NOT NULL,
    EnrollmentDate DATE        NOT NULL,
    Grade          CHAR(2),
    Status         VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    
    -- Natural key as unique constraint
    UNIQUE (StudentID, CourseID),
    
    FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
    FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
);

When to Use Surrogate Key:

Use composite natural key when:

Relationship is truly unique (student enrolls in course once)
No other tables reference the enrollment
Simple queries suffice

Use surrogate key when:

Multiple enrollments possible (re-enrollment after dropping)
Other entities reference the enrollment (Grade Appeals → Enrollment)
ORM framework prefers single-column keys
History tracking needed (old enrollments kept)

Point-in-Time Attributes

Pattern: Type Hierarchies

The Pattern:

A supertype entity with multiple subtypes, each having specialized attributes or relationships. This models "is-a" relationships.

Common Examples:

Account: CheckingAccount, SavingsAccount, InvestmentAccount
Product: PhysicalProduct, DigitalProduct, SubscriptionProduct
Vehicle: Car, Truck, Motorcycle
Person: Student, Staff, Faculty (in university)
Transaction: Deposit, Withdrawal, Transfer, Payment

Mapping Strategies Compared:

We covered the three strategies in Design Decisions. Here's a practical template for each:

Best for: Few subtypes, similar attributes, frequent polymorphic queries

STI Template: Payment Hierarchy
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
CREATE TABLE PAYMENT (
    PaymentID       INT PRIMARY KEY AUTO_INCREMENT,
    PaymentType     VARCHAR(20) NOT NULL,  -- Discriminator
    Amount          DECIMAL(12,2) NOT NULL,
    PaymentDate     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Credit Card specific (NULL for others)
    CardLastFour    CHAR(4),
    CardExpiry      DATE,
    AuthorizationCode VARCHAR(20),
    
    -- Bank Transfer specific (NULL for others)
    BankAccountNum  VARCHAR(34),
    BankRoutingNum  VARCHAR(20),
    TransferRef     VARCHAR(50),
    
    -- Check specific (NULL for others)
    CheckNumber     VARCHAR(20),
    BankName        VARCHAR(100),
    
    CHECK (PaymentType IN ('CREDIT_CARD', 'BANK_TRANSFER', 'CHECK', 'CASH'))
);
 
-- Type-specific constraints via CHECK
ALTER TABLE PAYMENT ADD CONSTRAINT chk_credit_card 
    CHECK (PaymentType <> 'CREDIT_CARD' OR 
           (CardLastFour IS NOT NULL AND AuthorizationCode IS NOT NULL));

Pattern: Temporal Data (History Tracking)

The Pattern:

An entity's attributes change over time, and the system must track both current and historical values. This extends simple entity mapping with temporal dimensions.

Common Examples:

Employee salary history
Product price changes
Customer address history
Slowly Changing Dimensions (SCD) in data warehousing
Regulatory audit requirements

Temporal Mapping Approaches:

Temporal Data Strategies
Strategy	Description	Use Case
Type 1 (Overwrite)	Update in place, no history	Only current value needed
Type 2 (History Table)	Separate table tracks historical values	Full history required, current access optimized
Type 3 (Previous Value)	Add column for previous value	Only one prior version needed
Type 4 (History Flag)	All versions in same table with current flag	Simple queries for both current and history
Bi-Temporal	Track both valid time and transaction time	Regulatory/audit requirements

Type 2 History Pattern: Employee Salary
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
-- Current values table (optimized for normal operations)
CREATE TABLE EMPLOYEE (
    EmployeeID    INT PRIMARY KEY,
    Name          VARCHAR(100) NOT NULL,
    DepartmentID  INT,
    CurrentSalary DECIMAL(10,2) NOT NULL,
    LastUpdated   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (DepartmentID) REFERENCES DEPARTMENT(DeptID)
);
 
-- History table (all historical salary changes)
CREATE TABLE EMPLOYEE_SALARY_HISTORY (
    HistoryID     INT PRIMARY KEY AUTO_INCREMENT,
    EmployeeID    INT NOT NULL,
    Salary        DECIMAL(10,2) NOT NULL,
    EffectiveFrom DATE NOT NULL,
    EffectiveTo   DATE,  -- NULL = current
    ChangedBy     INT,   -- User who made change
    ChangeReason  VARCHAR(200),
    
    FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
    
    -- Ensure no overlapping periods
    UNIQUE (EmployeeID, EffectiveFrom)
);
 
-- Trigger to auto-populate history on salary change
-- (Pseudo-code - syntax varies by DBMS)
CREATE TRIGGER trg_salary_history
BEFORE UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
    IF OLD.CurrentSalary <> NEW.CurrentSalary THEN
        -- Close current history record
        UPDATE EMPLOYEE_SALARY_HISTORY 
        SET EffectiveTo = CURRENT_DATE
        WHERE EmployeeID = NEW.EmployeeID 
          AND EffectiveTo IS NULL;
        
        -- Insert new history record  
        INSERT INTO EMPLOYEE_SALARY_HISTORY 
            (EmployeeID, Salary, EffectiveFrom)
        VALUES 
            (NEW.EmployeeID, NEW.CurrentSalary, CURRENT_DATE);
    END IF;
END;

SQL:2011 Temporal Tables

Pattern: Aggregation (Relationship on Relationship)

The Pattern:

A relationship involves not just entities but another relationship. The aggregation construct treats a relationship set as a higher-level entity that can participate in other relationships.

Common Examples:

Company sponsors the (Employee, Project) assignment
Review evaluates the (Student, Course) enrollment
Manager oversees the (Project, Contractor) engagement
Insurance covers the (Patient, Treatment) procedure

Standard Mapping:

Map the inner relationship as a junction table (with its own identity), then the outer relationship references this junction:

Aggregation Pattern: Project Sponsorship
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
-- Base entities
CREATE TABLE EMPLOYEE (
    EmployeeID  INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL
);
 
CREATE TABLE PROJECT (
    ProjectID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL
);
 
CREATE TABLE COMPANY (
    CompanyID   INT PRIMARY KEY,
    Name        VARCHAR(100) NOT NULL
);
 
-- The aggregated relationship (Employee works on Project)
-- This becomes an entity that can participate in other relationships
CREATE TABLE PROJECT_ASSIGNMENT (
    AssignmentID  INT PRIMARY KEY AUTO_INCREMENT,  -- Identity for aggregation
    EmployeeID    INT NOT NULL,
    ProjectID     INT NOT NULL,
    StartDate     DATE NOT NULL,
    Role          VARCHAR(50),
    
    UNIQUE (EmployeeID, ProjectID),  -- Natural key
    FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID)
);
 
-- The outer relationship: Company sponsors the Assignment
CREATE TABLE SPONSORSHIP (
    SponsorshipID   INT PRIMARY KEY AUTO_INCREMENT,
    CompanyID       INT NOT NULL,
    AssignmentID    INT NOT NULL,  -- References the aggregated relationship
    FundingAmount   DECIMAL(12,2) NOT NULL,
    SponsorshipDate DATE NOT NULL,
    
    FOREIGN KEY (CompanyID) REFERENCES COMPANY(CompanyID),
    FOREIGN KEY (AssignmentID) REFERENCES PROJECT_ASSIGNMENT(AssignmentID),
    
    UNIQUE (CompanyID, AssignmentID)  -- Each company sponsors each assignment once
);

Why Surrogate Key on Inner Relationship?

Pattern: Weak Entity Chains

The Pattern:

A weak entity is owned by another weak entity, creating a chain of identification dependencies. Each level's key includes all ancestor keys.

Common Examples:

Building → Floor → Room → Outlet
Country → State → City → Address
Company → Division → Department → Team
Book → Chapter → Section → Paragraph

Weak Entity Chain: Location Hierarchy
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
-- Level 0: Strong entity (root)
CREATE TABLE BUILDING (
    BuildingCode   VARCHAR(10) PRIMARY KEY,
    BuildingName   VARCHAR(100) NOT NULL,
    Address        VARCHAR(200)
);
 
-- Level 1: Weak entity, depends on Building
CREATE TABLE FLOOR (
    BuildingCode   VARCHAR(10) NOT NULL,
    FloorNumber    INT NOT NULL,
    FloorName      VARCHAR(50),  -- "Ground", "Mezzanine", etc.
    
    PRIMARY KEY (BuildingCode, FloorNumber),
    FOREIGN KEY (BuildingCode) 
        REFERENCES BUILDING(BuildingCode)
        ON DELETE CASCADE
);
 
-- Level 2: Weak entity, depends on Floor
CREATE TABLE ROOM (
    BuildingCode   VARCHAR(10) NOT NULL,
    FloorNumber    INT NOT NULL,
    RoomNumber     VARCHAR(10) NOT NULL,
    RoomType       VARCHAR(50),  -- "Office", "Conference", "Lab"
    Capacity       INT,
    
    PRIMARY KEY (BuildingCode, FloorNumber, RoomNumber),
    FOREIGN KEY (BuildingCode, FloorNumber) 
        REFERENCES FLOOR(BuildingCode, FloorNumber)
        ON DELETE CASCADE
);
 
-- Level 3: Weak entity, depends on Room
CREATE TABLE OUTLET (
    BuildingCode   VARCHAR(10) NOT NULL,
    FloorNumber    INT NOT NULL,
    RoomNumber     VARCHAR(10) NOT NULL,
    OutletID       VARCHAR(10) NOT NULL,  -- Discriminator at this level
    OutletType     VARCHAR(20) NOT NULL,  -- "POWER", "NETWORK", "PHONE"
    Location       VARCHAR(50),  -- "North wall", "Under desk 3"
    
    PRIMARY KEY (BuildingCode, FloorNumber, RoomNumber, OutletID),
    FOREIGN KEY (BuildingCode, FloorNumber, RoomNumber)
        REFERENCES ROOM(BuildingCode, FloorNumber, RoomNumber)
        ON DELETE CASCADE
);

Key Width Explosion

Surrogate Alternative:

CREATE TABLE OUTLET (
    OutletID       INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate
    RoomID         INT NOT NULL,  -- FK to ROOM's surrogate
    OutletCode     VARCHAR(10) NOT NULL,  -- Local discriminator
    OutletType     VARCHAR(20) NOT NULL,
    Location       VARCHAR(50),
    
    UNIQUE (RoomID, OutletCode),  -- Natural uniqueness preserved
    FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID)
);

This trades chain visibility for simpler FKs. The full path (Building→Floor→Room→Outlet) now requires joining through the chain rather than being present in each row.

Summary: Common Patterns

Patterns accelerate mapping by providing proven templates for recurring structures. Let's consolidate the key patterns:

Key Takeaways

•Self-Referential Pattern — FK to same table's PK; enables hierarchies with recursive CTEs
•Associative Entity Pattern — Rich junction tables with their own attributes; consider surrogate keys for referenceability
•Type Hierarchy Pattern — STI for simplicity, CTI for normalization, Concrete for independence; match to query patterns
•Temporal Pattern — History tables, Type 2 SCD, or native temporal support for tracking changes over time
•Aggregation Pattern — Give inner relationships identity so outer relationships can reference them
•Weak Entity Chain Pattern — Composite keys grow with depth; surrogates trade visibility for simplicity

Pattern Application Strategy:

Study the catalog — Internalize these patterns so you recognize them quickly
Identify matches — When mapping, check if ER structures match known patterns
Apply templates — Use pattern templates as starting points
Customize — Adapt naming, constraints, and variations for your domain
Validate — Verify the adapted pattern meets quality criteria

Module Complete