M:N через junction table: обязательный паттерн

В прошлом уроке мы установили факт: реляционная модель не умеет связь M:N напрямую, она реализуется через третью таблицу. Этот урок — целиком про эту третью таблицу. Она называется junction table (а также associative table, bridge table, link table, cross-reference table — все термины об одном). Junction table — не «костыль» и не временное решение: это фундаментальный, обязательный паттерн, который вы будете применять в каждой почти схеме.

Разберём, как она устроена, как её правильно ключевать, и почему очень часто junction table перестаёт быть «просто связкой» и превращается в полноценную сущность.

Анатомия junction table

Junction table — это таблица, единственная роль которой в простейшем случае — хранить факты связи между строками двух других таблиц. Каждая её строка означает «вот этот конкретный A связан с этим конкретным B».

Минимальная junction table состоит из двух столбцов, и каждый из них — foreign key на одну из родительских таблиц.

Возьмём классику — студенты и курсы (связь M:N: студент на многих курсах, на курсе много студентов):

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name       TEXT NOT NULL
);

CREATE TABLE courses (
    course_id  INTEGER PRIMARY KEY,
    title      TEXT NOT NULL
);

-- junction table:
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    course_id  INTEGER NOT NULL REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)   -- составной ключ из двух FK
);

Что мы видим в enrollments:

• Два столбца student_id и course_id, каждый — foreign key.
• Составной PRIMARY KEY (student_id, course_id) — это и есть ключ, и он одновременно сделан из тех же двух foreign key.

Данные выглядят так:

students:            courses:              enrollments:
student_id | name    course_id | title     student_id | course_id
-----------+-----    ----------+--------    -----------+----------
    1      | Ann        10     | Math           1      |    10
    2      | Bob        11     | Physics         1      |    11
    3      | Cy         12     | History         2      |    10
                                               3      |    12

Читаем enrollments: Ann записана на Math и Physics,
Bob на Math, Cy на History. Связь M:N реализована.

Почему ключ junction-таблицы — составной из двух FK

Это центральная деталь паттерна, разберём её внимательно. Почему primary key — именно пара (student_id, course_id)?

Вспомним урок про composite keys из модуля про ключи. В enrollments:

• student_id сам по себе не уникален — Ann записана на два курса, значит student_id = 1 встречается дважды.
• course_id сам по себе не уникален — на Math записаны двое, значит course_id = 10 встречается дважды.
• Пара (student_id, course_id) — уникальна: один студент не может быть записан на один курс дважды.

Значит, естественный primary key — составной (student_id, course_id). И у этого ключа есть важная функция: он запрещает дубликаты связи. Без него можно было бы вставить две одинаковые строки (1, 10) — «Ann записана на Math» дважды. Это бессмыслица: связь либо есть, либо нет. Составной primary key делает дубликат связи невозможным на уровне СУБД.

INSERT INTO enrollments VALUES (1, 10);
INSERT INTO enrollments VALUES (1, 10);
-- ERROR: duplicate key value violates unique constraint
-- пара (1, 10) уже существует — связь не задваивается

Это и есть причина, по которой составной ключ из двух FK — не случайность, а правильный дизайн junction-таблицы.

Запросы через junction table

Чтобы получить осмысленный ответ, junction table соединяют JOIN-ами с обеими родительскими таблицами. Это стандартная операция, которую вы будете писать постоянно.

-- На какие курсы записана Ann (имена курсов):
SELECT c.title
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c     ON e.course_id  = c.course_id
WHERE s.name = 'Ann';
-- title
-- -------
-- Math
-- Physics

-- Сколько студентов на каждом курсе:
SELECT c.title, count(*) AS students_count
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.title;
-- title   | students_count
-- --------+---------------
-- Math    |       2
-- Physics |       1
-- History |       1

Запрос «через M:N» — это всегда два JOIN: родитель -> junction -> родитель. Junction table стоит посередине цепочки. Это базовый навык — потренируйте его на задании в конце урока.

Junction table как полноценная сущность

Теперь — самое интересное и часто упускаемое. Junction table начинается как «просто связка двух FK». Но очень часто у самой связи есть собственные атрибуты — данные, которые описывают не A, не B, а именно факт их связи. И тогда junction table перестаёт быть технической связкой и становится полноценной сущностью (её называют associative entity).

Вернёмся к enrollments. У записи студента на курс есть собственные данные:

• Дата записи — когда студент записался. Это не атрибут студента и не атрибут курса — это атрибут именно записи.
• Оценка — какую оценку студент получил на этом курсе. Тоже принадлежит связи: у студента нет «оценки вообще», у курса нет «оценки вообще» — оценка существует только в паре «студент + курс».
• Статус — активна запись, завершена, отчислен.

CREATE TABLE enrollments (
    student_id  INTEGER NOT NULL REFERENCES students(student_id),
    course_id   INTEGER NOT NULL REFERENCES courses(course_id),
    enrolled_on DATE NOT NULL,                          -- атрибут связи
    grade       INTEGER CHECK (grade BETWEEN 1 AND 5),  -- атрибут связи
    status      TEXT NOT NULL DEFAULT 'active',         -- атрибут связи
    PRIMARY KEY (student_id, course_id)
);

enrolled_on, grade, status не относятся ни к студенту, ни к курсу — они описывают сам факт «студент X на курсе Y». Это и делает enrollments полноценной сущностью «запись на курс», а не просто технической таблицей-связкой.

Отличный практический пример, который вы уже видели — order_items из модуля про ключи. Это junction table между orders и products (заказ содержит много товаров, товар в многих заказах — M:N). Но у строки order_items есть quantity и price — количество и цена именно в этом заказе. Это атрибуты связи. order_items — полноценная associative entity «позиция заказа».

Признак, что атрибут принадлежит связи

Junior-инженеру нужен критерий: куда положить атрибут — в A, в B или в junction table? Задайте вопрос: атрибут имеет смысл без обеих сторон связи?

• grade (оценка) имеет смысл без курса? Нет — «оценка студента» ни о чём, нужен курс. Без студента? Нет. Значит, grade принадлежит связи -> в junction table.
• student.name (имя студента) имеет смысл без курса? Да — у студента есть имя независимо от курсов. Значит, в таблицу students.
• course.title (название курса) имеет смысл без студента? Да. Значит, в таблицу courses.

Правило: атрибут, который существует только когда есть обе стороны связи, кладётся в junction table. Это и превращает её в associative entity.

Попробуй сам

Спроектируйте M:N-связь «актёры и фильмы» полностью.

1. Создайте таблицы actors(actor_id, name) и movies(movie_id, title, release_year).
2. Создайте junction table roles, реализующую M:N. Объявите составной primary key из двух foreign key. Вставьте данные так, чтобы один актёр снялся в нескольких фильмах, а в одном фильме было несколько актёров.
3. Подумайте над атрибутами связи. У роли актёра в фильме есть собственные данные: имя персонажа (character_name), это главная роль или эпизодическая (role_type). Добавьте их в roles — и объясните, почему character_name не может лежать ни в actors, ни в movies.
4. Напишите два запроса через junction table: (а) все фильмы конкретного актёра; (б) все актёры конкретного фильма с именами их персонажей. Каждый запрос — это два JOIN.
5. Проверьте, что составной primary key не даёт вставить одну и ту же пару «актёр + фильм» дважды. Подумайте: а корректно ли это ограничение, если актёр может играть в одном фильме две разные роли? Если нет — как изменить ключ?