Базовая нормализованная реляционная схема (основные таблицы и ключи):
- Books (works)
- book_id PK
- title
- description
- Editions
- edition_id PK
- book_id FK → Books.book_id
- isbn UNIQUE
- publisher_id FK → Publishers.publisher_id
- pub_year
- pages
- language
- Authors
- author_id PK
- first_name
- last_name
- birth_year
- EditionAuthors (многие-ко-многим)
- edition_id FK → Editions.edition_id
- author_id FK → Authors.author_id
- position (порядок авторов)
- PK: (edition_id, author_id)
- Publishers
- publisher_id PK
- name
- address
- Genres
- genre_id PK
- name
- EditionGenres (многие-ко-многим)
- edition_id FK → Editions.edition_id
- genre_id FK → Genres.genre_id
- PK: (edition_id, genre_id)
- Copies (физические экземпляры)
- copy_id PK
- edition_id FK → Editions.edition_id
- barcode UNIQUE
- location (полка/филиал)
- status ENUM('available','on_loan','reserved','lost')
- acquisition_date
- Members
- member_id PK
- full_name
- email UNIQUE
- phone
- join_date
- status
- Loans
- loan_id PK
- copy_id FK → Copies.copy_id
- member_id FK → Members.member_id
- loan_date
- due_date
- return_date NULLABLE
- fine_amount
- UNIQUE(copy_id, return_date IS NULL) — обеспечивает, что копия не выдана одновременно нескольким
- Reservations
- reservation_id PK
- edition_id FK → Editions.edition_id
- member_id FK → Members.member_id
- reservation_date
- expires_at
- status
Краткое обоснование нормальной формы:
- Таблицы разложены так, чтобы устранить повторяющиеся группы и множественные значения в полях → соблюдается $\text{1NF}$.
- Все неполные зависимостя по составным ключам устранены (вjunction-таблицах вся зависимость от полного ключа) → соответствует $\text{2NF}$.
- Транситивные зависимости минимизированы: атрибуты принадлежат только одной сущности (например, данные издателя в таблице Publishers, а не в Editions) → соответствует $\text{3NF}$. Многие таблицы также соответствуют $\text{BCNF}$ (каждый детерминист — суперключ), за исключением практических компромиссов при junction‑таблицах.
Примеры функциональных зависимостей, иллюстрирующих нормализацию:
- $edition\_id\to isbn,publisher\_id,pub\_year$ - $(edition\_id,author\_id)\to position$ - $copy\_id\to edition\_id,status,location$
Возможные денормализации для оптимизации чтения и компромиссы:
1) Быстрый показатель доступности книги
- Добавить в Editions/Books поля: total_copies, available_copies.
- Вычисление: $\text{available\_copies}=\text{total\_copies}-\text{on\_loan\_count}$.
- Плюсы: быстрый отклик на запросы наличия.
- Минусы: риск рассинхронизации; требуется транзакционная актуализация или фоновые задания; усложняются операции записи.
2) Кеширование авторов/строк для отображения
- Добавить в Editions поле authors_text (например, "Иванов И., Петров П.").
- Плюсы: быстрее рендерить списки/поиск.
- Минусы: дублирование данных при изменении автора; нужно обновлять при изменениях в Authors/EditionAuthors.
3) Хранение текущего статуса/владельца в Copies
- Добавить поле current_member_id в Copies (NULL если не выдано).
- Плюсы: быстро узнать, кто держит копию без JOIN на Loans.
- Минусы: дублирование информации, необходимость атомарного обновления при выдаче/возврате; сложнее поддерживать историю.
4) Материализованные представления (MV) для отчетов и поиска
- MV с join Editions+Authors+Publishers для полнотекстового поиска/каталога; MV с overdue per member.
- Плюсы: быстрые чтения/фильтрация.
- Минусы: накладные расходы на обновление MV; возможна задержка актуальности.
5) Предагрегации (счётчики резервов, просмотров)
- Хранимые счётчики в Editions: reserve_count, view_count.
- Минусы: race conditions при высоком трафике; нужны атомарные increment или внешняя очередь.
Общие компромиссы и рекомендации:
- Денормализация ускоряет чтение, но усложняет записи: нужны транзакции, триггеры или фоновые задачи для консистентности.
- Для OLTP‑системы (много записей/много транзакций) предпочтительна нормализованная модель; для heavy-read / аналитических сценариев используйте MV, репликацию/кеши или специализированные поисковые движки (Elasticsearch).
- Индексы: индексировать Copies.barcode, Copies.status, Loans.member_id, Loans.due_date, Editions.isbn, EditionAuthors.author_id — это уменьшит потребность в агрессивной денормализации.
- При критической необходимости консистентности (штрафы, выдачи) выполняйте изменения в одной транзакции, либо используйте механизмы оптимистической блокировки.
Если нужно, могу дать примеры DDL для выбранных таблиц или варианты схемы «упрощённо» (без Works/editions) для небольших библиотек.