← назад к разделу

Задача звучит просто: проверить, что при отмене заказа создаётся возврат. Открываете базу стенда — сорок таблиц с именами вроде order_items и idempotency_keys, и непонятно, где искать. Ориентироваться помогает схема — описание базы: какие есть таблицы, какие в них колонки и типы, что обязательно и какая таблица на какую ссылается. Пишут её разработчики, а читают все: видно, где лежат нужные данные, какое поле нельзя оставить пустым и что удалится вместе с заказом.

в заказе много товаров, товар — во многих заказах ordersidstatusord-02COMPLETEDord-04PAID productsidtitleprod-02клавиатураprod-03мышь ?в колонку помещается одно значение —ссылку на «много товаров» записать некуда order_items — связующая таблицаorder_idproduct_idqtyord-02prod-021ord-02prod-031ord-04prod-033 order_idproduct_idдве связи «один ко многим» вместо одной «многие ко многим»

Двумя таблицами связь «многие ко многим» не записывается: в колонке помещается одно значение. Поэтому между ними появляется третья таблица с парой ключей — и со своим полем quantity, которое относится к паре, а не к заказу и не к товару.

Схема — это правила, а не данные

Таблицу не рисуют мышкой — её объявляют запросом, и он остаётся её описанием: клиент показывает его вкладкой DDL. Вот так объявлена orders в песочнице курса:

CREATE TABLE orders (
    id              VARCHAR(36) PRIMARY KEY,
    customer_id     VARCHAR(36) NOT NULL,
    seller_id       VARCHAR(36) NOT NULL,
    status          VARCHAR(32) NOT NULL,
    currency        VARCHAR(3) NOT NULL,
    total_amount    NUMERIC(15,2) NOT NULL,
    created_at      TIMESTAMP NOT NULL,
    paid_at         TIMESTAMP,
    parent_order_id VARCHAR(36) REFERENCES orders(id)
);

Читается построчно: имя, тип, ограничения. NOT NULL — поле обязательное, строку без него база не примет. А paid_at объявлена без него — подсказка: заказ без даты оплаты существует, и это состояние надо проверить. Последняя строка — ссылка таблицы на саму себя: заказ-замена помнит, вместо какого заказа его оформили. Из-за неё ord-13 так просто не удалить — на него смотрит ord-15.

Про ограничения в этом объявлении сказано не всё: кроме NOT NULL в схемах живут ещё три вида, и каждый отвечает на вопрос «чего база не даст записать».

UNIQUE — значение не повторяется. У покупателя в песочнице так объявлена почта, и вторую регистрацию на тот же адрес отвергнет сама база, даже если проверка на форме не сработала. CHECK — проверка выражения: CHECK (total_amount > 0) не пустит заказ на нулевую сумму, CHECK (status IN ('CREATED', 'PAID', 'CANCELLED')) не пустит статус, которого нет в списке. DEFAULT — не запрет, а подстановка: с ним колонка заполняется сама, когда значение не прислали.

ALTER TABLE orders ADD CONSTRAINT orders_amount_positive CHECK (total_amount > 0);
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();

Отсюда ответ на частый вопрос «почему дата создания появляется, хотя приложение её не передаёт»: у колонки стоит DEFAULT now(). И важная деталь про сочетание: UNIQUE не считает дублями пустые значения, потому что NULL не равен NULL. «Одно значение или ничего» даёт только пара UNIQUE + NOT NULL.

ALTER TABLE orders ADD COLUMN cancelled_at TIMESTAMP; меняет существующую таблицу: добавляет колонку, ставит или снимает ограничение. DROP TABLE orders; удаляет её вместе со строками, без вопросов и без корзины: чинится это только из резервной копии. Эти команды мы читаем, а не запускаем: схему стенда меняет разработчик.

Ключи: первичный, суррогатный, внешнийспросят на собеседовании

На строку надо как-то сослаться. Первичный ключ — колонка, значение которой уникально и не пусто: по нему строка находится однозначно. Почти всегда это ничего не означающий id — суррогатный ключ, UUID или число, которое база выдаёт сама. Взять вместо него почту заманчиво, но почта меняется, а на ключ уже ссылаются другие таблицы.

Внешний ключ — обещание, что значение в колонке существует в другой таблице:

ALTER TABLE payments
    ADD CONSTRAINT fk_payments_order
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

Отсюда две вещи. Платёж с несуществующим order_id в базу не попадёт — откажет сама база, даже если приложение попросит. И хвост ON DELETE решает судьбу связанных строк: CASCADE — удаляют заказ, молча исчезают его платежи и позиции; RESTRICT — база не даст удалить заказ, пока на него ссылаются. Ровно здесь рождается дефект «удалили товар — из старых заказов пропали позиции».

Вариантов у ON DELETE больше двух. SET NULL обнуляет ссылку: удалили категорию — товары остались, а поле категории у них стало пустым (колонка для этого должна разрешать пустоту). SET DEFAULT подставляет вместо ссылки значение по умолчанию. NO ACTION ведёт себя как RESTRICT, но проверку откладывает до конца транзакции — разница видна, когда внутри одной транзакции строку сначала удаляют, а потом ссылку восстанавливают.

DELETE заказа платежи удалены CASCADE база отказала RESTRICT ссылка обнулена SET NULL

Хвост ON DELETE у внешнего ключа решает судьбу ссылающихся строк, и самый тихий исход тут первый: платежи уходят вместе с заказом, без единого сообщения.

Есть и парный ON UPDATE — что делать, если поменяется сам ключ, на который ссылаются: ON UPDATE CASCADE протянет новое значение во все дочерние строки. Нужен он редко, и ровно по той причине, о которой сказано выше: первичный ключ берут суррогатным, а у значения, которое ничего не значит, нет повода меняться.

Оговорка про нашу песочницу: внешний ключ там ровно один — orders.parent_order_id. У payments.order_id и order_items.order_id его нет, поэтому сюжет «удалили заказ — исчезли платежи» на стенде курса не воспроизвести: удалится только сам заказ, а платёж останется висеть сиротой, ссылаясь в пустоту. Это, кстати, ровно та беда, от которой внешний ключ и защищает.

Откуда берётся третья таблица

В заказе много товаров, и один товар лежит во многих заказах — двумя таблицами это не записать. Поэтому появляется третья, связующая таблица: в песочнице это order_items, где строка означает «в заказе ord-04 товар prod-03, три штуки».

Заодно там лежат поля пары: quantity и unit_price — цена на момент покупки. Поэтому подорожание не переписывает историю: поменяли цену в products — старые order_items обязаны остаться прежними.

Сумма заказа лежит в двух местах: в orders.total_amount и в позициях. Расхождение находит один запрос, пустой результат значит «сходится»:

живой пример

SELECT o.id, o.total_amount, coalesce(sum(i.unit_price * i.quantity), 0) AS items_total
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.total_amount
HAVING coalesce(sum(i.unit_price * i.quantity), 0) <> o.total_amount;
Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Соединение здесь обязано быть LEFT. С обычным из проверки выпадут заказы, у которых позиций нет вовсе, — а это самые подозрительные строки: сумма есть, а покупать нечего. На песочнице таких три: ord-21, ord-22 и ord-23 числятся на 990, 1200 и 1500 рублей без единой позиции. COALESCE нужен по той же причине: сумма по пустому набору — это NULL, а не ноль, и сравнение без него молча промолчит.

Нормальные формы: три правила на одном примереспросят на собеседовании

Таблицу можно спроектировать так, что она будет работать и при этом врать. Представьте orders, где в каждой строке лежат имя покупателя, его телефон и список товаров через запятую: «book, pen». Покупатель сменил телефон, и его нужно поправить в сорока строках; одну пропустили, и у одного человека два телефона. Товар переименовали, а он спрятан внутри текста, и отчёт по нему не сходится. Правила, которые от этого защищают, называются нормальными формами, и для рабочей схемы хватает трёх.

Первая форма (1NF): в ячейке одно значение. Без списков через запятую и без колонок item1, item2, item3. Список товаров заказа это строки в order_items, по одной на товар: тогда «сколько раз купили ручку» считается одним COUNT, а не разбором строки.

Вторая форма (2NF): колонка зависит от всего ключа, а не от его части. У order_items ключ составной, заказ плюс товар. Название товара зависит только от товара, а не от пары, поэтому ему место в products, а в order_items остаётся ссылка product_id. Иначе переименование товара это правка во всех его позициях во всех заказах.

Третья форма (3NF): колонка зависит только от ключа, а не от другой неключевой колонки. Телефон покупателя зависит от покупателя, а не от заказа, поэтому он живёт в customer, а orders хранит customer_id. Признак нарушения простой: одно и то же значение повторяется во многих строках и меняется всегда вместе.

Схема песочницы ровно так и устроена: четыре таблицы вместо одной широкой, и каждая правка в одном месте. В этом смысл нормализации: факт хранится один раз, обновление не размножается, вставка и удаление не теряют чужих данных. Дальше третьей формы ходят редко: BCNF ужесточает третью для таблиц с несколькими ключами, четвёртая и пятая разбирают независимые многозначные связи, и в обычной схеме заказов до них не доходит.

Обратный ход тоже существует и называется денормализацией: orders.total_amount дублирует сумму позиций ради скорости отчётов, и запрос выше нужен именно потому, что дубль может разойтись. А вот order_items.unit_price не дубль, а отдельный факт «цена в момент покупки», который меняться не должен. Денормализуют осознанно, когда измерили, что соединения стали дорогими, и заводят проверку расхождений; хранилища для аналитики денормализуют куда сильнее, о чём статья про OLTP и OLAP.

Типы данных и CHAR против VARCHAR

Тип колонки — граница, которую выбрали до вас, а проверять вам. VARCHAR(255) — готовая пара проверок на 255 и 256 символов, NUMERIC(15,2) — вопрос про третий знак после запятой, INT — счётчик упрётся в 2 147 483 647. Деньги держат в NUMERIC, а не в FLOAT: дробные типы хранят приблизительно, и на копейках это вылезает в итогах.

Строковых типов два. VARCHAR(3) хранит ровно записанное: RUB — три символа. CHAR(3) хранит фиксированную длину и добивает короткие значения пробелами: RU превратится в RU␣. Из-за этого в выгрузках появляются хвосты пробелов, а сравнение с 'RU' в разных базах ведёт себя по-разному. Поэтому по умолчанию берут VARCHAR, а CHAR оставляют полям строго фиксированной длины — коду валюты или страны.

Индексы тоже видны в схеме

В выдаче \d orders под списком колонок печатаются индексы, и прочитать их стоит до того, как писать запросы: индекс говорит, какие обращения к этой таблице предполагались. В песочнице курса их пять, и самый говорящий — orders (customer_id, created_at DESC): по нему видно, что таблицу собирались читать как «заказы покупателя, свежие первыми».

Индекс — не часть логической схемы. Он ничего не запрещает и на результат запроса не влияет, только на скорость, поэтому его можно создать и удалить, не меняя ни одной строки данных. Единственное исключение — уникальный индекс: он заодно и ограничение, и UNIQUE в объявлении колонки создаёт именно его. Про то, когда индекс помогает, а когда только замедляет запись, — в статье почему запрос медленный.

Представление: у запроса появилось имяспросят на собеседовании

«Посмотри витрину оплаченных заказов» звучит так, будто такая таблица есть, а в схеме её нет. Скорее всего, речь о представлении — сохранённом под именем запросе: обращаются к нему как к таблице, но данных он не хранит, при каждом чтении выполняется его SELECT.

CREATE VIEW paid_orders AS
SELECT id, customer_id, total_amount, paid_at
FROM orders
WHERE status = 'PAID';

SELECT * FROM paid_orders WHERE paid_at >= DATE '2026-01-01';

Заводят их ради двух вещей. Соединение шести таблиц пишут один раз, а читают все — и в отчётах перестаёт расходиться, что считать оплаченным. Вторая — права: доступ дают к представлению без колонки с почтой, не открывая таблицу.

Данные в представлении всегда свежие — этим оно отличается от материализованного, которое хранит посчитанный результат и обновляется по расписанию. Если «в базе есть, а в витрине нет», сначала спрашивают, когда её обновляли: отставание на час бывает не дефектом, а устройством.

Два ограничения представлений, о которых узнают поздно. Первое: через представление обычно не пишут. Простое, из одной таблицы, PostgreSQL умеет обновлять сам, но как только внутри появились соединение, группировка или DISTINCT, UPDATE по нему отвергается — писать приходится в таблицы напрямую или заводить правило INSTEAD OF, а это отдельная работа. Второе: представление ничего не ускоряет. Внутри тот же SELECT, и он выполняется при каждом обращении: соединение шести таблиц останется соединением шести таблиц. Условие снаружи база попробует протолкнуть внутрь, и часто это удаётся, но не всегда — группировка или оконная функция внутри такому протаскиванию мешают. Жалоба «запрос к витрине тормозит» почти всегда означает «тормозит запрос внутри витрины».

ACID: почему оплата не зависает наполовинуспросят на собеседовании

Оплата — не одно действие: строка в payments, смена статуса заказа, запись события. Упади процесс посередине — заказ окажется оплачен деньгами, но не статусом. От этого защищает транзакция, и её гарантии описывают четырьмя буквами.

Процесс упал между записью платежа и сменой статуса, а заказ всё равно не оказался оплачен наполовину: это атомарность, шаги применяются все или ни один, на середине база не остановится. Платёж на несуществующий заказ не прошёл из-за внешнего ключа: это согласованность, зафиксировать можно только состояние, не нарушающее правил схемы.

Сосед начал оплату, но ещё не закончил, а вы его половинчатых изменений не видите: это изолированность, чужие незафиксированные шаги вам просто не показывают. Только «не видят» — это ещё не «стоят в очереди». На уровне по умолчанию (в PostgreSQL это READ COMMITTED) двое, одновременно прочитавшие остаток последнего товара и записавшие уменьшенный, спокойно уведут его в минус: каждый читал до чужой записи. Очередь появляется, когда строку берут под блокировку или меняют условным запросом — «отними единицу, если остаток больше нуля».

Машину выключили через секунду после COMMIT, и данные на месте: это долговечность, зафиксированное переживёт перезапуск сервиса и выключение машины.

Из четырёх букв настройку имеет только «I». Уровней изоляции в стандарте четыре, на практике выбирают из трёх. READ COMMITTED стоит по умолчанию: каждый запрос внутри транзакции видит данные, зафиксированные к его собственному началу, поэтому два одинаковых SELECT в одной транзакции могут дать разный ответ. REPEATABLE READ даёт всей транзакции один снимок: повторное чтение вернёт то же самое, а попытка изменить строку, которую тем временем поменял сосед, приведёт к ошибке и откату. SERIALIZABLE следит, чтобы итог совпал с каким-нибудь последовательным выполнением, и отменяет транзакцию, если это нарушено. Четвёртый, READ UNCOMMITTED, в PostgreSQL работает как READ COMMITTED — «грязное чтение» там просто не реализовано.

Правило выбора: чем выше уровень, тем меньше странностей в данных и тем чаще транзакция откатывается с ошибкой сериализации, которую приложение обязано уметь повторить. Задают уровень на транзакцию: BEGIN ISOLATION LEVEL REPEATABLE READ;. Полный разбор аномалий и их цены — в статьях ACID и изоляция и уровни изоляции.

Граница у гарантий одна — сама база. Шаг, ушедший во внешний платёжный сервис, откатить нельзя: деньги списаны там, строки нет здесь. Отсюда проверки: платёж прошёл, а ответ не дошёл; клиент повторил запрос. В песочнице на это намекает idempotency_keys — она и нужна, чтобы повтор не создал второй заказ.

Коротко

  • Схема — правила, а не данные: CREATE TABLE объявляет колонки и ограничения, ALTER меняет, DROP сносит со строками.
  • NOT NULL показывает обязательные поля, тип задаёт границы проверок: деньги — NUMERIC, не FLOAT, а CHAR добивает значение пробелами.
  • Первичный ключ берут суррогатным — он не меняется, потому что ничего не значит; ON DELETE у внешнего ключа говорит, что исчезнет вместе с родительской строкой.
  • Связь «многие ко многим» живёт в третьей таблице, там же поля пары — количество и цена на момент покупки.
  • Представление всегда отдаёт свежие данные, материализованное хранит результат и отстаёт до обновления.
  • ACID действует внутри базы; шаг во внешний сервис не откатывается, там проверяют повторы.
  • Ограничений четыре вида: NOT NULL, UNIQUE, CHECK и подстановка DEFAULT; у внешнего ключа кроме CASCADE и RESTRICT есть SET NULL и парный ON UPDATE.
  • Через представление обычно не пишут, и оно не ускоряет: внутри тот же запрос. Уровень изоляции настраивается на транзакцию, по умолчанию READ COMMITTED.
  • Нормальные формы: одно значение в ячейке (1NF), колонка зависит от всего ключа (2NF) и только от ключа (3NF); факт хранится один раз, а денормализацию делают осознанно и с проверкой расхождений.

Что почитать дальше