Разработка схемы учёта книг в библиотечной системе

Условие

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

Ответ

Храните выдачи интервалами времени. Exclusion constraint по экземпляру книги запрещает пересекающиеся выдачи, включая исторические записи.

Пример миграции для PostgreSQL; установке btree_gist нужны соответствующие права:

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE books (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL
);
CREATE TABLE readers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE loans (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id bigint NOT NULL REFERENCES books(id),
  reader_id bigint NOT NULL REFERENCES readers(id),
  taken_at timestamptz NOT NULL,
  returned_at timestamptz,
  CHECK (returned_at IS NULL OR returned_at > taken_at),
  EXCLUDE USING gist (
    book_id WITH =,
    tstzrange(taken_at, returned_at, '[)') WITH &&
  )
);

Состояние известного экземпляра в момент $2: найдите выдачу с book_id = $1 и taken_at ≤ $2, где returned_at IS NULL или returned_at > $2. Если такой выдачи нет, книга находится в фонде. Граница возврата исключена из интервала. Эта модель не учитывает списание или ремонт; если они нужны, добавляют отдельную историю состояний.

Источник: www.postgresql.org.