Relationships in ER Modeling

Consider an enrollment: a student enrolls in a course. Where does the enrollment date belong? Not to the student (they have many enrollment dates for different courses). Not to the course (different students enrolled on different dates). The date belongs to the relationship itself—to the specific pairing of this student with this course.

This is a relationship attribute: a property that describes the association between entities rather than any single entity. Relationship attributes are one of the most important yet often misunderstood concepts in ER modeling. They capture data that exists only in the context of connection.

Mastering relationship attributes is essential for creating accurate data models. Place an attribute in the wrong location—on an entity instead of the relationship—and you'll struggle with data redundancy, update anomalies, or inability to represent the required information at all.

A relationship attribute is an attribute that describes a characteristic of the association between entity instances, rather than a characteristic of any participating entity.

Formal Definition:

Given a relationship R between entity types E₁, E₂, ..., Eₙ, an attribute A is a relationship attribute if the value of A depends on the specific combination (e₁, e₂, ..., eₙ) rather than on any single eᵢ.

The Dependency Test:

To determine if an attribute belongs on a relationship:

1. Does the value change based on which entities are combined?
2. Would placing it on either entity cause redundancy or ambiguity?
3. Does the attribute only exist when the relationship exists?

If all answers are "yes," it's a relationship attribute.

ER Diagram Notation:

In Chen notation, relationship attributes attach directly to the diamond:

                           ┌───────────────┐
                           │     grade     │
                           └───────┬───────┘
                                   │
   ┌──────────┐         ┌──────────◇──────────┐         ┌──────────┐
   │ STUDENT  │─────────│      ENROLLS_IN     │─────────│  COURSE  │
   └──────────┘         └──────────┬──────────┘         └──────────┘
                                   │
                           ┌───────┴───────┐
                           │ enrollment_date│
                           └───────────────┘

The ovals for grade and enrollment_date connect to the diamond, not to either rectangle.

One of the most common modeling mistakes is placing relationship attributes on entities. This section provides a rigorous framework for distinguishing the two.

The Cardinal Rule:

An attribute belongs on the construct (entity or relationship) whose instances it describes. If the attribute varies with each relationship instance, it belongs on the relationship. If it's constant across all relationships for that entity, it belongs on the entity.

Case Study: Employee Salary

Consider an employee's salary. Where should it go?

Scenario A: Fixed salary per employee

• If each employee has one fixed salary regardless of role or department
• Then salary is an entity attribute on EMPLOYEE
• Implementation: column in Employee table

Scenario B: Salary varies by assignment

• If the same employee earns different amounts in different roles/departments
• Then salary is a relationship attribute on WORKS_IN
• Implementation: column in junction table or in the Employee record if 1:N

Scenario C: Salary changes over time

• If historical salary records must be maintained
• Then SALARY_HISTORY might become its own entity
• It's related to EMPLOYEE with start_date, end_date, amount

The same data item can belong to different constructs depending on business requirements!

The cardinality of a relationship affects how its attributes are implemented and whether they can be migrated to participating entities.

Many-to-Many (M:N):

Relationship attributes in M:N relationships are unambiguously necessary. They cannot be placed on either entity without creating redundancy or losing distinct values.

M:N: ENROLLS_IN (Student, Course, grade, enrollment_date)

   STUDENT                                          COURSE
   ┌────────┐     ┌──────────────────────┐     ┌────────┐
   │ S101   │────▶│ (S101, C01, A, 2024) │◀────│ C01    │
   │        │────▶│ (S101, C02, B, 2024) │     │        │
   └────────┘     │ (S102, C01, A-, 2024)│◀────│ C02    │
   ┌────────┐────▶│ (S102, C03, C, 2024) │     │        │
   │ S102   │     └──────────────────────┘     │ C03    │
   └────────┘                                   └────────┘

Each (student, course) pair has its own grade and date.
Attributes cannot move to either entity without duplication.

One-to-Many (1:N):

In 1:N relationships, relationship attributes can theoretically migrate to the "many" side entity because each "many" entity participates in exactly one relationship instance.

1:N: WORKS_IN (Employee, Department, start_date, position)

Can migrate to Employee:
   Employee table:
   emp_id | name    | dept_id | start_date | position
   E01    | Alice   | D01     | 2020-01-15 | Developer
   E02    | Bob     | D01     | 2021-03-20 | Analyst
   E03    | Charlie | D02     | 2019-07-10 | Manager

This works because each employee has exactly one (start_date, position) for their current department. However, conceptually recognizing these as relationship attributes helps if requirements change (e.g., historical tracking needed).

The implementation of relationship attributes in SQL follows directly from the relationship's cardinality and whether a junction table exists.

M:N Implementation (Junction Table Required):

1:N Implementation (Attributes in N-side Table):

-- Department (1-side)
CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    name VARCHAR(100),
    budget DECIMAL(15,2)
);

-- Employee (N-side) with relationship attributes
CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    -- Foreign key for relationship
    dept_id INT NOT NULL,
    -- Relationship attributes (describe WORKS_IN, not Employee intrinsically)
    start_date DATE NOT NULL,
    position VARCHAR(100),
    is_department_head BOOLEAN DEFAULT FALSE,
    -- Constraint
    FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

Note that start_date, position, and is_department_head are relationship attributes even though they're stored in the Employee table. They describe the employee-department association, not the employee alone.

Relationship attributes appear in queries whenever you need information about the association itself, not just the participating entities.

Common Query Patterns:

Update Patterns:

-- Update a specific enrollment's grade
UPDATE Enrollment 
SET grade = 'A', status = 'completed'
WHERE student_id = 101 AND course_id = 2;

-- Bulk update relationship attributes
UPDATE Enrollment
SET status = 'withdrawn'
WHERE enrollment_date < '2023-01-01' AND grade IS NULL;

Important: Notice that relationship attribute updates require identifying the specific relationship instance (the student-course pair), not just one entity.

Relationship attributes can be composite or multi-valued, just like entity attributes. These cases require special handling.

Composite Relationship Attributes:

A composite attribute has sub-components that together describe one characteristic.

SUPPLIES (Vendor, Product) with:
   contract_period: (start_date, end_date)
   contact: (name, phone, email)

Implementation Options:

1. Flatten into separate columns: contract_start_date, contract_end_date
2. Use composite type (if DBMS supports): contract_period DATERANGE
3. JSON field (flexible but less queryable): contract JSON

Multi-Valued Relationship Attributes:

A multi-valued attribute can have multiple values for a single relationship instance.

ENROLLS_IN (Student, Course) with:
   attendance_dates: [2024-01-15, 2024-01-17, 2024-01-19, ...]
   homework_scores: [95, 87, 92, ...]

Implementation requires a separate table:

-- Main relationship table
CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

-- Multi-valued relationship attribute
CREATE TABLE EnrollmentAttendance (
    student_id INT,
    course_id INT,
    attendance_date DATE,
    status ENUM('present', 'absent', 'excused'),
    PRIMARY KEY (student_id, course_id, attendance_date),
    FOREIGN KEY (student_id, course_id) 
        REFERENCES Enrollment(student_id, course_id)
);

-- Another multi-valued relationship attribute
CREATE TABLE EnrollmentHomework (
    student_id INT,
    course_id INT,
    homework_number INT,
    score DECIMAL(5,2),
    submitted_date DATETIME,
    PRIMARY KEY (student_id, course_id, homework_number),
    FOREIGN KEY (student_id, course_id) 
        REFERENCES Enrollment(student_id, course_id)
);

Relationships often have temporal dimensions—they exist during a time period, or their attributes change over time. Handling temporality in relationship attributes requires careful design.

Time-Bounded Relationships:

Some relationships have explicit start and end times:

CREATE TABLE Employment (
    employee_id INT,
    company_id INT,
    start_date DATE NOT NULL,
    end_date DATE,              -- NULL means current
    position VARCHAR(100),
    salary DECIMAL(12,2),
    PRIMARY KEY (employee_id, company_id, start_date), -- Includes start for history
    FOREIGN KEY (employee_id) REFERENCES Employee(id),
    FOREIGN KEY (company_id) REFERENCES Company(id)
);

The (employee_id, company_id, start_date) primary key allows the same employee-company pair to appear multiple times with different periods (rehires, promotions recorded as new periods).

Historical Tracking:

When relationship attribute values change and history must be preserved:

-- Current enrollments (standard design)
CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    current_grade VARCHAR(2),
    PRIMARY KEY (student_id, course_id)
);

-- Grade history (relationship attribute changes over time)
CREATE TABLE EnrollmentGradeHistory (
    student_id INT,
    course_id INT,
    grade VARCHAR(2),
    effective_date DATETIME,
    changed_by INT,  -- Who made the change
    reason VARCHAR(200),  -- Why it changed
    PRIMARY KEY (student_id, course_id, effective_date),
    FOREIGN KEY (student_id, course_id) 
        REFERENCES Enrollment(student_id, course_id)
);

This pattern maintains current values for fast querying while preserving full history for auditing.

Based on years of database design experience, here are best practices for handling relationship attributes effectively:

Relationship attributes capture data that describes the association between entities rather than the entities themselves. They are essential for accurate modeling of many real-world relationships, especially M:N relationships where attribute values vary by each entity pair.

Module Complete:

With relationship attributes understood, you've completed the study of Relationships in ER Modeling. You now understand:

• What relationships are and why they matter
• How relationship sets collect instances
• How relationship types define structure
• How degree affects complexity (unary through n-ary)
• How attributes enrich relationships with descriptive data

This comprehensive understanding of relationships prepares you to model complex domains accurately and implement them effectively in database systems.