Проєктування схеми

Навіщо це

Аналітикам «Крамниці» потрібна одна велика таблиця: замовлення, покупець, його населений пункт і область в одному рядку. Тоді не треба писати JOIN. Таку таблицю легко зробити, і вона правильна до першої правки.

У пісочниці нижче orders_wide збирається з 22 030 замовлень (розмір small, див. Датасет), і жоден населений пункт не належить двом областям. Виправте область в одному рядку, і такий пункт з’являється. Таблиця тепер стверджує про нього два різні факти, а звіт за областями дає для одного міста дві відповіді.

Назву області записано в кожне замовлення кожного покупця з цього пункту. Один факт лежить у тисячах рядків, а база не знає, що вони мають збігатися. Нормалізація дає правила, за якими кожен факт потрапляє в єдине місце. Правила коштують читань, тому «Крамниця» свідомо порушує частину з них.

Передумови. Ключі й обмеження цілісності: модуль 2. CREATE TABLE, ALTER TABLE і типи PostgreSQL: модуль 4. B-дерево, від якого залежить вибір ідентифікатора: модуль 8.

Сутності, зв’язки й ER-діаграма

Проєктування починають не з CREATE TABLE, а з переліку того, про що база має знати. Сутність (entity) — тип об’єктів із власною ідентичністю: покупець, замовлення, товар. Атрибут описує сутність. Зв’язок (relationship) поєднує сутності й має кардинальність (скільки об’єктів з кожного боку) та обов’язковість.

Нотацій дві. У нотації Чена (1976) сутності зображено прямокутниками, зв’язки ромбами, атрибути овалами, а кардинальність підписано цифрами 1, N, M. Діаграма швидко заростає овалами, тому на практиці частіше беруть crow’s foot. Сутність там схожа на таблицю з переліком атрибутів, а кінець лінії кодує кардинальність і обов’язковість: риска означає «один», кільце «нуль», три промені «багато».

Фрагмент «Крамниці» в нотації crow's foot. Лінію читають від кожного кінця окремо: покупець подає нуль або багато замовлень, замовлення належить рівно одному покупцеві. В order_items, shipments і inventory первинний ключ (повністю чи частково) водночас є зовнішнім.

Від customers до orders читаємо: один покупець має нуль або багато замовлень (покупців без замовлень у small 1 820, тож нижня межа нуль). Від orders до customers: замовлення належить рівно одному покупцеві, бо orders.customer_id оголошено NOT NULL. Обов’язковість на боці «один» і є NOT NULL на зовнішньому ключі.

Зв’язки й таблиці

1:N. Зовнішній ключ стоїть на боці «багато»: orders.customer_id посилається на customers. PostgreSQL сам індексує первинні ключі й UNIQUE, але не зовнішні. Без індексу на orders.customer_id запит «замовлення покупця» читає всю таблицю (модуль 8).

1:1. Зв’язок задає первинний ключ, що водночас є зовнішнім: inventory.product_id оголошено PRIMARY KEY REFERENCES products, тож другого рядка для товару вставити не можна. Таблицю можна було б злити з products, але залишок змінюється з кожним продажем, а опис ні. Кожна зміна рядка в PostgreSQL створює його нову версію (модуль 10), тому вузька таблиця дешевша. shipments існує лише для відправлених замовлень, а злиття дало б у orders колонки tracking_number, arrived_at, delivered_at, порожні до відправки. Такий ключ гарантує «не більше одного», але не «принаймні один»: рівно один рядок inventory на товар тримає не база, а завантаження даних.

M:N. «Багато до багатьох» між двома таблицями не виражається, його розкладають на проміжну таблицю з двома зовнішніми ключами. Між замовленнями й товарами це order_items. У ній живуть атрибути самого зв’язку: quantity, unit_price, discount_pct, яких немає ні в orders, ні в products. Ключ (order_id, line_no) визначає, що вважати «одним рядком». З ключем (order_id, product_id) той самий товар не можна було б додати двічі з різною знижкою. Так само влаштовано seller_follows.

Зв’язок таблиці із самою собою, «покупці-друзі», зберігає неорієнтовану пару один раз. CHECK (customer_id < friend_id) робить запис канонічним, інакше (1, 2) і (2, 1) були б двома рядками про одну дружбу.

Функціональні залежності

Нормальні форми описують одне: які колонки від яких залежать. Функціональна залежність (functional dependency) X → Y означає, що два рядки з однаковим X завжди мають однакове Y: customer_id → email, settlement_id → region_id, (order_id, line_no) → product_id, quantity. Набір колонок, що визначає всі інші колонки таблиці, є суперключем, а мінімальний такий набір — потенційним ключем.

Залежність є твердженням про предметну область, а не про поточні дані. Дані можуть її спростувати, але не довести. Спростування видно запитом: групуємо за X і шукаємо групи, де Y має кілька значень.

SELECT count(*) AS products_with_many_prices
FROM (SELECT product_id FROM order_items GROUP BY product_id HAVING count(DISTINCT unit_price) > 1) AS t;

 products_with_many_prices
---------------------------
                      2573

Із 3 079 проданих товарів (small) 2 573 трапляються в замовленнях за різними цінами, тож product_id → unit_price не виконується: ціна залежить від позиції замовлення.

Нормалізація

Візьмемо orders_wide із початку сторінки. Її ключ order_id, а решта залежить так: customer_id → email, settlement_id, settlement_id → settlement_name, region_id, region_id → region_name. Назва області пов’язана із замовленням лише ланцюжком через покупця й населений пункт. Звідси три аномалії.

СпробуйPostgreSQLCtrl+Enter — виконати

CREATE TABLE orders_wide AS SELECT o.order_id, o.placed_at, o.status, o.total_amount, c.customer_id, c.email, s.settlement_id, s.name AS settlement_name, g.region_id, g.name AS region_name FROM orders o JOIN customers c ON c.customer_id = o.customer_id LEFT JOIN settlements s ON s.settlement_id = c.settlement_id LEFT JOIN regions g ON g.region_id = s.region_id; UPDATE orders_wide SET region_name = 'Київська обл.' WHERE order_id = (SELECT min(order_id) FROM orders_wide WHERE settlement_id IS NOT NULL); SELECT count(*) AS settlements_with_two_regions FROM ( SELECT settlement_id FROM orders_wide WHERE settlement_id IS NOT NULL GROUP BY settlement_id HAVING count(DISTINCT region_name) > 1 ) AS t;

ВиконатиСкинути запитСкинути базу

 settlements_with_two_regions
------------------------------
                            1

Без UPDATE запит повертає 0. Це аномалія оновлення (update anomaly): зміна одного факту потребує змін у багатьох рядках, і пропущений лишає суперечність. Аномалія вставки: щоб записати новий населений пункт, доводиться вигадувати замовлення, бо ключ таблиці order_id. Аномалія видалення: з останнім замовленням покупця зникає відомість, де він живе. Кожна нормальна форма прибирає свій клас аномалій.

1НФ. Значення колонки неподільне для застосунку, рядки не залежать від порядку й не містять повторюваних груп. Її порушила б колонка orders.items зі значенням '12:2,55:1'. Порахувати товари чи знайти замовлення товару 55 можна лише розбором рядка, а зовнішній ключ на товар не поставити. Виправлення вже в схемі: order_items.

2НФ. Кожна неключова колонка залежить від усього ключа; форма має сенс для складеного ключа. Якби order_items містила placed_at, він залежав би від order_id, тобто від половини ключа (order_id, line_no), і правка одного рядка давала б замовлення з двома датами. Виправлення: placed_at живе в orders.

3НФ. Кожна неключова колонка залежить від ключа безпосередньо, а не через іншу неключову. orders_wide порушує це двічі: order_id → customer_id → settlement_id → region_id. Виправлення — винести кожну проміжну станцію в таблицю, де вона ключ. Коротко: неключова колонка залежить від ключа, від усього ключа й лише від ключа.

Розкладання за залежностями. Колір смужки показує, куди потрапляє колонка. Результат збігається зі схемою «Крамниці»: кожна назва області записана один раз, у regions.

Розклад без втрат: JOIN чотирьох таблиць повертає ті самі 22 030 рядків, що були в orders_wide, а назва області лежить в одному з 27 рядків regions.

Суворіші форми: НФБК і 4НФ

НФБК (нормальна форма Бойса–Кодда). Для кожної нетривіальної залежності X → Y лівий бік має бути суперключем. 3НФ прощає залежність, якщо Y входить у якийсь потенційний ключ, НФБК не прощає.

Припустимо, є seller_category_managers(seller_id, category_id, manager_id): менеджер веде рівно одну категорію, а продавець у категорії має одного менеджера. Ключі (seller_id, category_id) і (seller_id, manager_id). Залежність manager_id → category_id порушує НФБК (manager_id не суперключ), але 3НФ її допускає, адже category_id входить у ключ. Менеджера переводять в іншу категорію, і правити доводиться всі рядки його продавців.

Розклад на (manager_id, category_id) і (seller_id, manager_id) це усуває. Проте залежність (seller_id, category_id) → manager_id тепер не виражена жодним ключем, і перевірити її можна лише тригером. Тож НФБК буває свідомим вибором.

4НФ оглядово. Багатозначна залежність виникає, коли два незалежні набори значень прив’язано до одного ключа: продавець працює з кількома перевізниками й приймає кілька способів оплати. Таблиця (seller_id, carrier, method) змушує зберігати всі комбінації, і новий перевізник додає рядок на кожен спосіб оплати. 4НФ (Фейджин, 1977) розкладає її на (seller_id, carrier) і (seller_id, method).

Денормалізація як рішення

Нормалізована схема дорожча на читання: кожен звіт потребує JOIN. Денормалізація свідомо повторює дані заради швидшого читання. Перед нею корисно спитати: чи можна це значення порахувати з решти даних? Якщо так, воно надлишкове, і потрібен механізм, що тримає його правильним. Якщо ні, це окремий факт, а не копія. У датасеті є обидва випадки.

orders.total_amount — сума рядків замовлення після знижок. Її можна порахувати з order_items, але список замовлень читається швидше без JOIN із таблицею позицій, яка вдвічі довша за orders. Плата: сума має змінюватись у тій самій транзакції, що й позиції, а розходження ловить перевірочний запит.

sellers.rating_cached — середня оцінка відгуків на товари продавця. З кожним новим відгуком вона застаріває й лишається NULL, доки відгуків немає (у десяти продавців зі 120). Назва каже, що це кеш: його має оновлювати фонове завдання, а читач знає про запізнення. У знімку датасету всі 120 значень збігаються з перерахунком, але так буває лише на знімку.

order_items.unit_price схожа на копію products.price, але нею не є. Ціна товару змінюється, а сума, яку покупець заплатив позавчора, ні. Якби позиції читали ціну з products, правка каталогу переписала б історію. Сума рядків розійшлася б із total_amount, а виручка за минулий рік мінялася б щоразу, коли продавець виправляє ціну.

Тож unit_price є фактом продажу, який нізвідки перерахувати. Тест простий: якщо значення в джерелі зміниться, чи мало б змінитися й це? Для total_amount і rating_cached так, тому це надлишок із механізмом синхронізації. Для unit_price ні, і це історія.

СпробуйPostgreSQLCtrl+Enter — виконати

SELECT (SELECT count(*) FROM orders o WHERE o.total_amount IS DISTINCT FROM (SELECT sum(round(i.quantity * i.unit_price * (1 - i.discount_pct / 100), 2)) FROM order_items i WHERE i.order_id = o.order_id)) AS total_mismatch, (SELECT count(*) FROM order_items i JOIN products p ON p.product_id = i.product_id WHERE i.unit_price <> p.price) AS price_differs, (SELECT count(*) FROM order_items) AS items;

ВиконатиСкинути запитСкинути базу

 total_mismatch | price_differs | items
----------------+---------------+-------
              0 |         36492 | 39504

Перша колонка перевіряє надлишок, друга показує, наскільки unit_price відійшла від поточного каталогу (small).

Антипатерни

Деякі схеми трапляються так часто, що мають назви (Karwin, 2010).

EAV (entity-attribute-value). Замість колонок таблиця рядків (product_id, name, value). Новий атрибут не потребує ALTER TABLE, але всі значення мають тип text. Тому CHECK (weight_g > 0) чи зовнішній ключ на бренд поставити не можна, а запит «бренд X і вага понад 500 г» потребує JOIN таблиці із самою собою на кожну умову. Потребу «набір ключів різний у різних товарів» у датасеті закрито колонкою attributes jsonb. Значення в ній мають типи JSON, фільтр пишеться одним запитом, а обов’язкові ключі задає CHECK (модуль 4). EAV виправдана, коли атрибутів тисячі й вони справді довільні.

Поліморфні зв’язки. Примітку, що може стосуватись замовлення, товару або відгуку, часто зберігають як (target_type, target_id). Зовнішній ключ не може вказувати в різні таблиці залежно від сусідньої колонки, тож база не перевірить, що ціль існує, і видалення замовлення лишає сиріт. Надійніше мати окремий ключ на кожну ціль і вимагати рівно один:

CREATE TABLE notes (
    note_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   bigint REFERENCES orders,
    product_id integer REFERENCES products,
    review_id  bigint REFERENCES reviews,
    body       text NOT NULL,
    CHECK (num_nonnulls(order_id, product_id, review_id) = 1)
);

Список у рядку. orders.items = '12:2,55:1' порушує 1НФ, а LIKE '%55%' знайде й товар 155. Якщо елементи є просто значеннями, підійде масив чи jsonb, але зовнішнього ключа на елемент масиву PostgreSQL не має: тоді потрібна таблиця з рядком на елемент.

Soft delete. Замість DELETE рядок позначають видаленим. У customers це status = 'deleted' разом із deleted_at, а CHECK ((status = 'deleted') = (deleted_at IS NOT NULL)) не дає поставити одне без другого. Справді видалити покупця не вийде: на нього посилаються замовлення, платежі й відгуки, які треба зберегти для обліку. Водночас право на видалення персональних даних (модуль 21) вимагає прибрати ім’я, дату народження й адресу. Тому видалений акаунт ще й анонімізовано:

СпробуйPostgreSQLCtrl+Enter — виконати

SELECT customer_id, email, full_name, birth_date, settlement_id, status FROM customers WHERE status = 'deleted' ORDER BY customer_id LIMIT 3;

ВиконатиСкинути запитСкинути базу

 customer_id |          email          | full_name | birth_date | settlement_id | status
-------------+-------------------------+-----------+------------+---------------+---------
          44 | deleted-44@anon.example |           |            |               | deleted
          80 | deleted-80@anon.example |           |            |               | deleted
          88 | deleted-88@anon.example |           |            |               | deleted

Рядок лишається скелетом, на який посилаються 138 замовлень (small), а персональних даних у ньому немає. Мітка без анонімізації право на видалення не виконує. Soft delete має ціну: кожен запит мусить знати про статус, і забутий фільтр покаже видалені акаунти в розсилці. Унікальність email доводиться обмежувати частковим індексом WHERE status <> 'deleted'.

Ідентифікатори

Первинний ключ має бути унікальним, стабільним і незмінним. Природні ключі цього не гарантують: у customers є групи адрес, що різняться лише регістром, а людина змінює email. Тому майже скрізь є сурогатний ключ, і питання в тому, який.

Identity. Значення бере лічильник bigint: 8 байтів, зростають, у межах однієї бази без колізій. У датасеті GENERATED ALWAYS AS IDENTITY, а не serial: це стандартна форма, і ALWAYS не дає вручну підставити значення. Недоліки: лічильник один (злиття двох баз дає колізії), а значення розкривають обсяг бізнесу й легко вгадуються (/orders/1043).

UUIDv4. 122 випадкових біти, 16 байтів, генерується де завгодно, зокрема клієнтом до вставки (gen_random_uuid()). Ціна в тому, як такий ключ лягає в B-дерево (модуль 8). Кожен новий ключ потрапляє в довільну листову сторінку (leaf), бо сусіди за часом мають далекі ключі. Поки індекс вміщається в shared_buffers, це розбиття сторінок (page split) і більший індекс. Коли ні, кожна вставка читає випадкову сторінку з диска. Після контрольної точки така сторінка потрапляє у WAL цілою (WAL — журнал, у який база спершу записує зміни, модуль 11).

UUIDv7. 48 біт часу Unix у мілісекундах, далі частки мілісекунди й випадкові біти (RFC 9562). Значення зростають разом із часом, тож вставки знову йдуть у правий край індексу, а глобальна унікальність без координації лишається. У PostgreSQL 18 uuidv7() вбудована, а uuid_extract_timestamp() повертає з неї час. Час створення стає видимим усім, хто бачить ідентифікатор: для замовлення це прийнятно, для об’єкта, чий вік є секретом, ні.

Куди лягають нові ключі. Послідовний ключ (identity, UUIDv7) щоразу потрапляє в правий листок, решта сторінок повні. Випадковий (UUIDv4) щоразу обирає інший листок, і сторінки розбиваються з недозаповненням. Заповненість із вимірів нижче.

Як це насправді

Схему таблиці показує \d у psql. Для orders (скорочено):

Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    "orders_pickup_point_id_fkey" FOREIGN KEY (pickup_point_id) REFERENCES pickup_points(pickup_point_id)
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id)
    TABLE "payments" CONSTRAINT "payments_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id)
    TABLE "reviews" CONSTRAINT "reviews_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id)
    TABLE "shipments" CONSTRAINT "shipments_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id)

Блок «Referenced by» показує, хто посилається на таблицю, і тому \d корисніший за діаграму, коли схему проєктували не ви. Індекс один, orders_pkey: на зовнішніх ключах їх немає. У пісочниці те саме дає каталог:

СпробуйPostgreSQLCtrl+Enter — виконати

SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'orders' ORDER BY ordinal_position;

ВиконатиСкинути запитСкинути базу

Обмеження лежать у pg_constraint: contype p (первинний ключ), f (зовнішній), u (унікальне), c (перевірка), а в PostgreSQL 18 ще й n для NOT NULL. Визначення повертає pg_get_constraintdef(oid).

Тепер ціна ідентифікатора. Три таблиці з однаковими колонками й різними ключами (bigint identity, gen_random_uuid(), uuidv7()) заповнено 2 мільйонами рядків: 2000 транзакцій по 1000 рядків, PostgreSQL 18.6 у Docker, перед кожною таблицею CHECKPOINT. Структуру індексу показує pgstatindex з розширення pgstattuple:

CREATE EXTENSION pgstattuple;
SELECT leaf_pages, avg_leaf_density, leaf_fragmentation FROM pgstatindex('t_uuid4_pkey');

Ключ	Час, с	Таблиця, МБ	Індекс, МБ	Заповненість листків	WAL, МБ
identity	4.5–5.5	99.5	42.9	90 %	286
UUIDv7	5.6–7.4	114.9	60.2	90 %	312
UUIDv4	10.7–11.3	114.9	75.4–75.9	71–72 %	340–451

Діапазони — три прогони. Різницю між identity й UUIDv7 (15 МБ у таблиці, 17 в індексі) дає лише ширина ключа: 16 байтів проти 8. Ще 15 МБ індексу UUIDv4 додає випадковість: листки заповнено на 71 % замість 90 %, бо сторінки розбиваються навпіл. leaf_fragmentation 50 % означає, що логічно сусідні листки лежать у файлі не поруч. Вставки майже вдвічі повільніші й пишуть більше WAL. Індекс на 2 мільйони рядків вміщається в shared_buffers (256 МБ), тож тут немає читань із диска. На більшому обсязі розрив, імовірно, зросте, але цього ми не вимірювали.

Типові помилки розуміння

«Нормалізація завжди правильна». Вона прибирає аномалії, але віддає за це JOIN. Надлишок має бути свідомим, з механізмом синхронізації, як total_amount. Історичний факт, як unit_price, не дублікат.

«UUID як первинний ключ завжди повільний». Повільним робить випадковість, а не 16 байтів: UUIDv7 вставляє в правий край, як послідовність.

«Soft delete — безпечне видалення». Мітка лишає всі дані на місці, зокрема персональні, і кожен запит мусить про неї пам’ятати.

Перевір себе

1. У таблиці `order_lines(order_id, line_no, product_id, quantity, placed_at)` ключ `(order_id, line_no)`, а `placed_at` однакова для всіх рядків одного замовлення. Яку форму порушено?

2НФ: `placed_at` залежить лише від частини складеного ключа1НФ: у таблиці є колонки з датою3НФ: `placed_at` залежить від `quantity`НФБК: `line_no` не суперключ

2. Таблицю `(manager_id, seller_id, category_id)` зведено до 3НФ, але не до НФБК. Яка залежність це пояснює?

`seller_id → category_id``manager_id → category_id` при тому, що `manager_id` не суперключ, а `category_id` входить у потенційний ключ`(seller_id, category_id) → manager_id`, бо ця пара є ключемБудь-яка залежність від суперключа

3. Команда хоче прибрати `order_items.unit_price` і брати ціну з `products.price`: «це дублікат». Що зламається?

Нічого: значення однаковіЗовнішній ключ на `products`Історія: після зміни ціни в каталозі старі замовлення змінять суму й розійдуться з `total_amount`1НФ таблиці `order_items`

4. Чому `(target_type, target_id)` у таблиці приміток гірша за три окремі зовнішні ключі з `CHECK (num_nonnulls(...) = 1)`?

Бо `text` повільніший за `bigint`Бо PostgreSQL не дозволяє двох колонок однакового типуБо `CHECK` швидший за індексБо зовнішній ключ не може залежати від значення сусідньої колонки, тож сироти й неіснуючі цілі лишаються без контролю бази

5. Дві однакові таблиці заповнили вставками: одну з `gen_random_uuid()`, другу з `uuidv7()`. Що відрізнятиметься й чому?

Індекс на UUIDv4 буде менш щільним, а вставки дорожчими: ключі падають у випадкові листки, і сторінки розбиваютьсяНічого: обидва ключі 16 байтівТаблиця з UUIDv7 займе вдвічі більше, бо має мітку часуІндекс на UUIDv4 буде меншим, бо випадкові ключі краще стискаються

Лабораторна

L2. Схема і міграція без простою: спроєктувати в 3НФ схему повернень товарів, яку перевіряють вставками, що мають пройти й бути відхилені, а потім додати обов’язкову колонку до orders кроками expand/contract, поки на таблицю йде навантаження.

Джерела

• P. P. Chen, The Entity-Relationship Model: Toward a Unified View of Data, ACM Transactions on Database Systems 1(1), 1976.
• R. Fagin, Multivalued Dependencies and a New Normal Form for Relational Databases, ACM Transactions on Database Systems 2(3), 1977.
• B. Karwin, SQL Antipatterns, Pragmatic Bookshelf, 2010.
• PostgreSQL 18, документація: Constraints, UUID Type, UUID Functions, pgstattuple.
• RFC 9562, Universally Unique IDentifiers (UUIDs), IETF, 2024.