Таблицы, типы и правила допустимых данных
Клуб приобрёл два одинаковых экземпляра «Практики Go». Анна взяла один, второй остался на полке. Если хранить только название и имя читателя, нельзя понять, какую физическую книгу надо вернуть. База должна различать описание книги, конкретный экземпляр и факт выдачи. Схема задаёт эти различия и ограничения, которые действуют для любого клиента.
Строка представляет один факт
В club.books строка описывает произведение или выбранное издание. В club.copies строка представляет экземпляр с инвентарным кодом. club.members хранит участника, а club.loans — выдачу экземпляра участнику. Поле id служит устойчивым идентификатором. Название книги можно изменить; все связанные выдачи не должны потерять связь из-за исправления опечатки.
В нашем dataset идентификаторы заданы явно, чтобы результаты легко проверялись глазами. В приложении их обычно назначает база или согласованный генератор. PostgreSQL поддерживает identity columns. Автоматическое получение числа не заменяет уникальное ограничение, не обещает отсутствие пропусков и не должно использоваться как номер «сколько записей всего». Позже проект выберет способ назначения отдельно.
SELECT id, title FROM club.books ORDER BY id;
SELECT id, book_id, inventory_code FROM club.copies ORDER BY id;Первый результат: 10 — «Практика Go», 11 — «Запросы SQL», 12 — «Как работают сети». Второй: 100 и 101 ссылаются на book_id=10, 102 на 11, 103 на 12. Вызов ORDER BY задаёт порядок; без него мы не обещаем, в какой последовательности строки покажет сервер.
Тип хранит смысл значения
bigint хранит целое число в определённом диапазоне. text подходит имени и заголовку; произвольный предел varchar не улучшает саму модель. date хранит календарный день. Для момента, который нужно сравнивать между часовыми поясами, обычно рассматривают timestamptz: он представляет момент и отображает его согласно timezone сессии. Это другой смысл, чем дата обязательного возврата книги.
Для точных десятичных значений существует numeric. Денежная сумма в integer минимальных единицах тоже возможна, если единица и допустимый диапазон ясны. Float удобен для приближённых вычислений, но его нельзя бездумно использовать для точного равенства сумм. В этом курсе деньги в библиотечную схему пока не входят: выбор типа обсуждаем как принцип, а не как незаметную новую таблицу.
NULL означает отсутствие значения. У Бориса email неизвестен; returned_on=NULL означает, что возврат ещё не зарегистрирован. Эти два значения имеют разный предметный смысл, хотя представлены одинаковым маркером. Поле due_on обязано иметь дату: его отсутствие не должно молча означать бесконечный срок выдачи.
Ограничения проверяют разные условия
Первичный ключ запрещает повтор одного ID и отсутствие самого ключа. Внешний ключ не позволяет выдаче ссылаться на несуществующего участника или экземпляр. NOT NULL требует значение, CHECK проверяет условие строки, UNIQUE — отсутствие запрещённого повторения. Приложение может заранее показывать удобное сообщение, но база остаётся общей границей для API, импорта и административного клиента.
BEGIN;
INSERT INTO club.loans
(id, member_id, copy_id, borrowed_on, due_on)
VALUES (2000, 3, 103, DATE '2026-09-18', DATE '2026-10-01');
SELECT id, member_id, copy_id FROM club.loans WHERE id=2000;
ROLLBACK;SELECT возвращает 2000,3,103. После ROLLBACK выдача не сохранена, исходные три строки остаются. Мы вводим BEGIN/ROLLBACK сейчас как границу безопасного учебного изменения; устройство транзакций подробно разберём позже. Все команды примера отправляются в той же сессии, иначе откат не относится к нужной работе.
CHECK due_on >= borrowed_on не проверяет количество других выдач. CHECK с неизвестным результатом также не заменяет NOT NULL. Одно ограничение нельзя считать доказательством всей модели. Внешний ключ не выбирает за приложение политику удаления: сейчас удаление участника с историей запрещено; отмена учётной записи и сохранение истории требуют отдельного решения.
Один экземпляр и несколько исторических выдач
Индекс one_active_loan_per_copy уникален по copy_id только там, где returned_on отсутствует. Один экземпляр можно выдавать много раз после возврата, но не иметь две активные выдачи одновременно. Простая UNIQUE(copy_id) запретила бы всю историю. Отдельный SELECT «свободно?» без ограничения может быть обойдён двумя клиентами.
SELECT copy_id, COUNT(*) AS active_count
FROM club.loans
WHERE returned_on IS NULL
GROUP BY copy_id
ORDER BY copy_id;Исходный результат: 100→1 и 101→1. Возвращённая выдача 102 не участвует. Механизм индекса пока воспринимаем как проверку правила; его физическую цену изучим в уроке об индексах. Повтор команды с тем же пользовательским намерением дополнительно потребует idempotency: уникальность активной выдачи не запоминает весь прежний ответ API.
Самостоятельная лаборатория
Отведите 25 минут. В новой учебной базе выполните tests.sql. Он проверяет ошибочные ссылки, неверную дату и вторую активную выдачу, ловит ожидаемые ошибки и откатывает изменения. Затем объясните три отдельных случая: выдача неизвестному участнику; срок до дня выдачи; другая активная выдача экземпляра 100. Назовите ограничение, которое должно остановить каждый случай.
Критерии: вы объясняете разницу книги и экземпляра, не предлагаете CHECK для общего счётчика других строк, сохраняете историю после возврата и понимаете, почему NULL email допустим. До отправки отрицательной команды вручную подготовьте откат: после ошибки транзакция PostgreSQL может требовать ROLLBACK, прежде чем продолжить работу.
Разбор
Внешний ключ защищает существование ссылки, CHECK — порядок дат, partial unique index — конкурирующие активные выдачи. Исправление названия не меняет book_id. Схема ещё не проверяет максимальное число книг участника и не различает отмену/утрату: эти требования нельзя считать реализованными только потому, что таблиц четыре. Добавляйте правило после точного определения его смысла и пути проверки.