Таблицы для авторов и книг (многие-ко-многим)

25. Спроектировать таблицы для авторов и книг со связью многие-ко-многим

Условие задачи:
Есть две сущности:

Authors — авторы;
Books — книги.

Один автор может написать несколько книг, и у одной книги может быть несколько авторов.

Необходимо спроектировать структуру таблиц для связи many-to-many.

Спойлеры к решению

Подсказки

💡 Связь many-to-many реализуется через отдельную таблицу связей.
💡 В ней достаточно хранить author_id и book_id.
💡 Пара (author_id, book_id) должна быть уникальной.
💡 Оба поля должны быть внешними ключами на основные таблицы.

Решение

Таблица авторов:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

Таблица книг:

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

Таблица связи:

CREATE TABLE author_book (
    author_id INT NOT NULL,
    book_id INT NOT NULL,

    PRIMARY KEY (author_id, book_id),

    FOREIGN KEY (author_id)
        REFERENCES authors(id),

    FOREIGN KEY (book_id)
        REFERENCES books(id)
);

Связь выглядит так:

authors        author_book        books

id  <-------- author_id
                 book_id -------> id

Каждая строка в author_book означает связь одного автора с одной книгой.

Составной первичный ключ:

PRIMARY KEY (author_id, book_id)

не позволяет создать одну и ту же связь между автором и книгой несколько раз.