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

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

Знаете ли вы связующую таблицу и умеете ли довести её до рабочего состояния: ключи, индексы, удаление, выборка без N+1. Для Go-разработчика следом почти всегда спрашивают, как вы разложите результат по структурам и в какой транзакции добавите книгу вместе с авторами.

Как отвечать

Схема из трёх таблиц:

• authors и books: обычные сущности со своими id;
• book_authors: book_id и author_id со ссылками на обе таблицы и первичным ключом (book_id, author_id). Ключ заодно запрещает дублировать одну и ту же пару;
• второй индекс: PostgreSQL не создаёт индексы на внешние ключи сам. Составной ключ обслуживает поиск по book_id, а для «всех книг автора» нужен индекс (author_id, book_id);
• атрибуты связи: порядок авторов на обложке или роль (автор, редактор, переводчик) живут как раз в связующей таблице;
• удаление: ON DELETE CASCADE на ссылках из book_authors, чтобы удаление книги не оставляло висящих строк.

В Go книга с авторами добавляется одной транзакцией: pool.Begin(ctx), отложенный tx.Rollback(ctx), вставка книги с RETURNING id, вставка связей, tx.Commit(ctx). Повторную связь удобно глотать через ON CONFLICT DO NOTHING.

При чтении не надо ходить за авторами в цикле по книгам. Два нормальных пути: один запрос с JOIN и сборка в map[int64]*Book или второй запрос WHERE book_id = ANY($1) со срезом идентификаторов, pgx передаёт срез как массив PostgreSQL. Можно собрать авторов прямо в SQL через json_agg. Если в проекте GORM, связь описывается тегом gorm:"many2many:book_authors;", а подгрузка делается через Preload("Authors").

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

Хранят author_ids массивом в строке книги. Внешний ключ на элементы массива не повесить, и удалённый автор так и останется в списке.

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

Делают запрос авторов на каждую книгу. На странице из 50 книг это 51 запрос вместо двух.