Проектування схеми бази даних для веб-додатків
Схема бази даних — це фундамент, який складніше змінювати після запуску. Неправильно нормалізовані таблиці, відсутні зовнішні ключі або некоректні типи даних перетворюються на технічний борг, який накопичується роками. Розберемо принципи та конкретні рішення для типових веб-додатків.
Нормалізація та денормалізація
Третя нормальна форма (3NF) — розумний рівень для більшості додатків. Кожне поле залежить тільки від первинного ключа, без транзитивних залежностей.
Коли денормалізація виправдана:
- Лічильники (
comments_count,likes_count) — замість COUNT JOIN для кожного запиту. - Кешовані агрегати для звітів (суми замовлень за місяць).
- Спрощення ієрархічних даних для пошуку.
Коли денормалізація шкідлива:
- Персональні дані, дубльовані в кількох таблицях.
- Статуси, які часто змінюються.
Типи даних: часті помилки
-- Погано
user_id INT -- переповнюється на 2,1 млрд записів
price FLOAT -- втрата точності при фінансових обчисленнях
status INT -- магічні числа, немає обмеження домену
created VARCHAR(30) -- сортування рядків замість дат
settings TEXT -- немає структури, не індексується
-- Добре
user_id BIGINT -- або UUID
price DECIMAL(12, 2) -- точна арифметика
status VARCHAR(20) CHECK (status IN ('draft', 'published', 'archived'))
created TIMESTAMPTZ -- з часовою зоною
settings JSONB -- структурована, індексована
TIMESTAMPTZ зберігає час у UTC і конвертує при читанні відповідно до TimeZone сесії. TIMESTAMP зберігає "як є" — при зміні часової зони сервера дані втрачають значення.
Первинні ключі: SERIAL vs UUID vs ULID
-- SERIAL (автоінкремент): простий, компактний (8 байт), передбачуваний
id BIGSERIAL PRIMARY KEY
-- UUID v4: унікальний глобально, але 16 байт, випадковий порядок = фрагментація індексу
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- ULID через pg_ulid або генерацію на стороні додатку:
-- лексикографічно сортується за часом, 16 байт
id UUID PRIMARY KEY DEFAULT uuid_generate_v7() -- PostgreSQL 17+
Для більшості веб-додатків BIGSERIAL — оптимальний вибір. UUID потрібен, коли ID генеруються на стороні клієнта або потрібно приховати передбачуваність.
Приклад: схема інтернет-магазину
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL,
sort_order INT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
slug VARCHAR(520) NOT NULL UNIQUE,
category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
price DECIMAL(12, 2) NOT NULL CHECK (price > 0),
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'published', 'archived')),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
specs JSONB,
search_vector TSVECTOR, -- для повнотекстового пошуку
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
total DECIMAL(12, 2) NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'USD',
meta JSONB, -- адреса доставки тощо
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(12, 2) NOT NULL, -- ціна на момент покупки
UNIQUE (order_id, product_id)
);
unit_price в order_items — навмисна денормалізація: ціна продукту змінюватиметься з часом, але історична ціна в замовленні повинна залишитися незмінною.
ON DELETE RESTRICT vs CASCADE — правило: CASCADE тільки коли дочірні записи не мають сенсу без батька (order_items без order). RESTRICT, коли видалення батька повинне бути явно заборонено (не можна видалити категорію з товарами).
Індекси: що додавати при проектуванні
Додаємо одразу при створенні схеми:
-- FK колонки — завжди, інакше DELETE батька = seq scan дочірної таблиці
CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
-- Частих фільтрів
CREATE INDEX idx_products_status_created ON products (status, created_at DESC);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
-- Частковий індекс для активних записів
CREATE INDEX idx_products_published ON products (category_id, created_at DESC)
WHERE status = 'published';
-- GIN для JSONB
CREATE INDEX idx_products_specs ON products USING GIN (specs);
Аудит та м'яке видалення
Паттерн м'якого видалення:
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_products_deleted_at ON products (deleted_at) WHERE deleted_at IS NULL;
Частковий індекс по WHERE deleted_at IS NULL — активні записи індексуються окремо. Видалені записи не потрапляють в індекс і не сповільнюють запити.
Паттерн аудиту через триггер:
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
row_id BIGINT NOT NULL,
operation CHAR(1) NOT NULL CHECK (operation IN ('I', 'U', 'D')),
old_data JSONB,
new_data JSONB,
changed_by BIGINT REFERENCES users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Графіки
Проектування схеми для нового проекту (до 15 таблиць, ER-діаграма, SQL DDL, обговорення індексів та стратегій FK): 1–2 дні. Огляд та рефакторинг існуючої схеми з виявленням проблем (неправильні типи, відсутні FK, надмірні індекси): 1–3 дні залежно від обсягу.