Капстоун: проектирование OLTP-схемы

В прошлом уроке мы разобрали бизнес-кейс проката велосипедов, нашли пять сущностей (Customer, Station, Bike, Rental, Payment), связи и бизнес-правила, и собрали conceptual model. Теперь следующий шаг сквозного проекта: превратить концептуальную модель в OLTP-схему — рабочую базу данных для самой системы проката, нормализованную до 3NF/BCNF, с ключами, типами и constraints.

Напомним из модуля про OLTP: это система с короткими частыми транзакциями (взять велосипед, вернуть, оплатить), высокой конкурентностью и приоритетом целостности. Под такую нагрузку правильна нормализованная схема. Наша задача — пройти от conceptual model к physical DDL, на каждом шаге применяя то, что вы изучили.

Шаг 1: от сущностей к таблицам и ключам

Каждая сущность conceptual model становится таблицей. Первое решение для каждой — primary key.

Из модуля про ключи: у нас выбор между natural key и surrogate key. У клиента есть «естественный» кандидат — email (уникален). Но email может меняться, бывает длинным, и завязывать на него все foreign keys рискованно. Поэтому для всех таблиц берём surrogate key — компактный целочисленный идентификатор. Это стандартный выбор для OLTP: узкий ключ ускоряет JOIN и изолирует схему от изменений бизнес-данных. Email при этом не выбрасываем — он остаётся атрибутом с ограничением UNIQUE (это alternate key).

Начнём с простых сущностей — Station и Customer, у них нет внешних зависимостей:

CREATE TABLE station (
    station_id    SERIAL PRIMARY KEY,        -- surrogate key
    name          VARCHAR(100) NOT NULL,
    address       VARCHAR(200) NOT NULL,
    capacity      INTEGER      NOT NULL CHECK (capacity > 0)
);

CREATE TABLE customer (
    customer_id   SERIAL PRIMARY KEY,        -- surrogate key
    full_name     VARCHAR(150) NOT NULL,
    email         VARCHAR(150) NOT NULL UNIQUE,  -- natural key как alternate
    phone         VARCHAR(20),
    registered_at TIMESTAMP    NOT NULL DEFAULT now()
);

Уже здесь работают бизнес-правила из урока 1, превращённые в constraints: capacity > 0 (станция не может иметь неположительную ёмкость), email ... UNIQUE (клиент регистрируется один раз), NOT NULL на обязательных полях.

Шаг 2: foreign keys для связей

Сущность Bike зависит от Station — у велосипеда есть домашняя станция. Из модуля про связи: связь 1:N реализуется foreign key на стороне «многих». Велосипедов много, станция одна — значит, FK ставится в таблицу bike.

CREATE TABLE bike (
    bike_id          SERIAL PRIMARY KEY,
    bike_type        VARCHAR(20) NOT NULL
                     CHECK (bike_type IN ('regular', 'electric')),
    home_station_id  INTEGER NOT NULL
                     REFERENCES station(station_id),
    status           VARCHAR(20) NOT NULL DEFAULT 'available'
                     CHECK (status IN ('available', 'rented', 'maintenance'))
);

CHECK (bike_type IN ('regular', 'electric')) — это бизнес-правило «тип велосипеда обычный или электро», выраженное в схеме. REFERENCES station(station_id) — referential integrity: нельзя завести велосипед на несуществующей станции.

Теперь Rental — центральная и самая интересная таблица. Аренда связана с тремя сущностями: клиент, велосипед и станция, причём станция в двух ролях (старт и конец) — это role-playing из модуля про dimension-таблицы, проявившийся уже в OLTP. Две роли = два отдельных foreign key на одну и ту же таблицу:

CREATE TABLE rental (
    rental_id          SERIAL PRIMARY KEY,
    customer_id        INTEGER NOT NULL REFERENCES customer(customer_id),
    bike_id            INTEGER NOT NULL REFERENCES bike(bike_id),
    start_station_id   INTEGER NOT NULL REFERENCES station(station_id),
    end_station_id     INTEGER          REFERENCES station(station_id),
    started_at         TIMESTAMP NOT NULL,
    ended_at           TIMESTAMP,
    CHECK (ended_at IS NULL OR ended_at > started_at)
);

Разберём решения. start_station_id — NOT NULL (аренда всегда где-то началась), end_station_id — допускает NULL (аренда может быть в процессе, велосипед ещё не вернули). ended_at тоже NULL, пока аренда идёт. CHECK (ended_at IS NULL OR ended_at > started_at) ловит абсурд — возврат раньше старта. Два FK на station — это и есть role-playing в реляционной схеме.

Наконец Payment. Связь Аренда-Платёж — 1:0..1 (у аренды ноль или один платёж). Реализовать опциональный 1:1 можно так: FK на rental в таблице payment плюс ограничение UNIQUE на этом FK — это гарантирует «не больше одного платежа на аренду».

CREATE TABLE payment (
    payment_id   SERIAL PRIMARY KEY,
    rental_id    INTEGER NOT NULL UNIQUE       -- UNIQUE даёт 1:0..1
                 REFERENCES rental(rental_id),
    amount       DECIMAL(8,2) NOT NULL CHECK (amount >= 0),
    paid_at      TIMESTAMP    NOT NULL DEFAULT now(),
    method       VARCHAR(20)  NOT NULL
                 CHECK (method IN ('card', 'cash', 'app'))
);

rental_id ... UNIQUE — ключевая деталь: без UNIQUE связь была бы 1:N (много платежей на аренду), с UNIQUE — ровно 1:0..1, как требует бизнес-правило.

Шаг 3: проверяем нормализацию

Схема собрана — теперь проверим её на нормальные формы из модулей про нормализацию. Это не формальность: нормализация гарантирует отсутствие аномалий вставки, обновления и удаления.

1NF — все значения атомарны, нет повторяющихся групп. Проверяем: в наших таблицах нет ячеек со списками, нет «телефон1, телефон2». Каждое поле атомарно. 1NF выполнена.

2NF — нет partial dependencies (зависимостей от части составного ключа). 2NF актуальна только при составном primary key. У нас все PK — одиночные surrogate keys (rental_id, payment_id, …). Раз составных ключей нет — partial dependency невозможна в принципе. 2NF выполнена автоматически.

3NF — нет transitive dependencies: non-prime атрибут не зависит от другого non-prime. Вот здесь нужна внимательность. Проверим rental: зависят ли started_at, ended_at от чего-то, кроме rental_id? Нет — они свойства самой аренды. А не закрался ли transitive? Представим, что мы по ошибке добавили в rental столбец customer_email. Тогда: rental_id -> customer_id -> customer_email — customer_email зависит от customer_id (non-prime) транзитивно. Это нарушение 3NF. Мы такой столбец не добавляли — customer_email живёт только в customer. 3NF выполнена.

BCNF — для каждой нетривиальной FD детерминант является superkey. BCNF строже 3NF. В наших таблицах каждый non-prime атрибут зависит только от полного PK (он же единственный superkey), «опасных» FD с non-superkey-детерминантом нет. Схема в BCNF.

Шаг 4: индексы под нагрузку

Последний штрих physical-модели — индексы. Из модуля про OLTP: foreign keys стоит индексировать, потому что по ним постоянно идут JOIN и проверки целостности.

-- Индексы на foreign keys: ускоряют JOIN и каскадные проверки
CREATE INDEX idx_rental_customer ON rental(customer_id);
CREATE INDEX idx_rental_bike     ON rental(bike_id);
CREATE INDEX idx_bike_station    ON bike(home_station_id);
-- Индекс под частый запрос "активные аренды клиента":
CREATE INDEX idx_rental_active   ON rental(customer_id) WHERE ended_at IS NULL;

PK и UNIQUE-ограничения СУБД индексирует сама. Добавляем индексы на FK и на типичные паттерны запросов (например, «активные аренды» — частичный индекс по ended_at IS NULL). Это уже физический уровень: одна и та же логическая модель, разные индексы под разную нагрузку.

OLTP-схема готова: пять нормализованных таблиц, ключи, referential integrity, бизнес-правила как constraints, индексы. На ней система проката может работать — корректно и быстро принимать аренды и платежи. Но для аналитики («выручка по месяцам и типам велосипедов») эта схема неудобна: ответы потребуют множества JOIN. Поэтому следующий урок берёт ту же предметную область и строит для неё другую модель — star schema.

Попробуй сам

Продолжите кейс онлайн-библиотеки из прошлого урока (читатель, книга, автор, жанр, выдача, штраф).

1. Для каждой сущности создайте таблицу с surrogate key. Где есть natural key (ISBN книги?) — оставьте его атрибутом с UNIQUE.
2. Расставьте foreign keys по правилу «FK на стороне многих». Учтите, что у книги есть автор и жанр.
3. Добавьте constraints из бизнес-правил: CHECK на размер штрафа, NOT NULL на обязательных полях, ограничение «дата возврата не раньше даты выдачи».
4. Проверьте схему по 1NF, 2NF, 3NF, BCNF. Особенно ищите соблазн продублировать чужой атрибут (имя автора в таблице книги?) — и не поддавайтесь.
5. Перечислите, какие индексы вы создадите и почему.