Пошук уроків, статей та іншого контенту
Зрозумійте, як нормалізація усуває дублювання, аномалії оновлення та суперечності в даних.
Нормалізація даних — це спосіб організації таблиць у базі даних, який допомагає:
зменшити дублювання даних;
уникнути суперечностей;
безпечно додавати, змінювати й видаляти записи;
чітко визначити зв’язки між сутностями.
Розглянемо таблицю замовлень, у якій зберігаються всі дані разом:
orders
id | customer_name | customer_email | product_name | price | quantity
---+---------------+----------------+--------------+-------+---------
1 | Олена | olena@mail.com | Клавіатура | 1200 | 1
2 | Олена | olena@mail.com | Миша | 800 | 2
3 | Андрій | andrii@mail.com| Клавіатура | 1200 | 1На перший погляд, така таблиця проста. Але в ній є проблеми:
ім’я та електронна пошта Олени дублюються;
назва й ціна клавіатури дублюються;
зміну ціни потрібно виконувати в кількох рядках;
видалення останнього замовлення може призвести до втрати інформації про товар або клієнта.
Нормалізація розділяє різні сутності на окремі таблиці та встановлює між ними зв’язки.
Припустімо, ціна клавіатури змінилася з 1200 на 1350.
Якщо товар записаний у десяти замовленнях, потрібно змінити ціну в десяти рядках. Якщо один рядок залишиться без змін, база міститиме суперечливі дані.
Ми хочемо додати новий товар, але ще не маємо жодного замовлення на нього.
У таблиці, де товари та замовлення зберігаються разом, може не бути зручного місця для такого товару. Доведеться створити неповний запис замовлення або використовувати NULL.
Якщо видалити останнє замовлення клієнта, можна випадково втратити і єдину інформацію про цього клієнта.
Клієнт і замовлення — це різні сутності, тому їх краще зберігати окремо.
Таблиця перебуває у першій нормальній формі, якщо:
кожен стовпець містить один тип значення;
у комірці зберігається одне значення, а не список;
у таблиці немає повторюваних груп стовпців;
кожен рядок можна однозначно ідентифікувати.
Поганий приклад:
id | customer_name | products
---+---------------+-------------------------
1 | Олена | Клавіатура, МишаУ стовпці products зберігається список товарів. Це ускладнює пошук, підрахунок кількості та зміну окремого товару.
Краще зберігати кожен товар окремим рядком або винести товари в окрему таблицю:
order_id | product_id | quantity
----------+------------+---------
1 | 1 | 1
1 | 2 | 2Тепер кожне значення є окремим і його можна обробляти за допомогою звичайних SQL-запитів.
Таблиця перебуває у другій нормальній формі, якщо:
вона вже відповідає першій нормальній формі;
кожен неключовий стовпець залежить від усього первинного ключа, а не лише від його частини.
Це особливо важливо для таблиць зі складеним первинним ключем.
Наприклад:
order_items
order_id | product_id | product_name | quantity
---------+------------+--------------+---------
1 | 10 | Клавіатура | 1
1 | 20 | Миша | 2Якщо первинний ключ складається з (order_id, product_id), то:
quantity залежить від усього ключа: від конкретного замовлення і конкретного товару;
product_name залежить лише від product_id.
Отже, product_name не повинен зберігатися в order_items. Його потрібно перенести в таблицю products.
products
id | name
---+------------
10 | Клавіатура
20 | МишаА в order_items залишаться дані, що стосуються саме позиції замовлення:
order_id | product_id | quantity
---------+------------+---------
1 | 10 | 1
1 | 20 | 2Таблиця перебуває у третій нормальній формі, якщо:
вона відповідає другій нормальній формі;
неключові стовпці не залежать один від одного.
Розглянемо таблицю:
customers
id | name | city | postal_code
---+-------+------------+------------
1 | Олена | Київ | 01001Якщо поштовий індекс визначає місто, то city фактично залежить від postal_code, а не безпосередньо від id.
Залежності виглядають так:
id → postal_code → cityЦе називають транзитивною залежністю. Щоб уникнути її, можна винести поштові індекси в окрему таблицю:
postal_codes
postal_code | city
------------+------
01001 | КиївА в таблиці клієнтів залишити посилання на поштовий індекс:
customers
id | name | postal_code
---+-------+------------
1 | Олена | 01001У простих прикладах достатньо запам’ятати:
1NF: одне поле — одне значення;
2NF: поле залежить від усього ключа;
3NF: поле не залежить від іншого неключового поля.
Для замовлень можна виділити такі сутності:
клієнти;
товари;
замовлення;
позиції замовлення.
Їм відповідають таблиці:
customers;
products;
orders;
order_items.
Наведений скрипт створює нормалізовану схему та додає приклад даних:
-- Видаляємо таблиці, щоб скрипт можна було запускати повторно
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE
);
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL
REFERENCES customers(id),
created_at date NOT NULL DEFAULT CURRENT_DATE
);
CREATE TABLE order_items (
order_id integer NOT NULL
REFERENCES orders(id) ON DELETE CASCADE,
product_id integer NOT NULL
REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0),
-- Один товар може зустрічатися в замовленні лише один раз
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers (name, email)
VALUES
('Олена', 'olena@example.com'),
('Андрій', 'andrii@example.com');
INSERT INTO products (name, price)
VALUES
('Клавіатура', 1200.00),
('Миша', 800.00);
INSERT INTO orders (customer_id)
VALUES
(1),
(2);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1);Після нормалізації дані розподілені по кількох таблицях. Щоб отримати їх разом, використовують JOIN:
SELECT
o.id AS order_id,
c.name AS customer_name,
c.email,
p.name AS product_name,
p.price,
oi.quantity,
p.price * oi.quantity AS item_total
FROM orders AS o
JOIN customers AS c
ON c.id = o.customer_id
JOIN order_items AS oi
ON oi.order_id = o.id
JOIN products AS p
ON p.id = oi.product_id
ORDER BY o.id, p.name;Результат міститиме інформацію про клієнта, товар, кількість і суму позиції, хоча ці дані зберігаються в різних таблицях.
У нормалізованій схемі:
ім’я та email клієнта зберігаються один раз у customers;
назва й ціна товару зберігаються один раз у products;
замовлення посилається на клієнта через customer_id;
позиція замовлення посилається на товар через product_id.
Якщо email Олени змінився, достатньо виконати один запит:
UPDATE customers
SET email = 'olena.new@example.com'
WHERE id = 1;Якщо ціна клавіатури змінилася:
UPDATE products
SET price = 1350.00
WHERE id = 1;Не потрібно шукати й оновлювати всі замовлення, де ця клавіатура використовувалася.
Нормалізація тісно пов’язана з ключами.
Первинний ключ однозначно ідентифікує рядок:
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEYУ таблиці customers значення id відрізняє одного клієнта від іншого.
Зовнішній ключ посилається на рядок іншої таблиці:
customer_id integer REFERENCES customers(id)Це означає, що замовлення може посилатися лише на клієнта, який існує в таблиці customers.
Для таблиці order_items використано складений первинний ключ:
PRIMARY KEY (order_id, product_id)Він забороняє додати один і той самий товар у те саме замовлення двічі.
Не варто створювати одну велику таблицю лише для того, щоб рідше використовувати JOIN.
Окремі таблиці доречні, коли вони описують різні сутності:
клієнт не є замовленням;
товар не є позицією замовлення;
замовлення може містити багато товарів;
один клієнт може мати багато замовлень.
Нормалізована схема може містити більше таблиць, але кожен факт зберігається в одному логічному місці.
Погано:
product_ids = '10,20,30'Такі дані складно перевіряти, фільтрувати та з’єднувати з іншими таблицями. Для зв’язку замовлення з товарами потрібна окрема таблиця на кшталт order_items.
Погано зберігати в кожному рядку замовлення:
customer_name
customer_email
product_name
product_priceЦе створює дублювання та ризик суперечностей. Краще зберігати ідентифікатори customer_id і product_id.
Якщо не додати PRIMARY KEY, FOREIGN KEY, NOT NULL і UNIQUE, база даних не зможе достатньо добре захистити дані від помилок.
Наприклад:
email text NOT NULL UNIQUEгарантує, що email клієнта не буде порожнім і не повторюватиметься.
Нормалізація не означає, що кожне значення потрібно виносити в окрему таблицю. Таблиці мають відповідати реальним сутностям і зв’язкам предметної області.
Спочатку потрібно усунути дублювання та аномалії, а не механічно створювати якомога більше таблиць.
Нормалізація організовує дані так, щоб зменшити дублювання та суперечності.
Перша нормальна форма вимагає атомарних значень і відсутності списків у комірках.
Друга нормальна форма усуває залежність неключових полів лише від частини складеного ключа.
Третя нормальна форма усуває залежність неключових полів одне від одного.
Різні сутності, наприклад клієнти, товари та замовлення, потрібно зберігати в окремих таблицях.
PRIMARY KEY і FOREIGN KEY допомагають підтримувати зв’язки між нормалізованими таблицями.
Нормалізована база даних полегшує вставку, оновлення та видалення даних без аномалій.