Проектування схеми бази даних веб-застосунку
Схема бази даних — фундамент, який найважче змінювати після запуску. Неправильно нормалізовані таблиці, відсутні FK або невірні типи даних перетворюються на технічний борг, який накопичується роками. Ми стикалися з цим десятки разів: коли проект зростає, а база тріщить по швах — перепроектування обходиться в рази дорожче. На одному проекті клієнт втратив два місяці через відсутність індексів — запити виконувалися по 30 секунд. Після впровадження правильної схеми все полетіло за 50 мілісекунд. У середньому, рефакторинг схеми займає 2-3 дні, але запобігає місяцям переробок. Ми гарантуємо, що після нашої роботи запити стануть у десятки разів швидше.
Основні принципи та типові проблеми
У 80% проектів ми знаходимо одні й ті ж помилки: N+1 запити через відсутність зовнішніх ключів, фрагментація індексів при використанні випадкових UUID, втрата точності у фінансових розрахунках через FLOAT і магічні числа замість enum-ів. Для більшості застосунків достатньо третьої нормальної форми (3NF): кожне поле залежить тільки від первинного ключа, без транзитивних залежностей. Нормалізація допомагає уникнути надлишковості та аномалій оновлення.
Денормалізація виправдана для лічильників (comments_count, likes_count) — замість COUNT JOIN при кожному запиті. Також для кешованих агрегатів (суми замовлень за місяць) і flatten ієрархічних даних для пошуку. Шкідлива, коли дублюються персональні дані або статуси, які часто змінюються.
Чому вибір типу даних — критичне рішення?
Помилки в типах даних — одна з найчастіших причин проблем у продакшені. Ось типові антипатерни:
-- Погано
user_id INT -- переповниться на 2.1 млрд записів
price FLOAT -- втрати точності при фінансових обчисленнях
status INT -- магічні числа, немає domain constraint
created VARCHAR(30) -- сортування рядків замість дат
settings TEXT -- немає структури, немає індексу
-- Добре
user_id BIGINT -- або UUID
price DECIMAL(12, 2) -- точна арифметика
status VARCHAR(20) CHECK (status IN ('draft', 'published', 'archived'))
created TIMESTAMPTZ -- з timezone
settings JSONB -- структурований, індексований
TIMESTAMPTZ зберігає час в UTC і конвертує при читанні згідно TimeZone сесії. TIMESTAMP зберігає "як є" — при зміні timezone сервера дані втрачають сенс.
| Тип даних | Рекомендація |
|---|---|
| INT | BIGINT для PK |
| FLOAT | DECIMAL |
| VARCHAR без CHECK | VARCHAR з CHECK |
Як вибрати первинний ключ: BIGSERIAL чи UUID?
-- 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 генеруються на стороні клієнта або потрібно приховати передбачуваність.
| Тип | Розмір | Продуктивність запису | Глобальна унікальність |
|---|---|---|---|
| SERIAL (8 байт) | 8 байт | Висока | Ні |
| UUID v4 | 16 байт | Середня (фрагментація) | Так |
| ULID | 16 байт | Висока (впорядкований) | Так |
Як уникнути типових помилок при проектуванні схеми?
Ключ до успіху — заздалегідь продумати патерни доступу. Якщо застосунок часто читає кошик з товарами, використовуйте агрегатні поля та уникайте глибоких JOIN. Для історичних даних (замовлення) навмисно денормалізуйте unit_price, щоб зберегти знімок ціни. Завжди перевіряйте, чи підтримує вибраний PK діапазон зростання — BIGSERIAL покриває 9.2 квінтильйона записів, чого вистачить на десятиліття.
Приклад: схема інтернет-магазину
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, -- для full-text search
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, -- delivery address тощо
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 — навмисна денормалізація: ціна продукту зміниться з часом, а історична ціна в замовленні повинна залишитися незмінною.
ON DELETE RESTRICT vs CASCADE — правило: CASCADE тільки коли дочірні записи не мають сенсу без батька (order_items без order). RESTRICT, коли видалення батька має бути явно запобіжено (не можна видалити категорію з товарами).
Які індекси створювати одразу та soft delete
Додаємо одразу при створенні схеми:
-- FK columns — завжди, інакше 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);
-- Partial index для активних записів
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);
Патерн soft delete:
-- Soft delete
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_products_deleted_at ON products (deleted_at) WHERE deleted_at IS NULL;
-- Audit table
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()
);
Partial index по WHERE deleted_at IS NULL — активні записи індексуються окремо. Видалені записи не потрапляють в індекс і не сповільнюють запити.
Процес роботи та що входить
- Аналітика — виявлення бізнес-сутностей, зв'язків, частих запитів.
- Проектування — побудова ER-діаграми, нормалізація, вибір типів.
- Створення DDL — SQL-скрипти з індексами, FK, constraints.
- Документування — опис схеми, коментарі, guide для розробників.
- Аудит — рев'ю існуючої схеми, виявлення проблем, рекомендації.
У результат роботи входить: ER-діаграма (до 15 таблиць) у форматі PlantUML або Draw.io, SQL DDL з індексами та констрейнтами, документація по схемі (README з описом таблиць і полів), рекомендації по міграціях і консультація протягом тижня після здачі.
Строки
Проектування схеми для нового проекту (до 15 таблиць): 1–2 дні. Рев'ю та рефакторинг існуючої схеми: 1–3 дні. Вартість розраховується індивідуально — напишіть нам, і ми оцінимо ваш проект. Замовте проектування схеми — ми врахуємо всі нюанси. Отримайте професійну консультацію, навіть якщо не впевнені в обсязі робіт.