Identifying Relationships in ER Modeling

Owner entities provide identity context. Discriminators distinguish instances within that context. But how exactly do these elements combine to create a complete, globally unique identifier for dependent entities? This is the domain of composite key formation.

A composite primary key is a key consisting of two or more attributes that together uniquely identify an entity instance. For dependent entities, this composite key has a specific structure: it contains the owner's primary key (in its entirety) plus the dependent's discriminator. This composition isn't arbitrary—it follows precise rules that ensure global uniqueness while preserving the semantic relationship between owner and dependent.

Understanding composite key formation is essential for translating ER models to relational schemas, designing efficient database structures, maintaining referential integrity, and implementing cascading operations correctly.

A composite primary key for a dependent entity consists of two distinct components, each serving a specific purpose in establishing unique identity.

Component 1: Inherited Key (From Owner)

This component is the complete primary key of the owner entity, inherited in its entirety by the dependent. If the owner itself has a composite key, that entire composite becomes part of the dependent's key.

Component 2: Discriminator (From Dependent)

This component is the dependent's own attribute(s) that distinguish it from other dependents of the same owner. Together with the inherited key, it achieves global uniqueness.

Formal Composition Rule:

Let E_o be an owner entity with primary key PK_o = {a₁, a₂, ..., aₘ} Let E_d be a dependent entity with discriminator D = {d₁, d₂, ..., dₙ}

The composite primary key of E_d is:

PK_d = PK_o ∪ D = {a₁, a₂, ..., aₘ, d₁, d₂, ..., dₙ}

Key Properties:

1. Containment: PK_o ⊂ PK_d (owner's key is contained within dependent's key)
2. Minimality: No attribute in (PK_o ∪ D) is redundant for uniqueness
3. Inheritance: All attributes of PK_o are inherited, not copied; they reference the owner instance

When dependent entities form chains or hierarchies, the owner's key propagates through each level, growing as it descends. Each level inherits the full key from its parent and adds its own discriminator.

Three-Level Example: Order System

Level 0: Order (Strong Entity)
    PK: order_id

Level 1: OrderLine (Dependent on Order)
    Inherited: order_id
    Discriminator: line_number
    Composite PK: (order_id, line_number)

Level 2: OrderLineAllocation (Dependent on OrderLine)
    Inherited: (order_id, line_number)  ← entire parent key
    Discriminator: allocation_seq
    Composite PK: (order_id, line_number, allocation_seq)

Key Width Growth:

Four-Level Academic Example:

University (PK: university_id)                           Width: 1
    └─→ Department (PK: university_id, dept_code)        Width: 2
            └─→ Course (PK: university_id, dept_code, course_num)    Width: 3
                    └─→ Section (PK: university_id, dept_code, course_num, section_id)    Width: 4

To reference a specific Section, you need ALL FOUR components. The key carries the complete path from root to leaf.

Hierarchy Flattening:

The composite key essentially 'flattens' the hierarchy into the key structure. Instead of traversing entity relationships to determine identity, the key embeds the entire ancestry. This enables direct access but requires carrying full context.

When other entities reference a dependent entity, they must reference its complete composite primary key. This affects foreign key design and referential integrity constraints.

Referencing a Dependent Entity:

Consider a ShipmentLine entity that references OrderLine:

CREATE TABLE order_line (
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, line_number),
    FOREIGN KEY (order_id) REFERENCES order(order_id)
);

CREATE TABLE shipment_line (
    shipment_id INT NOT NULL,
    shipment_line_num INT NOT NULL,
    order_id INT NOT NULL,          -- FK part 1
    order_line_number INT NOT NULL, -- FK part 2
    shipped_quantity INT,
    PRIMARY KEY (shipment_id, shipment_line_num),
    FOREIGN KEY (order_id, order_line_number) 
        REFERENCES order_line(order_id, line_number)
);

The foreign key to order_line must include BOTH components of its primary key.

Referential Integrity Cascades:

With composite foreign keys, referential actions must handle all components:

FOREIGN KEY (order_id, order_line_number)
    REFERENCES order_line(order_id, line_number)
    ON DELETE CASCADE
    ON UPDATE CASCADE

If order_line (1000, 3) is deleted, all shipment_line rows referencing (1000, 3) are cascade-deleted.

If the primary key of order_line could change (rare for natural keys, but possible), ON UPDATE CASCADE would propagate the change to all references.

Translating conceptual ER models with dependent entities to relational schemas follows a systematic mapping process. The composite key structure emerges naturally from this mapping.

Complete Mapping Example:

Conceptual ER Model:

BUILDING (building_id, name, address)
    |__ [contains] __|
                     |
               ROOM (room_number, capacity, type)
                    [dependent on BUILDING]

Relational Schema:

CREATE TABLE building (
    building_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200)
);

CREATE TABLE room (
    building_id INT NOT NULL,           -- Inherited from owner
    room_number VARCHAR(10) NOT NULL,   -- Discriminator
    capacity INT,
    type VARCHAR(50),
    PRIMARY KEY (building_id, room_number),
    FOREIGN KEY (building_id) REFERENCES building(building_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The building_id in room serves dual roles:

1. Part of the composite primary key (enabling uniqueness)
2. Foreign key to building (establishing the relationship)

Composite primary keys automatically create indexes, but understanding their behavior and designing supplementary indexes is crucial for query performance.

Composite Index Behavior:

A composite primary key like (order_id, line_number) creates an index that is most efficient for queries matching the leftmost prefix:

-- Excellent: Both components (exact match)
SELECT * FROM order_line 
WHERE order_id = 1000 AND line_number = 3;

-- Good: Leading component only (range scan within owner)
SELECT * FROM order_line 
WHERE order_id = 1000;

-- Poor: Trailing component only (index not useful)
SELECT * FROM order_line 
WHERE line_number = 3;  -- Full table scan!

This is the leftmost prefix rule: composite indexes are useful when queries specify a leading subset of the key columns in order.

Clustered Index Considerations:

In databases with clustered indexes (SQL Server, MySQL InnoDB), the primary key determines physical row ordering:

• Composite PK (order_id, line_number) physically clusters all lines of an order together
• Sequential reads of 'all lines for order X' are very efficient
• But inserting new lines may cause page splits if order_id is not monotonically increasing

For high-insert workloads on dependent entities:

1. Ensure owner's key component is somewhat sequential (monotonic order_ids help)
2. Or accept some fragmentation as trade-off for semantic clustering
3. Or consider a surrogate PK with a secondary unique index on the natural composite

Some entities depend on multiple owner entities simultaneously. These create composite keys that include primary keys from multiple parents, representing intersection or association entities.

Classic Example: Many-to-Many Resolution

The Enrollment entity depends on both Student AND Course:

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE course (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE enrollment (
    student_id INT NOT NULL,  -- From Student
    course_id INT NOT NULL,   -- From Course
    enrollment_date DATE,
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Here, both student_id and course_id are inherited keys. Is there a discriminator? In this case, the combination of parent keys IS sufficient for uniqueness—no additional discriminator needed.

Multi-Owner with Additional Discriminator:

Sometimes even with multiple parents, an additional discriminator is needed:

-- Student can enroll in same course multiple times (retakes)
CREATE TABLE enrollment (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    attempt_number INT NOT NULL,  -- Discriminator for retakes
    enrollment_date DATE,
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id, attempt_number),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Now the composite key is three components: two inherited plus one discriminator.

Triple-Owner Example:

Project assignments might involve Employee, Project, and Role:

CREATE TABLE assignment (
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    role_id INT NOT NULL,
    start_date DATE,
    allocation_percent INT,
    PRIMARY KEY (employee_id, project_id, role_id),
    -- Three foreign keys to three parent tables
);

The assignment is uniquely identified by the combination of all three parent keys.

While composite keys faithfully represent dependent entity semantics, practical considerations sometimes favor surrogate keys. Understanding when to use each approach is essential for effective design.

Hybrid Approach: Surrogate PK + Natural Unique Constraint

CREATE TABLE order_line (
    order_line_id INT PRIMARY KEY AUTO_INCREMENT,  -- Surrogate PK
    order_id INT NOT NULL,
    line_number INT NOT NULL,
    product_id INT,
    quantity INT,
    UNIQUE (order_id, line_number),  -- Natural key as constraint
    FOREIGN KEY (order_id) REFERENCES order(order_id)
);

What You Gain:

• Single-column PK for compact FKs and simple joins
• Natural key uniqueness still enforced
• Flexibility to re-parent (change order_id) if needed
• ORM compatibility (most ORMs prefer auto-increment PKs)

What You Lose:

• Key no longer self-documenting ('order_line_id = 7382' vs '(1000, 3)')
• Extra index for uniqueness constraint
• Slight additional storage
• Must query to determine line's order (vs. reading key directly)

Let's examine complete practical examples showing composite key formation, foreign key design, and query patterns.

We've thoroughly examined composite key formation—the mechanism by which owner keys and discriminators combine to create complete identity for dependent entities. Let's consolidate the essential knowledge:

What's Next:

With the mechanics of composite key formation mastered, we'll conclude this module by examining practical examples of identifying relationships across diverse domains. The next page presents comprehensive case studies that integrate all concepts: owner entities, dependent entities, discriminators, and composite keys working together in real-world scenarios.