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

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

Понимаете ли вы связующую таблицу и умеете ли работать с ней через ORM без лишних запросов. Для Python-разработчика вопрос почти всегда продолжается так: как это описать в Django или SQLAlchemy и как добавить к связи поле, например порядок авторов.

Как отвечать

В базе три таблицы: book, author и связующая с парой внешних ключей и уникальностью пары, удобнее всего составным первичным ключом (book_id, author_id). Если связь несёт данные (порядок на обложке, роль), они хранятся в этой же таблице.

В Django:

• ManyToManyField: authors = models.ManyToManyField(Author, related_name="books") сам создаёт связующую таблицу с уникальной парой и индексами на обе колонки;
• through: для полей связи описываете модель BookAuthor явно и указываете through=BookAuthor. Добавлять связь можно через book.authors.add(author, through_defaults={"position": 1});
• загрузка: Book.objects.prefetch_related("authors") делает второй запрос с IN вместо запроса на каждую книгу. select_related для многие-ко-многим не работает, он умеет только внешний ключ и один-к-одному.

В SQLAlchemy 2.0:

• таблица связи: Table("book_author", Base.metadata, ...) с двумя ForeignKey и primary_key=True на обеих колонках;
• связь: relationship(secondary=book_author, back_populates=...) на обеих моделях;
• поля связи: паттерн association object, связь становится отдельным классом;
• загрузка: selectinload(Book.authors), иначе ленивая загрузка устроит N+1, а в асинхронном режиме просто упадёт.

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

Хранят авторов строкой через запятую или в JSONField. Искать книги автора, переименовывать автора и проверять ссылочную целостность становится мучением.

Путают select_related и prefetch_related и не смотрят, сколько запросов ушло. Django Debug Toolbar или assertNumQueries в тестах показывают это сразу.

В SQLAlchemy делают составной ключ (book_id, author_id) и считают, что индекс есть на обе колонки. Поиск по одному author_id этот ключ не ускоряет, нужен отдельный индекс.