Реляционная модель и нормализация без лишних копий

Библиотекарь исправил имя автора Ирины. В CSV оно повторяется для каждого экземпляра и каждой выдачи, поэтому после исправления часть отчётов показывает старое имя. Это аномалия обновления: один предметный факт представлен несколькими независимо изменяемыми копиями. Нормализация помогает выбрать, какие факты хранить отдельно и как соединять их обратно.

Сущность, связь и зависимость

Сущность — объект, о котором ведём сведения: участник, издание, физический экземпляр. Выдача — отдельный факт связи с собственными датами. Простая таблица «название, читатель, дата» смешивает описания объектов и историю действий. Сначала определите ключ строки и спросите, какие остальные поля однозначно определяются этим ключом.

В нашей модели copy_id определяет конкретный book_id и inventory_code. book_id определяет title и author в текущей упрощённой модели. loan_id определяет участника, экземпляр и даты выдачи. Если title повторяется в каждой строке loans как текущий заголовок, его обновление требует менять историю или согласовывать копии. Связь через copies.books позволяет читать актуальное название из одного источника.

SELECT l.id,c.inventory_code,b.title
FROM club.loans l
JOIN club.copies c ON c.id=l.copy_id
JOIN club.books b ON b.id=c.book_id
ORDER BY l.id;

Получаем три строки с теми же названиями, что в предыдущих уроках. Здесь JOIN восстанавливает удобное представление из отдельных фактов. Дополнительные таблицы не являются самоцелью: они должны уменьшать противоречие и сохранять нужные запросы. Цена соединений и индексов измеряется после проверки корректности.

Что объясняют нормальные формы

Первая нормальная форма в базовом реляционном понимании предполагает значения полей выбранного домена, а не строку с непонятным списком через запятую, который приложение вынуждено разбирать как несколько независимых фактов. Однако массивы и JSON в PostgreSQL существуют: вопрос состоит в смысле объекта и операциях, а не в запрете любого сложного типа.

Если ключ составной, поле не должно зависеть только от его части, когда оно описывает отдельную сущность. Для связи «участник подписан на книгу» ключом может быть пара member_id/book_id, но имя участника зависит только от member_id. Его копия в каждой подписке создаёт повторение. Следующий шаг нормализации убирает зависимости через другие неключевые факты: контакт автора, определяемый автором, не надо считать независимым полем каждой выдачи.

На начальном этапе полезнее предъявить конкретную зависимость и аномалию, чем перечислить все формы по памяти. Вставка новой книги не должна требовать существующей выдачи. Удаление последней выдачи не должно удалять описание книги. Изменение контакта автора не должно оставлять несколько несовместимых значений.

Упрощения dataset имеют границы

В books.author сейчас строка имени. Это допустимая учебная модель одного автора, но не полный каталог литературы. У произведения могут быть несколько авторов, псевдонимы, разные издания и переводы. Имя человека не обязательно уникально. Когда появился вопрос «все книги конкретного автора», устойчивый author_id и таблица связи становятся полезнее сравнения текста.

Не меняя golden seed, посмотрим будущую many-to-many модель на временных таблицах одной сессии. Опыт откатывается полностью:

BEGIN;
CREATE TEMP TABLE author_demo(id bigint PRIMARY KEY,name text NOT NULL);
CREATE TEMP TABLE book_demo(id bigint PRIMARY KEY);
INSERT INTO book_demo SELECT id FROM club.books;
CREATE TEMP TABLE book_author_demo(
  book_id bigint NOT NULL REFERENCES book_demo(id),
  author_id bigint NOT NULL REFERENCES author_demo(id),
  PRIMARY KEY(book_id,author_id)
);
INSERT INTO author_demo VALUES(1,'Ирина'),(2,'Олег');
INSERT INTO book_author_demo VALUES(10,1),(11,2),(12,1);
SELECT a.id,a.name,COUNT(ba.book_id) AS books
FROM author_demo a
LEFT JOIN book_author_demo ba ON ba.author_id=a.id
GROUP BY a.id,a.name ORDER BY a.id;
ROLLBACK;

Ожидаем 1,Ирина,2 и 2,Олег,1. Один автор связывается с несколькими книгами; одну книгу можно связать с несколькими авторами новыми парами. Первичный ключ связи запрещает повтор одной пары. Внешний ключ запрещает неизвестного автора. Переименование сохраняет ID, поэтому связи не переписываются по новому тексту.

Исторический снимок и денормализация

Иногда копия нужна по смыслу. Если печатная квитанция должна сохранить название на момент выдачи, отдельное поле title_at_issue содержит исторический факт, а не неудачный дубль текущего title. Правило обновления и источник истины отличаются. Нельзя автоматически синхронизировать историческое поле при исправлении сегодняшнего каталога, иначе прошлый документ изменится.

Денормализация ради скорости тоже возможна: например, таблица отчёта хранит готовое число активных выдач. Но это производное состояние. Нужно определить, кто обновляет его, как переживает повтор и отказ, как проверяются удаления и что значит отставание. «Один JOIN медленный» не доказывает, что все данные надо копировать; сначала изучите план и нужные индексы.

При двух сохранённых счётчиках без общего протокола одна транзакция может обновить исходные loans, а другую работу не завершить. В результате быстрый отчёт станет неверным. Он не может служить разрешением на выдачу последней физической копии, если его свежесть не соответствует этому правилу. Авторитетный инвариант остаётся у выбранного пути изменений.

Самостоятельная лаборатория

Отведите 30–40 минут. Предложите схему авторов и изданий для двух требований: у книги два автора; одна книга переиздана с другим ISBN. Выпишите ключи и связи. Затем сравните нынешнее название и название на старой квитанции: какие изменения должны распространяться, какие должны сохранять историю?

Критерии: каждое поле относится к названному факту; many-to-many имеет отдельную связь; текст имени не заменяет ID человека; удаление выдачи не уничтожает описание книги; выбранная денормализация получает правило обновления и сверки. Не усложняйте схему сущностью, для которой пока нет ни отдельного правила, ни запроса.

Разбор

Авторы получают свои ID, участие автора в книге задаётся парами. Издание требует отдельного объекта, если у него собственные ISBN, год и физические экземпляры. Историческая квитанция сохраняет прежний текст по договору; текущий каталог обновляется отдельно. Можно начать с простой схемы и расширить её при новом требовании, но нельзя выдавать случайный один автор в seed за универсальное ограничение предметной области.

Первичные источники

PostgreSQL: Foreign Keys, CMU 15-445: Relational Model, Berkeley: Relational Algebra. Формальные темы используются как ориентир; библиотечные модели и задачи оригинальные.