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

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

Понимаете ли вы, почему «многие ко многим» всегда превращается в третью таблицу, и умеете ли описать её в ORM, с которым работаете: SQLAlchemy или Django. Следующим вопросом обычно просят вытащить студентов со списком их курсов без проблемы N+1.

Как отвечать

Схема: таблицы students, courses и связующая student_courses с двумя внешними ключами. На ней стоит проговорить детали:

• первичный ключ на паре (student_id, course_id): отсекает повторную запись того же студента на тот же курс;
• индекс по course_id: составной ключ работает слева направо и помогает в первую очередь запросам от студента. Запрос «кто записан на курс» без отдельного индекса обычно уходит в полный скан;
• ON DELETE CASCADE на обоих внешних ключах, чтобы удаление курса не оставляло висящих строк;
• SQLAlchemy: связующая таблица объявляется как Table, а в моделях пишется relationship(secondary=student_courses, back_populates=...). Если у связи есть свои поля (дата записи, оценка), нужен паттерн association object: отдельная модель Enrollment и две связи «один ко многим»;
• Django: ManyToManyField сам создаёт связующую таблицу с уникальностью пары. Для дополнительных полей указывается through=Enrollment, и тогда вы работаете с промежуточной моделью напрямую.

Про чтение: обращение к student.courses в цикле даёт отдельный запрос на каждого студента. В SQLAlchemy это лечится selectinload(Student.courses), в Django prefetch_related("courses"). Оба делают один дополнительный запрос с IN на всю пачку.

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

Используют select_related для связи «многие ко многим» в Django. Он работает только с ForeignKey и OneToOneField, здесь нужен prefetch_related.

Добавляют в связующую таблицу суррогатный id и не ставят уникальность на пару. Повторный запрос с фронтенда создаёт дубль, и счётчики начинают врать.

Пытаются положить дату записи в таблицу, объявленную через secondary. ORM про эту колонку ничего не знает, заполнять её приходится сырым SQL, хотя для этого и существует association object.