Таблицы и связи — Примеры

Пример 1 — Ключи, которых движок не оставит висящими

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

create table customers (
  email text primary key
);

create table orders (
  id integer,
  customer_email text,
  total double precision,
  placed_at text
);

Решение

create table customers (
  id integer generated always as identity primary key,
  email text not null unique,
  created_at date not null
);

create table orders (
  id integer generated always as identity primary key,
  customer_id integer not null references customers (id),
  total numeric(12, 2) not null check (total >= 0),
  placed_at date not null
);

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

Пример 2 — Какая копия имени верна

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

select o.id, o.customer_name, c.name from orders o
join customers c on c.id = o.customer_id;        (пояснительно)

 id | customer_name | name
 41 | A. Nowak      | Anna Nowak
 42 | A. Nowak      | Anna Nowak
 43 | Anna Nowak    | Anna Nowak

Решение

alter table orders drop column customer_name;

select o.id, c.name, o.total
from orders o join customers c on c.id = o.customer_id
order by o.placed_at desc;

Теперь исправление меняет одну строку, и все читатели видят новое значение: второй копии, которая могла устареть, не осталось, а соединение сводится к поиску по первичному ключу. Если измеренный запрос когда-нибудь потребует копию назад, запишите, что она намеренна, — иначе поздний читатель примет её за ошибку или доверится ей как истине. Обычно же ошибаются, обновляя копию по событию приложения. Так пропускается каждая строка, до которой событие не дошло.