Как организовать связи между таблицами авторов, книг и жанров?

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

Для аналитика важно не столько знание CREATE TABLE, сколько умение уточнить требования до проектирования: может ли у книги быть несколько авторов и несколько жанров. Следом обычно просят ER-диаграмму и запрос вроде «все жанры автора».

Как отвечать

Начните с вопросов к заказчику, потому что от ответов зависит кардинальность. В жизни у книги бывают соавторы и несколько жанров, поэтому обе связи многие-ко-многим:

• сущности: authors (id, full_name), genres (id, name) с уникальным name, books (id, title, published_year);
• связующие таблицы: book_authors (book_id, author_id) и book_genres (book_id, genre_id). Первичный ключ составной из двух внешних ключей, он же запрещает дубли пар;
• атрибуты связи: порядок авторов на обложке или роль (автор, переводчик, редактор) хранятся полями position и role в book_authors, а не в самих сущностях;
• ссылочная целостность: внешние ключи с осознанным ON DELETE. Удаление книги каскадно убирает её связи, а удаление жанра, к которому привязаны книги, лучше запретить через RESTRICT;
• индексы: составной ключ (book_id, author_id) обслуживает поиск авторов книги, для поиска книг автора нужен отдельный индекс по author_id.

Если заказчик подтверждает, что у книги ровно один автор и один жанр, хватит внешних ключей author_id и genre_id прямо в books, это связь один-ко-многим. Проговорите цену: схема проще, но первый же соавтор потребует миграции.

Схему проверьте запросом: книги с авторами через два JOIN, жанры одной строкой через string_agg в PostgreSQL или GROUP_CONCAT в MySQL. На ER-диаграмме связующие таблицы рисуются явно, с кардинальностью на обоих концах.

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

Кладут author_id и genre_id прямо в books, не спросив про соавторов. Интервьюер приводит в пример книгу двух авторов, и схема разваливается.

Хранят жанры строкой через запятую в поле books.genres. Это нарушение первой нормальной формы: внешний ключ не повесить, искать и переименовывать жанр трудно.

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