Структурируй схемы от 1NF до 3NF, чтобы устранить аномалии, и пойми, когда денормализовать осознанно.
Открыть этот урок в KodokonПлохая схема обходится дорого годами. Нормализация нацелена на одно: чтобы каждый факт хранился только один раз и три аномалии исчезли: аномалия обновления (менять один e-mail в десяти местах), вставки (нельзя добавить товар без заказа) и удаления (удаление последнего заказа стирает клиента). Посмотри на эту нарочно испорченную схему.
CREATE TABLE bad_orders (
id INTEGER PRIMARY KEY,
customer_name TEXT,
customer_email TEXT,
product_names TEXT,
total REAL
);
INSERT INTO bad_orders
(customer_name, customer_email,
product_names, total)
VALUES
('Alice', 'alice@mail.com',
'keyboard,mouse', 74.0),
('Alice', 'alice@mail.co',
'monitor', 199.0);Первые три нормальные формы на практике. 1NF: атомарные значения - product_names = 'keyboard,mouse' нарушает её, из-за чего любой WHERE или JOIN по товарам становится неудобным. 2NF (составной ключ): каждая колонка зависит от всего ключа - хранить каталожную цену товара в строке заказа, которая идентифицирована парой (заказ, товар), нарушает её, потому что цена зависит только от товара. 3NF: ни одна неключевая колонка не зависит от другой неключевой - customer_email, зависящий от customer_name, нарушает её; отсюда и дубль alice@mail.co. Вот исправленная схема.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);
CREATE TABLE purchases (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES customers(id)
);
CREATE TABLE purchase_items (
purchase_id INTEGER NOT NULL
REFERENCES purchases(id),
product_id INTEGER NOT NULL
REFERENCES products(id),
quantity INTEGER NOT NULL,
PRIMARY KEY (purchase_id, product_id)
);PRAGMA foreign_keys = ON;
INSERT INTO customers (name, email)
VALUES ('Alice', 'alice@mail.com');
INSERT INTO products (name, price)
VALUES ('keyboard', 49.0), ('mouse', 25.0);
INSERT INTO purchases (customer_id) VALUES (1);
INSERT INTO purchase_items
(purchase_id, product_id, quantity)
VALUES (1, 1, 1), (1, 2, 1);
SELECT c.name, p.name AS product,
i.quantity, p.price
FROM purchase_items AS i
JOIN purchases AS pu ON pu.id = i.purchase_id
JOIN customers AS c ON c.id = pu.customer_id
JOIN products AS p ON p.id = i.product_id;Так когда же стоит денормализовать? Когда чтения становятся измеренным узким местом: соединение пяти таблиц на важном экране, агрегат, который пересчитывается при каждой отрисовке. Честные паттерны: колонка-кэш (purchases.total, зафиксированный в момент заказа - и это законно: заплаченная цена есть исторический факт, отличный от каталожной цены), счётчик, который поддерживает триггер, или отчётная таблица, перестраиваемая пакетно. Цена вопроса: каждая копия может разойтись, и теперь согласованность гарантирует твой код, а не движок.