Many to Many Relationships and Junction Tables

Concept

A many to many relationship cannot be represented with a foreign key on either side. One employee works on many projects, and one project has many employees - neither table can hold a single value pointing at the other.

The solution is a third table, called a junction (or bridge, link, associative) table, holding one row per pair. It converts one many to many into two one to many relationships.

Syntax

CREATE TABLE assignments (
    employee_id INT NOT NULL,
    project_id  INT NOT NULL,
    hours       DECIMAL(6,1) NOT NULL DEFAULT 0,   -- relationship attribute
    assigned_on DATE NULL,
    PRIMARY KEY (employee_id, project_id),
    CONSTRAINT fk_asg_emp  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_asg_proj FOREIGN KEY (project_id)  REFERENCES projects(id)  ON DELETE CASCADE
);

CREATE INDEX idx_assignments_project ON assignments (project_id);

Explanation

Three design decisions matter here:

• The composite primary key (employee_id, project_id) is the whole point: it makes the pair unique, so nobody can be assigned to the same project twice.
• Attributes of the relationship - hours, assigned_on - belong in the junction table, not in either parent. Hours worked is a property of the pairing, not of the employee or of the project.
• The second index. The primary key indexes (employee_id, project_id), which serves lookups by employee. Finding everyone on a project needs a separate index leading with project_id.

Querying across a junction table

-- All three tables joined: one row per employee per project
SELECT e.first_name,
       p.name  AS project,
       a.hours
FROM   employees e
JOIN   assignments a ON a.employee_id = e.id
JOIN   projects    p ON p.id = a.project_id
ORDER BY e.first_name, p.name;

-- How many people on each project, including projects with nobody
SELECT p.name, COUNT(a.employee_id) AS people
FROM   projects p
LEFT JOIN assignments a ON a.project_id = p.id
GROUP BY p.name
ORDER BY people DESC;

-- Total hours per employee across all projects
SELECT e.first_name, COALESCE(SUM(a.hours), 0) AS total_hours
FROM   employees e
LEFT JOIN assignments a ON a.employee_id = e.id
GROUP BY e.first_name
ORDER BY total_hours DESC;

-- Employees on no project at all
SELECT e.first_name
FROM   employees e
WHERE  NOT EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id);

Composite key or surrogate id?

Prefer the composite key. Add a surrogate id only if another table needs to reference the pairing itself - and if you do, keep the UNIQUE (a_id, b_id), or duplicate pairs become possible.

Important rules

• A junction table needs two foreign keys, one to each parent.
• The pair must be unique, by composite primary key or by a UNIQUE constraint.
• Attributes describing the pairing live in the junction table.
• Index both directions: the composite key covers one, a second index covers the other.
• Joining through a junction table multiplies rows - be deliberate about the grain before aggregating.

Common mistakes

• Storing project_ids = '100,101,102' as a string. It cannot be joined, indexed, constrained or counted.
• Adding columns project1_id, project2_id, project3_id - a repeating group, and always the wrong number.
• Omitting the composite key, allowing the same pair many times.
• Indexing only the leading column and leaving reverse lookups to a full scan.
• Summing a parent column across a junction join and inflating the total.

Best practices

• Name junction tables after the relationship where one exists (assignments, enrolments), or after both parents (note_tags).
• Use the composite primary key by default.
• Create the reverse index explicitly.
• Use ON DELETE CASCADE on both foreign keys - a pairing has no meaning once either side is gone.

Practice

1. Design a junction table linking students and courses, carrying a grade and an enrolment date.
2. Write the query that lists every project with the names of everyone assigned to it.
3. Explain why SELECT SUM(p.budget) FROM projects p JOIN assignments a ON a.project_id = p.id is wrong.