Нормальные формы, принципы нормализации, когда и зачем денормализовать
Нормализация — это процесс организации данных в базе для уменьшения избыточности и улучшения целостности. Но иногда денормализация оправдана для производительности. Разберёмся, когда применять каждый подход.
Представьте таблицу заказов без нормализации:
-- Плохой дизайн: избыточность данных
CREATE TABLE orders_denormalized (
order_id INTEGER,
customer_name TEXT,
customer_email TEXT,
customer_phone TEXT,
customer_address TEXT,
product_name TEXT,
product_price NUMERIC,
quantity INTEGER,
order_date TIMESTAMP
);Проблемы такого подхода:
| Проблема | Описание |
|---|---|
| Избыточность | Данные клиента дублируются в каждом заказе |
| Аномалии вставки | Нельзя добавить клиента без заказа |
| Аномалии обновления | При смене email нужно обновить все записи клиента |
| Аномалии удаления | При удалении последнего заказа теряются данные клиента |
Требования:
-- Нарушает 1NF: неатомарное значение
CREATE TABLE users_bad (
id SERIAL,
name TEXT,
phones TEXT -- "555-1234,555-5678" — несколько значений в одном поле
);
-- Соответствует 1NF
CREATE TABLE users_good (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE user_phones (
user_id INTEGER REFERENCES users(id),
phone TEXT,
PRIMARY KEY (user_id, phone)
);-- Нарушает 1NF: повторяющиеся группы
CREATE TABLE orders_bad (
order_id SERIAL,
product1 TEXT,
price1 NUMERIC,
product2 TEXT,
price2 NUMERIC,
product3 TEXT,
price3 NUMERIC
);
-- Соответствует 1NF
CREATE TABLE orders_good (
order_id SERIAL PRIMARY KEY,
order_date TIMESTAMP
);
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(order_id),
product TEXT,
price NUMERIC,
quantity INTEGER
);Требования:
-- Нарушает 2NF: product_price зависит только от product_id, а не от всего ключа
CREATE TABLE order_items_bad (
order_id INTEGER,
product_id INTEGER,
product_name TEXT, -- зависит только от product_id
product_price NUMERIC, -- зависит только от product_id
quantity INTEGER, -- зависит от обоих ключей
PRIMARY KEY (order_id, product_id)
);
-- Соответствует 2NF
CREATE TABLE order_items_good (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name TEXT,
product_price NUMERIC
);Требования:
-- Нарушает 3NF: city зависит от postal_code, а не от customer_id
CREATE TABLE customers_bad (
customer_id SERIAL PRIMARY KEY,
name TEXT,
postal_code TEXT,
city TEXT, -- транзитивная зависимость: customer_id → postal_code → city
country TEXT -- транзитивная зависимость
);
-- Соответствует 3NF
CREATE TABLE customers_good (
customer_id SERIAL PRIMARY KEY,
name TEXT,
postal_code TEXT
);
CREATE TABLE postal_codes (
postal_code TEXT PRIMARY KEY,
city TEXT,
country TEXT
);Усиленная версия 3NF. Таблица находится в BCNF, если для каждой нетривиальной функциональной зависимости X → Y, X является суперключом.
-- Пример нарушения BCNF
-- Предположим, что в таблице один преподаватель ведёт только один предмет
CREATE TABLE teaching (
student_id INTEGER,
subject TEXT,
teacher TEXT,
PRIMARY KEY (student_id, subject)
);
-- Зависимость: teacher → subject (преподаватель ведёт один предмет)
-- Но teacher не является ключом → нарушение BCNF
-- Решение: разделение на две таблицы
CREATE TABLE teaches (
teacher TEXT PRIMARY KEY,
subject TEXT
);
CREATE TABLE enrollment (
student_id INTEGER,
teacher TEXT,
PRIMARY KEY (student_id, teacher)
);Нормализация не всегда оптимальна. Рассмотрим случаи денормализации:
-- Нормализовано (много JOIN для частых запросов)
SELECT o.id, o.total, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '7 days';
-- Денормализовано (данные клиента дублируются в orders)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
customer_name TEXT, -- дублирование
customer_email TEXT, -- дублирование
total NUMERIC,
created_at TIMESTAMP
);
-- Запрос без JOIN
SELECT id, total, customer_name, customer_email
FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';Когда оправдано:
-- Денормализация: храним агрегаты
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT,
total_orders INTEGER DEFAULT 0, -- кэш
total_spent NUMERIC DEFAULT 0 -- кэш
);
-- Триггер для обновления агрегатов
CREATE OR REPLACE FUNCTION update_customer_stats()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE customers
SET total_orders = total_orders + 1,
total_spent = total_spent + NEW.total
WHERE id = NEW.customer_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE customers
SET total_orders = total_orders - 1,
total_spent = total_spent - OLD.total
WHERE id = OLD.customer_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_stats
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_customer_stats();Когда оправдано:
-- Создаём материализованное представление
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
DATE_TRUNC('month', order_date) as month,
product_category,
SUM(total) as total_sales,
COUNT(*) as order_count
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY DATE_TRUNC('month', order_date), product_category;
-- Быстрый запрос к агрегатам
SELECT * FROM monthly_sales
WHERE month >= '2026-01-01';
-- Обновление данных (вручную или по расписанию)
REFRESH MATERIALIZED VIEW monthly_sales;
-- Обновление без блокировки чтений (PostgreSQL 9.4+)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;-- Денормализация: храним поисковый вектор
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
search_vector TSVECTOR -- денормализованный поисковый индекс
);
-- Триггер для обновления вектора
CREATE TRIGGER trg_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.russian', title, content);
-- Быстрый поиск
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgres & база');Проектирование БД:
1. Нормализуйте до 3NF
2. Измерьте производительность
3. Денормализуйте только узкие места
COMMENT ON COLUMN orders.customer_name IS
'Денормализовано из customers.name для ускорения отчётов. Обновляется триггером.';-- Автоматическое обновление денормализованных данных
CREATE TRIGGER trg_sync_customer_name
AFTER UPDATE OF name ON customers
FOR EACH ROW
EXECUTE FUNCTION sync_customer_name_in_orders();| Нагрузка | Рекомендация |
|---|---|
| OLTP (много записи) | Строгая нормализация |
| OLAP (много чтения) | Агрегаты, материализованные представления |
| Смешанная | Гибридный подход |
| Критерий | Нормализация | Денормализация |
|---|---|---|
| Избыточность | Минимальная | Высокая |
| Целостность | Гарантирована БД | Требует триггеров/кода |
| Запись | Быстрая | Медленнее (обновление дублей) |
| Чтение | Медленнее (JOIN) | Быстрее (меньше JOIN) |
| Сложность | Проще для понимания | Сложнее поддержка |
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
email TEXT
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
title TEXT,
content TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INTEGER REFERENCES posts(id),
user_id INTEGER REFERENCES users(id),
content TEXT,
created_at TIMESTAMP DEFAULT NOW()
);-- Добавляем денормализованные данные
ALTER TABLE posts ADD COLUMN author_name TEXT;
ALTER TABLE posts ADD COLUMN comments_count INTEGER DEFAULT 0;
ALTER TABLE comments ADD COLUMN author_name TEXT;
-- Триггер для подсчёта комментариев
CREATE OR REPLACE FUNCTION update_comments_count()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE posts SET comments_count = comments_count + 1
WHERE id = NEW.post_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE posts SET comments_count = comments_count - 1
WHERE id = OLD.post_id;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_comments_count
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comments_count();Теперь вы понимаете принципы нормализации и знаете, когда можно денормализовать данные. В следующей теме вы изучите индексы — мощный инструмент ускорения запросов.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.