Как реализовать связь многие ко многим в базе данных

Что проверяют

Для аналитика важна не столько DDL, сколько последствия: соединение через связь многие ко многим размножает строки и ломает суммы в отчёте. Вопрос в том, видите ли вы этот эффект заранее.

Как отвечать

Модель. Связь M:N между двумя таблицами напрямую не выражается, между ними ставится связующая (bridge) таблица с внешними ключами на обе стороны и составным первичным ключом по паре. Составной ключ и есть защита от дублей.

create table student_courses (
    student_id int not null references students(student_id),
    course_id  int not null references courses(course_id),
    primary key (student_id, course_id)
);

Главный практический эффект: fan-out. После join через bridge строка студента, записанного на три курса, появится в результате трижды. Любая агрегация метрики из таблицы студентов посчитает её три раза.

-- неверно: сумма задваивается по числу курсов
select sum(s.paid)
from students s
join student_courses sc on sc.student_id = s.student_id;

-- корректно: считать метрику на её собственном зерне
select sum(paid) from students where student_id in (select student_id from student_courses);

Практическое правило: каждую метрику агрегировать на своём уровне детализации, а потом соединять уже агрегаты, а не сырые таблицы. Для счётчиков count(distinct student_id) вместо count(*). В BI-инструментах та же проблема выглядит как неоднозначный путь связи между таблицами и решается тем же bridge с явным направлением фильтрации.

Историчность. Если членство меняется во времени (студент отчислен с курса, сотрудник сменил отдел), в bridge добавляют valid_from и valid_to, а выборки делают на дату. Без этого отчёт за прошлый квартал каждый раз пересчитывается по сегодняшнему составу, и цифры «едут» задним числом.

Атрибуты связи. Дата записи, оценка, доля участия живут в bridge, это её нормальное назначение, а не признак плохой модели. Веса нужны, когда факт надо честно распределить между сторонами связи, например бюджет проекта по участникам: тогда сумма по частям совпадёт с исходной.

Производительность. Индексы по обоим направлениям: составной первичный ключ обслуживает только одно, для запроса «кто на курсе» нужен индекс, где course_id идёт первым.

Где спотыкаются

Описывают три таблицы и считают вопрос закрытым, а на follow-up «почему выручка в отчёте выросла вдвое после того, как студентам добавили курсы» не связывают это с размножением строк на соединении.