Relationships

A relationship describes how rows in one table relate to rows in another.

Concept

A relationship describes how rows in one table relate to rows in another. The cardinality of a relationship is the number of rows on each side that can be associated.

One-to-One (1:1) One row in A relates to at most one row in B

FK in one table with a UNIQUE constraint (or shared PK) One-to-Many (1:N)

One row in A relates to many rows in B; each B row relates to one A row FK in the "many" table Many-to-Many (M:N)

Rows in A relate to many rows in B and vice versa Junction (bridge/associative) table with two FKs

Why It Matters

Getting relationships right is the foundation of schema design. Wrong cardinality leads to duplicated data, impossible constraints, or the inability to represent real-world facts.

Syntax

-- 1:N — FK on the many side
child.parent_id REFERENCES parent(parent_id)

-- 1:1 — FK + UNIQUE (or FK that is also the PK)
child.parent_id REFERENCES parent(parent_id) UNIQUE

-- M:N — junction table
junction(a_id REFERENCES a, b_id REFERENCES b, PRIMARY KEY (a_id, b_id))

Example

One customer places many orders; each order belongs to exactly one customer.

The FK always goes on the "many" side. Putting order_id in customers would allow only one order per customer.

More 1:N examples: department → employees, vehicle → DTC records, ECU → CAN messages, author → books (if single-author).

Each employee has at most one passport record; each passport belongs to exactly one employee.

Why split into two tables instead of adding columns?

Optional data that most rows lack (avoids many NULL columns).

Sensitive data with separate access control (salary, medical info).

Large or rarely used columns (BLOBs, long text) kept out of the hot table.

Subtypes (a vehicles table plus electric_vehicle_details for EV-only attributes).

A student takes many courses; a course has many students. Relational tables cannot store M:N directly, so a junction table holds one row per pair.

The junction table often carries relationship attributes (enrollment date, grade, quantity, role). Example: order_items is the junction between orders and products with quantity and unit_price.

24.4 Self-referencing (recursive) relationships A table related to itself: employee → manager (1:N within employees), category → parent category, ECU → gateway ECU.

Query with a self join or recursive CTE (Module 16).

24.5 Optional vs mandatory participation Every order must have a customer orders.customer_id NOT NULL An employee may have no department employees.dept_id NULL

Every customer must have at least one address Cannot be enforced by FK alone; use application logic, deferred constraints, or triggers

Customers
   |
   | 1
   |
   | N
Orders

CREATE TABLE customers (customer_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),   -- NOT NULL = mandatory relationship
    order_date   DATE NOT NULL
);
CREATE INDEX ix_orders_customer_id ON orders(customer_id);

Employees
   |
   | 1
   |
   | 0..1
Employee_Passports

-- Option A: FK with UNIQUE
CREATE TABLE employee_passports (
    passport_id  INT PRIMARY KEY,
    emp_id       INT NOT NULL UNIQUE REFERENCES employees(emp_id),
    passport_no  VARCHAR(20) NOT NULL UNIQUE,
    expiry_date  DATE NOT NULL
);

-- Option B: shared primary key (PK is also the FK)
CREATE TABLE employee_passports (
    emp_id       INT PRIMARY KEY REFERENCES employees(emp_id),
    passport_no  VARCHAR(20) NOT NULL UNIQUE,
    expiry_date  DATE NOT NULL
);

Students                Enrollments               Courses
   |                  (junction table)               |
   | 1              N  +-----------+  N            1 |
   +-------------------| student_id|-----------------+
                       | course_id |
                       | grade     |
                       +-----------+

CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE courses  (course_id  INT PRIMARY KEY, title VARCHAR(100) NOT NULL);

CREATE TABLE enrollments (
    student_id   INT NOT NULL REFERENCES students(student_id) ON DELETE CASCADE,
    course_id    INT NOT NULL REFERENCES courses(course_id),
    enrolled_on  DATE NOT NULL DEFAULT CURRENT_DATE,
    grade        CHAR(2),
    PRIMARY KEY (student_id, course_id)            -- prevents duplicate enrollment
);
CREATE INDEX ix_enrollments_course_id ON enrollments(course_id);   -- for "students in a course" queries

-- Courses taken by student 1
SELECT c.title
FROM enrollments e
JOIN courses c ON c.course_id = e.course_id
WHERE e.student_id = 1;

-- Number of students per course, including empty courses
SELECT c.title, COUNT(e.student_id) AS n_students
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title;

CREATE TABLE categories (
    category_id         INT PRIMARY KEY,
    name                VARCHAR(50) NOT NULL,
    parent_category_id  INT NULL REFERENCES categories(category_id)
);

Expected Result

Order lines per order in the sample database (1:N between orders and order_items):

SELECT o.order_id, COUNT(oi.product_id) AS n_lines
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

Common Mistakes

Putting the FK on the "one" side of a 1:N relationship.

Implementing M:N with comma-separated IDs in a column.

Forgetting the composite PK/UNIQUE on junction tables (duplicate pairs).

Implementing 1:1 without UNIQUE on the FK (it silently becomes 1:N).

Not indexing the second FK column in a junction table.

Best Practices

Draw the relationship (ER diagram) before creating tables.

Always enforce relationships with foreign keys.

Name junction tables meaningfully (enrollments, order_items, ecu_software_installs) rather than student_course.

Decide NULL/NOT NULL on FKs from the business rule (optional vs mandatory).

Interview Relevance

High: "How do you implement a many-to-many relationship?", "Where does the foreign key go in a one-to-many?", "How do you model one-to-one?", "What is a junction table?".

Practice Questions

Model authors and books where a book can have multiple authors.

Model vehicles and owners where a vehicle has one current owner but an ownership history must be kept.

Model a 1:1 relationship between users and user_profiles.

Write a query listing books with a comma-separated author list.

Explain why a junction table usually has a composite primary key.