Нормалізація і проєктування схеми

Що таке нормалізація і навіщо вона потрібна?

Нормалізація (normalization) — це набір правил проєктування схеми, які прибирають дубльовані дані так, щоб кожен факт зберігався рівно в одному місці. Мета не академічна: надлишковість породжує три аномалії — вставки, оновлення й видалення, — а це прямі баги в проді. Сильна відповідь стартує не з означень, а з болю: email клієнта оновили в профілі, а в старих чеках він лишився старим — бо фізично лежав у кожному рядку замовлення. Нормалізація систематично цю надлишковість усуває: виносить кожну сутність в окрему таблицю, а зв'язок тримає посиланням. Практичний наслідок для QA — розуміючи нормальні форми, ти дивишся на схему й одразу бачиш, де дані можуть розсинхронитися і де на тебе чекають сироти.

Які бувають аномалії даних і чому вони виникають?

Аномалія (anomaly) — це ситуація, коли звичайна операція з даними псує їхню цілісність. Їх три. Аномалія вставки (insert) — не можна додати факт, бо бракує сусіднього: новий товар не внести в каталог, поки його ніхто не замовив, якщо каталог і замовлення в одній таблиці. Аномалія оновлення (update) — одну зміну доводиться робити в багатьох рядках: клієнт змінив місто, а воно продубльоване в кожному його замовленні, пропустиш один рядок — розсинхрон. Аномалія видалення (delete) — видаляючи одне, втрачаєш інше: прибрали останнє замовлення клієнта, а разом з ним зник і сам клієнт з його email. Корінь усіх трьох однаковий — надлишковість: один факт зберігається більш ніж в одному місці.

Яка з трьох аномалій найчастіше стає багом, який ловить QA?

Аномалія оновлення. Коли атрибут клієнта продубльований у кожному його замовленні, будь-яка зміна профілю мусить торкнутися всіх копій одразу — а на практиці якийсь апдейт колись омине одну з них. Відтоді база тримає одночасно старе й нове значення, і на екрані це виглядає суперечністю: профіль показує свіжий email, чек — торішній, і незрозуміло, якому вірити. Аномалії вставки й видалення теж болючі, але вони помітніші й рідше пролазять непоміченими. А розсинхрон від оновлення тихий: він не падає з помилкою, просто дані потроху розходяться, і виявити його можна тільки цілеспрямованою перевіркою двох копій.

Що таке функціональна залежність?

Функціональна залежність (functional dependency) A → B читається так: значення A однозначно визначає значення B. Скажімо, за customer_id завжди відновиш email клієнта — це залежність customer_id → customer_email. У зворотний бік стрілки немає: маючи email, дату конкретного замовлення не визначиш, бо замовлень у людини багато. Це базове поняття, якою мовою взагалі формулюють нормальні форми: нормалізація — це наведення ладу у функціональних залежностях. Правило, до якого все зводиться, — кожен неключовий атрибут має залежати від ключа, від усього ключа й ні від чого, окрім ключа.

Що вимагає перша нормальна форма (1НФ)?

1НФ вимагає атомарності: у кожній клітинці рівно одне неподільне значення, жодних списків і повторюваних груп, і в таблиці є ключ, що робить рядки унікальними. Порушення — це стовпець products зі значенням на кшталт "Клавіатура, Миша, Миша": такий список не дає ані порахувати позиції чи суму, ані відфільтрувати замовлення за товаром — усе впирається в парсинг рядка. Лікується розкладанням: окремий рядок на кожен товар. У бойових базах порушення найчастіше маскується під колонки phone1/phone2/phone3 або текстове поле з comma-separated списком id.

Що таке часткова залежність і яка нормальна форма її забороняє?

Часткову залежність (partial dependency) забороняє друга нормальна форма (2НФ), і застосовна вона лише там, де ключ складений — з кількох стовпців. Часткова залежність — це коли неключовий атрибут залежить лише від частини ключа, а не від усього. Приклад: у таблиці позицій із ключем (order_id, product_sku) назва й ціна товару залежать тільки від product_sku — товар називається однаково в будь-якому замовленні. Лікування — винести товари в окрему таблицю products(product_sku, product_title, price), щоб у кожній таблиці атрибути залежали від її повного ключа. Після цього ціна живе в єдиному рядку, і її зміна — один UPDATE замість правки всіх замовлень, де товар устиг з'явитися; аномалія оновлення для ціни зникає.

Що таке транзитивна залежність і як її прибирає 3НФ?

Транзитивна залежність (transitive dependency) — це коли неключовий атрибут залежить від іншого неключового, а не безпосередньо від ключа; третя нормальна форма (3НФ) її прибирає. У таблиці orders з ключем order_id місто клієнта залежить не від замовлення, а від клієнта: order_id → customer_id → customer_city, тобто місто залежить від замовлення лише транзитивно, через клієнта. Саме тут ховається початкова аномалія — email і місто дублюються в кожному замовленні людини. Виправлення — винести клієнта в окрему таблицю customers, а в orders лишити тільки посилання customer_id. Після цього кожен факт живе в одному місці: email клієнта — рядок у customers, зміна email — один UPDATE.

Поясни мнемоніку «the key, the whole key, and nothing but the key».

Це найкоротший спосіб згадати 2НФ і 3НФ на співбесіді: кожен неключовий атрибут залежить від ключа, усього ключа й нічого, крім ключа. «Від ключа» — це вимога мати ключ узагалі (по суті 1НФ). «Від усього ключа» — це 2НФ: жодних часткових залежностей від частини складеного ключа. «Нічого, крім ключа» — це 3НФ: жодних транзитивних залежностей через інший неключовий атрибут. Мнемоніка доречна, але за нею мусить стояти розуміння часткової й транзитивної залежностей — інтерв'юер зазвичай просить показати кожне порушення на конкретному прикладі, а не просто продекламувати фразу.

Нормальні форми — це частина стандарту SQL?

Ні. Нормальні форми — це теорія реляційної моделі, яку ввів Едгар Кодд, а не частина стандарту SQL. СУБД їх не вмикає й не перевіряє — це дисципліна проєктувальника схеми. База спокійно дозволить тобі тримати ненормалізовану широку таблицю з дублікатами; вона не поскаржиться. Саме тому нормалізація — питання не синтаксису, а рішень людини, що проєктує схему. І, до речі, це відрізняє нормалізацію від обмежень цілісності на кшталт FOREIGN KEY: обмеження база справді забезпечує сама, а нормальні форми — ні.

Що таке денормалізація і коли її свідомо застосовують?

Денормалізація (denormalization) — це навмисне введення дубліката заради швидкості читання. Плата за нормальну схему — з'єднання: замовлення з іменем клієнта й назвами товарів збирається кількома JOIN, і під мільйонами читань саме вони стають вузьким місцем. Тоді дублікат вводять свідомо: зберігають order_total прямо в orders, тримають items_count, дублюють customer_name заради швидкого пошуку. Це усвідомлений компроміс — платимо ризиком розсинхрону за швидкість читання. Денормалізація не помилка, а інженерне рішення; помилка — робити її несвідомо або без механізму, що тримає дублікати синхронними. І перш ніж дублювати, варто спробувати дешевший хід — лишити схему нормальною й поставити індекс під конкретний повільний запит.

У чому різниця між знімком на момент часу і кешем поточного значення?

Це два зовні схожі дублікати, які постійно плутають, хоча природа в них протилежна. Знімок на момент часу (snapshot) фіксує значення в момент події: price_at_purchase у позиції — ціна, за якою реально купили, і вона зобов'язана пережити будь-які подальші зміни каталогу, інакше чеки почнуть переписувати історію. Розбіжність копії з джерелом тут закладена в дизайн — це різні факти: «ціна тоді» проти «ціна зараз». Кеш поточного значення — навпаки, небезпечна денормалізація: order_total має завжди дорівнювати сумі позицій, і якщо позицію додали, а тотал не перерахували — це баг розсинхрону. Практичний наслідок: перш ніж заводити баг на розбіжність двох чисел, з'ясуй, це кеш поточного значення чи історичний знімок — інакше заведеш баг на правильну поведінку.

Що таке запахи схеми і які найтиповіші?

Запах схеми (schema smell) — це ознака, яка на рев'ю чи при знайомстві з базою має вмикати обережність, але сама по собі ще не означає дефекту. Список у клітинці (comma-separated чи JSON-масив id) — порушення 1НФ, за яким пошук і цілісність тримаються на чесному слові. Нумеровані колонки phone1, phone2 — повторювана група, яка не масштабується. Стовпці, які майже завжди NULL, — часто сигнал, що в одну таблицю змішали кілька сутностей. God-таблиця на 80 стовпців — те саме змішування в крайній формі. Без первинного ключа рядок нічим не адресується, тож дублі накопичуються тихо. Похідне значення без механізму оновлення — збережений total, який ніхто не перераховує. Кожен такий пункт — готове питання до бази: «а що буде, коли ці копії розійдуться?».

Як ступінь нормалізації змінює те, як QA перевіряє дані?

Нормалізована схема розкидає один екранний факт по кількох таблицях, тож щоб звірити замовлення з UI, доводиться впевнено володіти JOIN. Натомість джерело істини одне — розходитися нема чому, і ціна багів зміщується в бік цілісності зв'язків: сирітські записи й порушені зовнішні ключі. Денормалізована схема читається одним простим запитом без JOIN, але додає окремий клас перевірок — чи не розійшлися дублікати. Тобто нормалізація не прибирає баги, а переносить їх: з розсинхрону дублікатів у цілісність зв'язків і назад. Найцінніша QA-навичка навколо теми — вміти написати запит, що ловить і сироти, і розсинхрон.

Як знайти сирітські записи одним запитом?

Сирота (orphan) — рядок, що посилається на неіснуючий запис у батьківській таблиці, наприклад позиція замовлення, чий товар видалили. Класична перевірка — зовнішнє з'єднання з фільтром на NULL:

SELECT oi.order_id, oi.product_id
FROM order_items oi
LEFT JOIN products p ON p.id = oi.product_id
WHERE p.id IS NULL;

LEFT JOIN лишає всі рядки лівої таблиці, а для тих, кому не знайшлося пари справа, підставляє NULL; фільтр WHERE p.id IS NULL лишає саме сиріт. Порожній результат означає, що цілісність ціла. Такі перевірки надійно винести в автотест як інваріант даних, а ще краще — не допустити сиріт у принципі, оголосивши зовнішній ключ на рівні схеми.

Як перевірити, що денормалізований total не розійшовся з позиціями?

Прийом — порахувати справжню суму з позицій і порівняти зі збереженою через HAVING:

SELECT o.id, o.order_total, SUM(oi.qty * oi.price_at_purchase) AS real_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.order_total
HAVING o.order_total <> SUM(oi.qty * oi.price_at_purchase);

Групуємо по замовленню, рахуємо реальну суму позицій і в HAVING лишаємо тільки ті замовлення, де збережений тотал не дорівнює порахованому. Порожній результат означає, що кеш і джерело збіглися по всій базі. Той самий прийом HAVING COUNT(*) > 1 ловить дублікати там, де мав би стояти унікальний ключ. Таку перевірку зручно винести в автотест як інваріант після сценарію, що чіпає замовлення, і асертити порожній масив рядків.

Схема в 3НФ — чи гарантує це цілісність зв'язків?

Ні, і це поширена пастка. Нормалізація прибирає надлишковість, але не створює зв'язки сама: 3НФ і FOREIGN KEY — різні речі. Можна мати ідеально нормалізовану схему й водночас купу сиріт, бо зовнішні ключі просто не оголошені — база не заважатиме вставити позицію з product_id, якого немає в products. Нормалізація каже, як розкласти дані, щоб факт не дублювався; обмеження цілісності кажуть базі не дати даним зіпсуватися. Найнадійніше проти розсинхрону й сиріт працює не тест, а сама схема — правило, винесене в FOREIGN KEY, UNIQUE чи CHECK, СУБД просто не дасть порушити.

Чому доданий унікальний індекс не завжди рятує від дублікатів?

Індекс гарантує унікальність значень, а не сенсу: київ із зайвим пробілом — уже інший рядок, ніж Київ, і обмеження його пропустить (а чи вважати різницю лише в регістрі різними значеннями — вирішує колація; у PostgreSQL типове порівняння регістрозалежне). Тому дубльований довідник (той самий перелік міст чи статусів, вбитий текстом у двох таблицях) розійдеться саме так: одну копію хтось запише з іншим регістром чи зайвим пробілом, і індекс цього не помітить. Це один із запахів схеми — дубльований довідник варто виносити в окрему таблицю й посилатися на неї ключем, а не покладатися на те, що всі всюди наберуть однаковий текст. Урок для QA: наявність унікального індексу — не доказ відсутності логічних дублікатів.

Коли доводиться йти далі 3НФ — до BCNF чи 4НФ?

Рідко. Для більшості бізнес-схем 3НФ — практичний орієнтир «достатньо нормалізовано»: три аномалії вже усунені, кожен факт живе в одному місці. Суворіші форми — BCNF, 4НФ — існують і закривають хитріші випадки, зокрема зі складеними ключами-кандидатами й багатозначними залежностями, але поза цими спеціальними ситуаціями на них натрапляють нечасто. На співбесіді достатньо знати, що вони є, і що доводити схему до них варто лише за конкретної потреби, а не «про всяк випадок». Гнатися за максимальною нормальною формою без реальної проблеми — це така сама несвідома дія, як і денормалізувати наосліп.

Три кейси, як нормалізація виглядає в роботі QA: розкладання широкої таблиці крок за кроком (1НФ→2НФ→3НФ), таблиця рішень «баг чи знімок» на реальній розбіжності двох чисел, і винесення інваріанта цілісності в автотест на Playwright. Скрізь — що дивитися і чому.

Кейс 1. Розкладаємо широку таблицю крок за кроком

Дано плоску таблицю orders, у якій намішано все: замовлення, клієнта й товар. Треба довести її до 3НФ, показуючи на кожному кроці конкретне порушення й що дає виправлення.

Стартова точка — одна широка таблиця, де один факт дублюється в багатьох рядках:

order_id | order_date | customer_email    | customer_city | product_sku | product_title | price | qty
---------+------------+-------------------+---------------+-------------+---------------+-------+----
1001     | 2026-07-01 | anna@example.com  | Київ          | SKU-42      | Клавіатура    | 1200  | 1
1001     | 2026-07-01 | anna@example.com  | Київ          | SKU-77      | Миша          | 400   | 2
1002     | 2026-07-02 | anna@example.com  | Київ          | SKU-42      | Клавіатура    | 1200  | 1

Крок 1 — 1НФ. Перевіряємо атомарність: чи немає списків у клітинках і чи є ключ. Тут значення вже атомарні (по одному товару на рядок), а рядок унікально визначає пара (order_id, product_sku). Якби замість цього був стовпець products зі значенням "Клавіатура, Миша" — це порушення 1НФ, і перший крок був би саме «рядок на кожен товар».

Крок 2 — 2НФ. Ключ складений — (order_id, product_sku), отже шукаємо часткові залежності. Знаходимо дві:

• product_title, price залежать тільки від product_sku (товар називається однаково в будь-якому замовленні);
• customer_email, order_date залежать тільки від order_id.

Розщеплюємо так, щоб кожен атрибут залежав від повного ключа своєї таблиці:

orders(order_id, order_date, customer_email, customer_city)
products(product_sku, product_title, price)
order_items(order_id, product_sku, qty)   -- ключ (order_id, product_sku)

Ціна тепер в одному місці: змінити її — один UPDATE, а не правка кожного замовлення з цим товаром. Аномалія оновлення для ціни зникла.

Крок 3 — 3НФ. Дивимось на orders з ключем order_id і шукаємо транзитивні залежності. customer_city залежить не від замовлення, а від клієнта: order_id → customer_id → customer_city. Email і місто досі дублюються в кожному замовленні Анни — це та сама початкова аномалія. Виносимо клієнта:

customers(customer_id, email, city)
orders(order_id, order_date, customer_id)   -- замість email/міста лише посилання

Фінальний стан — чотири таблиці, де кожен факт живе в одному місці: email у customers, ціна у products, кількість у order_items. Усі три аномалії усунені за побудовою. Що з цього винести: нормалізація — це не «розбити на багато таблиць навмання», а щоразу відповісти на питання «від чого насправді залежить цей стовпець».

Кейс 2. Розбіжність двох чисел: баг чи знімок?

Тестувальник бачить: у чеку замовлення ціна клавіатури 1200, а в каталозі зараз 1500. Поруч інша розбіжність: order_total замовлення 1600, а сума позицій дає 2000. Обидва випадки — «два числа не збігаються», але висновок протилежний. Схема тут — фінальна з теорії: сурогатні id замість product_sku, а в позиціях зберігається знімок price_at_purchase. Перш ніж заводити баг, проходимо таблицею рішень:

Перша розбіжність легітимна: price_at_purchase навмисно копіює ціну в момент покупки, бо чек мусить лишитися історично правдивим навіть після подорожчання товару. Заводити тут баг — означає скаржитися на правильну поведінку.

Друга — справжній розсинхрон: збережений тотал не перерахували після зміни позицій. Ось запит, що знаходить усі такі замовлення по всій базі, а не лише те, що впало в очі:

SELECT o.id, o.order_total, SUM(oi.qty * oi.price_at_purchase) AS real_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.order_total
HAVING o.order_total <> SUM(oi.qty * oi.price_at_purchase);

Порожній результат означав би, що кеш і джерело збіглися скрізь; непорожній — список замовлень для багу з готовим доказом. Що з цього винести: розбіжність двох копій сама по собі нічого не означає — спершу з'ясуй, ці копії за задумом однакові чи різні.

Кейс 3. Інваріант цілісності в автотесті

Найнадійніша перевірка проти розсинхрону й сиріт — не разовий запит руками, а інваріант даних, який ганяється після кожного сценарію, що чіпає замовлення. Драйвер сценарію може бути будь-який (UI чи API); тут важлива сама асерція на стан бази після нього.

import { test, expect } from '@playwright/test';
import { Client } from 'pg';

let db: Client;

test.beforeAll(async () => {
  db = new Client({ connectionString: process.env.DATABASE_URL });
  await db.connect();
});

test.afterAll(async () => {
  await db.end();
});

test('денормалізований total не розходиться з позиціями', async () => {
  const { rows } = await db.query(`
    SELECT o.id FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id, o.order_total
    HAVING o.order_total <> SUM(oi.qty * oi.price_at_purchase)
  `);
  expect(rows, 'знайдено замовлення з розсинхроном total').toEqual([]);
});

test('немає сиріт: кожна позиція вказує на наявний товар', async () => {
  const { rows } = await db.query(`
    SELECT oi.order_id, oi.product_id
    FROM order_items oi
    LEFT JOIN products p ON p.id = oi.product_id
    WHERE p.id IS NULL
  `);
  expect(rows, 'знайдено позиції з неіснуючим товаром').toEqual([]);
});

Що дивитися і чому:

• Оракул — порожній масив рядків. Обидва запити спроєктовані так, що здоровий стан бази дає нуль рядків: HAVING лишає тільки розсинхронені замовлення, WHERE p.id IS NULL — тільки сиріт. Асерт toEqual([]) читається як «жодного порушення інваріанта».
• Повідомлення асерту несе доказ. Другий аргумент expect пояснює, що саме зламалось; коли тест почервоніє в CI, у звіті одразу видно, який інваріант порушено, без реран-полювання.
• Це страховка, а не єдиний рубіж. Такий тест ловить розсинхрон постфактум. Найнадійніше — не дати даним зіпсуватися взагалі, винісши правило в обмеження цілісності: FOREIGN KEY на order_items.product_id зробив би другий тест зайвим, бо база просто не дасть вставити сироту.
• Не плутай знімок із кешем і в тесті. Перевіряти на рівність варто кеш поточного значення (order_total), а не історичний знімок (price_at_purchase): останній за задумом розходиться з каталогом, і асерт на його рівність поточній ціні був би хибним падінням.

Аномалії й надлишковість

• Знаю три аномалії — вставки, оновлення, видалення — і можу пояснити кожну одним прикладом на широкій таблиці замовлень.
• Розумію, що корінь усіх трьох аномалій один — надлишковість (один факт зберігається більш ніж в одному місці), і нормалізація систематично її прибирає, щоб кожен факт жив рівно в одній клітинці.
• Можу пояснити, чому аномалія оновлення — головний тихий генератор багів QA: копії розходяться без жодної помилки.

Функціональні залежності і нормальні форми

• Можу пояснити функціональну залежність A → B («знаючи A, однозначно знаю B») і навести приклад, де зворотне не виконується.
• Знаю, що 1НФ — це атомарність значень і наявність ключа; впізнаю порушення (список у клітинці, phone1/phone2).
• Розумію 2НФ через часткову залежність і те, що вона застосовна лише при складеному ключі.
• Розумію 3НФ через транзитивну залежність (order_id → customer_id → customer_city) і можу показати виправлення.
• Пам'ятаю мнемоніку «the key, the whole key, and nothing but the key» і знаю, що «усього ключа» — це 2НФ, а «нічого, крім ключа» — 3НФ.
• Знаю, що нормальні форми — теорія Кодда, а не частина стандарту SQL: СУБД їх не вмикає, це дисципліна проєктувальника.
• Розумію, що 3НФ — практичний орієнтир «достатньо» для більшості схем, а BCNF/4НФ потрібні рідко.

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

• Можу пояснити денормалізацію як свідомий компроміс «швидкість читання проти ризику розсинхрону».
• Розумію, що денормалізація не помилка — помилка робити її несвідомо чи без механізму синхронізації дублікатів.
• Знаю різницю між знімком на момент часу (price_at_purchase, МАЄ розходитися з джерелом) і кешем поточного значення (order_total, розбіжність = баг), і з'ясовую це перед заведенням багу на розбіжність двох чисел.

Запахи схеми

• Впізнаю ключові запахи: список у клітинці, нумеровані колонки, майже завжди NULL, god-таблиця, відсутній первинний ключ, похідне значення без оновлення, дубльований довідник.
• Розумію, що запах — це не діагноз, а готова гіпотеза для тесту: «а що, якщо ці дві копії розійдуться?».
• Знаю, чому унікальний індекс не рятує від логічних дублікатів: Київ і київ для бази різні рядки.

Запити QA і цілісність

• Розумію, що нормалізована схема зміщує баги в бік цілісності зв'язків (сироти, FK), а денормалізована — у бік розсинхрону дублікатів.
• Можу написати пошук сиріт (LEFT JOIN ... WHERE p.id IS NULL), розсинхрону кешу (GROUP BY ... HAVING збережене <> пораховане) і дублікатів (HAVING COUNT(*) > 1).
• Розумію, що 3НФ не гарантує цілісності зв'язків: без оголошених FOREIGN KEY сироти будуть однаково.
• Знаю, що найнадійніший захист — не тест, а обмеження цілісності в схемі (FOREIGN KEY, UNIQUE, CHECK).