Смысл строки, ключи, ограничения, связи и данные разных организаций
В прошлом уроке мы разобрали, что база содержит схемы, а схемы — таблицы. Теперь спроектируем первые таблицы. Здесь слово «схема» будет означать всю структуру данных: таблицы, столбцы и связи между ними.
Модель данных — описание того, какие факты хранит система и как эти факты связаны. Хорошая модель не только позволяет записать правильные данные, но и мешает записать заведомо неправильные.
Представим интернет-магазин, в котором несколько организаций продают товары. До создания таблиц выпишем правила предметной области:
Правило, которое должно оставаться истинным при любых изменениях данных, называют инвариантом. PostgreSQL умеет защищать многие инварианты:
NOT NULL запрещает отсутствие обязательного значения;CHECK проверяет условие, например quantity > 0;UNIQUE запрещает повторяющиеся значения;PRIMARY KEY однозначно определяет строку;FOREIGN KEY не позволяет сослаться на несуществующую строку другой таблицы.Проверка в приложении всё равно нужна, чтобы показать человеку понятную ошибку. Ограничение в базе служит последней защитой, если данные пришли из другого сервиса, служебного сценария или консоли администратора.
Зерно таблицы — точный ответ на вопрос «какой один факт хранит одна строка?». Например:
orders — один заказ;products — один товар конкретной организации;order_items — один товар внутри одного заказа.Зерно помогает выбрать ключ. В order_items сочетание order_id и
product_id должно быть уникальным: один и тот же товар не нужно дважды
записывать отдельными строками в одном заказе.
CREATE TABLE marketplace.order_items (
order_id bigint NOT NULL REFERENCES marketplace.orders(id),
product_id bigint NOT NULL REFERENCES marketplace.products(id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id)
);Команда CREATE TABLE создаёт таблицу. Для каждого столбца указаны имя, тип и
ограничения. Составной первичный ключ
PRIMARY KEY (order_id, product_id) включает два столбца и запрещает повтор их
сочетания.
Естественный ключ — значение, которое уже существует в предметной области:
номер паспорта, артикул или код страны. Искусственный ключ — специально
созданный идентификатор, обычно число id или UUID.
Естественный ключ удобен, только если он короткий, неизменяемый и действительно
уникальный. Электронная почта и номер телефона меняются, поэтому их опасно
использовать как единственный идентификатор. Чаще таблице дают искусственный
id, а бизнес-правило защищают отдельным UNIQUE.
CREATE TABLE marketplace.products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
org_id bigint NOT NULL,
sku text NOT NULL,
name text NOT NULL,
UNIQUE (org_id, sku)
);GENERATED ... AS IDENTITY просит PostgreSQL автоматически выдавать новое
число для каждой строки. Ограничение UNIQUE (org_id, sku) разрешает двум
организациям одинаковый артикул, но запрещает повтор внутри одной организации.
В системах с несколькими организациями часто встречается термин tenant.
Здесь это одна организация-клиент, чьи данные должны быть отделены от остальных.
Внешнего ключа только по user_id недостаточно: заказ организации A может
случайно сослаться на пользователя организации B. Добавим организацию в обе
стороны связи:
ALTER TABLE marketplace.app_users
ADD CONSTRAINT app_users_org_id_id_key UNIQUE (org_id, id);
ALTER TABLE marketplace.orders
ADD CONSTRAINT orders_user_same_org_fk
FOREIGN KEY (org_id, user_id)
REFERENCES marketplace.app_users (org_id, id);Теперь PostgreSQL разрешит ссылку только на пару org_id и user_id, которая
действительно существует. Это составной внешний ключ.
Цена товара в каталоге может измениться завтра, но старый чек должен сохранить цену на момент покупки. Поэтому:
products.price — текущая цена товара;order_items.unit_price — историческая цена конкретной покупки.Одинаковые на вид числа обозначают разные факты. Это не случайное дублирование.
Другое дело — orders.total_amount, который можно вычислить как сумму позиций.
Такое поле называют производным значением. Если его хранить для ускорения,
нужно точно определить, кто обновляет сумму и как находить расхождения. Иначе
позиции заказа и итог рано или поздно перестанут совпадать.
CHECK хорошо проверяет одну строку, но не должен читать соседние строки. Так,
правило «общая сумма всех платежей не больше суммы заказа» требует транзакции и
блокировки либо другого способа согласовать одновременные изменения. Эти
механизмы появятся в уроке о транзакциях.
В лаборатории 02 вы создадите товары и позиции заказа, а затем попробуете записать отрицательное количество и ссылку на пользователя другой организации. Ошибка в этих опытах — ожидаемый результат: она доказывает, что модель защищает данные.
Перед следующим уроком проверьте себя:
Подробнее: создание таблиц и ограничений.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.
Далее: Нормализация: как убрать опасные повторы