Question 1

Що таке нормалізація бази даних і нормальні форми?

Accepted Answer

Оля змінила email - треба оновити всі її замовлення. Пропустили одне - дані суперечать одне одному.
Нормальні форми (основні три):

1НФ - у кожній клітинці одне атомарне значення; немає повторюваних груп («product1, product2, product3» чи список через кому в одній колонці).
2НФ - 1НФ, і кожна неключова колонка залежить від усього складеного ключа, а не від частини. У таблиці (order_id, product_id, product_name, qty) назва товару залежить лише від product_id - її місце в таблиці товарів.
3НФ - 2НФ, і неключові колонки не залежать одна від одної (немає транзитивних залежностей). orders (id, customer_id, customer_email) - email залежить від клієнта, а не від замовлення.

Зверніть увагу на price_at_purchase: ціна в момент покупки - окремий факт, а не дублювання. Якщо завтра ціна товару зміниться, старе замовлення не має змінитися.
Практичне правило: проєктувати в 3НФ за замовчуванням і свідомо денормалізувати там, де виміряно, що це потрібно для швидкодії. Вищі форми (BCNF, 4НФ, 5НФ) у прикладних застосунках потрібні рідко.
Докладніше в документації: Database normalization

Question 2

Як змоделювати зв'язок «багато-до-багатьох» у реляційній базі?

Accepted Answer

Колонка не може посилатися на кілька рядків іншої таблиці, тож зв'язок «багато-до-багатьох» (статті ↔ теги, студенти ↔ курси) реалізують проміжною таблицею з двома зовнішніми ключами.
CREATE TABLE posts (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text NOT NULL);
CREATE TABLE tags  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE);

CREATE TABLE post_tag (
    post_id bigint NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    tag_id  bigint NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX post_tag_tag_id_idx ON post_tag (tag_id);

Ключові деталі:

Складений первинний ключ (post_id, tag_id) - не дає прив'язати той самий тег двічі і водночас є індексом для «теги статті».
Окремий індекс на tag_id - для зворотного напрямку «статті з тегом». Складений ключ починається з post_id, тож для пошуку за tag_id він не допоможе.
ON DELETE CASCADE - видалили статтю, зник і зв'язок. Без цього видалення статті падатиме на зовнішньому ключі.

Атрибути зв'язку. Якщо в самого зв'язку є дані - коли тег додано, хто додав, роль користувача в команді, - вони живуть у проміжній таблиці:
CREATE TABLE team_user (
    team_id bigint REFERENCES teams (id),
    user_id bigint REFERENCES users (id),
    role text NOT NULL DEFAULT 'member',
    joined_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (team_id, user_id)
);

У Laravel: belongsToMany з таблицею post_tag (назви моделей в однині, за абеткою), withPivot('role') і withTimestamps() для атрибутів, attach/detach/sync для зміни зв'язків.
Коли проміжна таблиця стає сутністю: якщо в неї з'явилися власна поведінка й багато полів (членство з історією й статусами), варто зробити з неї повноцінну модель з власним id.
Докладніше в документації: Зовнішні ключі

Question 3

Природний чи сурогатний первинний ключ - що обрати?

Accepted Answer

Природний ключ - значення, що вже унікальне в реальному світі: email, ІПН, код валюти UAH, ISBN, номер телефону.
Сурогатний ключ - штучний ідентифікатор без бізнес-змісту: id з послідовності чи UUID.

Проблеми природних ключів:

Вони змінюються. Email змінюють, телефони переносять, номери документів виправляють. Зміна первинного ключа - каскадне оновлення всіх таблиць, що на нього посилаються.
Унікальність, яка виявляється не зовсім унікальною: один номер телефону в сім'ї, повторно використаний email після видалення облікового запису, дублі ІПН через помилки введення.
Розмір і швидкість: довгий рядок як ключ і в кожному зовнішньому ключі займає більше місця, ніж bigint.
Розкриття даних: email в URL і логах.

Тому типова практика:

Сурогатний первинний ключ для зв'язків між таблицями;
Унікальне обмеження на природний ключ, щоб правило бізнесу все одно перевірялося базою:

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    CONSTRAINT users_email_unique UNIQUE (email)
);

Коли природний ключ доречний:

Довідники зі стабільними стандартними кодами: валюти (UAH), країни (UA), мови (uk). Код зручно читати в даних, і він не змінюється.
Проміжні таблиці «багато-до-багатьох» - складений ключ з двох зовнішніх ключів природний за своєю суттю.

Сурогатний ключ не замінює унікальність: таблиця з лише id і без унікального обмеження на бізнес-ключ дозволить створити двох однакових клієнтів, і це найпоширеніша причина дублікатів у даних.
Докладніше в документації: Первинні ключі

Question 4

Коли денормалізація виправдана і як не зламати узгодженість?

Accepted Answer

Денормалізація - свідоме дублювання даних заради швидкодії читання: зберігати похідне чи скопійоване значення, щоб не обчислювати його й не з'єднувати таблиці на кожен запит.
Типові випадки:

Лічильники: posts.comments_count замість count(*) по коментарях на кожній сторінці списку.
Агрегати: orders.total замість суми по позиціях; рейтинг товару.
Копії для історії - насправді не денормалізація, а окремий факт: order_items.price в момент покупки, адреса доставки в замовленні.
Дані для пошуку й списків: ім'я автора в рядку статті, щоб показати список без JOIN.
Звітні таблиці й матеріалізовані подання.

Коли виправдано: читань набагато більше, ніж записів; обчислення чи JOIN справді дорогі (виміряно, а не здається); допустима невелика затримка актуальності.
Ціна - узгодженість. Тепер одне значення живе в двох місцях, і їх треба синхронізувати. Способи:

Тригер у базі - надійно, бо спрацює для будь-якого коду, що змінює дані:

UPDATE posts SET comments_count = comments_count + 1 WHERE id = NEW.post_id;

У застосунку в тій самій транзакції (Laravel: increment() поруч зі створенням коментаря). Простіше, але масові зміни в обхід моделей пропустять оновлення.
Асинхронно - подія в черзі перераховує агрегат. Допускає затримку.
Періодичне перерахування як страховка - нічне вирівнювання лічильників з реальністю.

Конкурентність: comments_count = comments_count + 1 атомарний; а «прочитати лічильник, додати один, записати» - втрачає оновлення при паралельних запитах.
Правило: спершу нормалізована схема й індекси. Денормалізувати конкретні місця, коли профілювання показало проблему, - і разом з механізмом підтримки узгодженості, а не «потім допишемо».
Докладніше в документації: Denormalization

Question 5

Як зберігати історію змін записів для аудиту?

Accepted Answer

Задача: знати, хто, коли й що змінив - для розслідувань, вимог регуляторів, відновлення помилково змінених даних. 1. Таблиця аудиту з тригером у базі: CREATE TABLE audit_log ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, table_name text NOT NULL, record_id bigint NOT NULL, action text NOT NULL, -- INSERT / UPDATE / DELETE old_data jsonb, new_data jsonb, changed_by text DEFAULT current_setting('app.user_id', true), changed_at timestamptz NOT NULL DEFAULT now() ); CREATE FUNCTION audit_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN INSERT INTO audit_log (table_name, record_id, action, old_data, new_data) VALUES (TG_TABLE_NAME, coalesce(NEW.id, OLD.id), TG_OP, CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END, CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END); RETURN NULL; END $$; CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders FOR EACH ROW EXECUTE FUNCTION audit_trigger(); Плюс: ловить усі зміни - з застосунку, міграцій, ручних SQL-запитів. Мінус: база не знає користувача застосунку - його передають змінною сесії (SET LOCAL app.user_id), що потребує дисципліни. 2. Аудит у застосунку - події моделей (у Laravel: spatie/laravel-activitylog, owen-it/laravel-auditing): Плюс: природно знає користувача, запит, IP, контекст бізнес-дії («скасування замовлення», а не просто «змінено status»). Мінус: пропускає масові оновлення в обхід моделей (Model::where(...)->update()) і зміни поза застосунком. 3. Історичні таблиці / темпоральні дані - кожна версія запису з періодом дії (valid_from, valid_to). Дає відповідь на «яким був запис на 1 березня» простим запитом. Що врахувати: Обсяг: таблиця аудиту росте швидше за основні. Партиціонування за часом і політика зберігання. Чутливі дані: у знімках змін опиняться паролі, токени, персональні дані - їх треба виключати чи маскувати. Незмінність: права лише на INSERT в таблицю аудиту, без UPDATE/DELETE для ролі застосунку. Зберігати лише змінені поля (diff), якщо повні знімки занадто великі. Докладніше в документації: Тригери в PL/pgSQL

Question 6

Які плюси й мінуси м'якого видалення (soft delete)?

Accepted Answer

Soft delete - замість видалення рядка позначати його як видалений: deleted_at timestamptz. Застосунок фільтрує такі рядки в запитах (у Laravel - трейт SoftDeletes з глобальним скоупом).
Плюси:

Відновлення помилково видаленого без бекапу.
Історія й аудит: видно, що існувало й коли зникло.
Посилання не ламаються: старі замовлення досі посилаються на «видалений» товар.

Мінуси й підводні камені:

Унікальність ламається. Користувач видалив обліковий запис, а зареєструватися знову з тим самим email не може: рядок досі є, і UNIQUE (email) спрацьовує. Рішення - частковий унікальний індекс:

CREATE UNIQUE INDEX users_email_active ON users (email) WHERE deleted_at IS NULL;

Зовнішні ключі не знають про «видалення»: база не завадить створити замовлення на «видаленого» клієнта, а ON DELETE CASCADE не спрацює. Каскад доводиться реалізовувати в коді.
Забуті фільтри: будь-який запит в обхід ORM (сирий SQL, звіти, аналітика, інший сервіс) бачить видалені рядки. Звідси витоки даних і неправильні цифри.
Ростуть таблиці й індекси, і кожен запит несе додаткову умову. Індекси варто робити частковими (WHERE deleted_at IS NULL).
Право на видалення даних (GDPR): «видалене» має бути видалене насправді. Soft delete не скасовує обов'язку стерти персональні дані.

Альтернативи:

Архівна таблиця: при видаленні рядок переноситься в deleted_users тригером чи кодом. Основна таблиця лишається чистою, унікальність і ключі працюють нормально.
Статус замість «видалення» (archived, closed), якщо це насправді бізнес-стан, а не видалення.
Аудит-лог зі знімком видаленого запису - для відновлення й історії.

Правило: soft delete - не дефолт для кожної таблиці. Він доречний там, де відновлення й історія справді потрібні, і з продуманими частковими індексами й очищенням.
Докладніше в документації: Часткові індекси

Question 7

Чим погані поліморфні зв'язки з погляду бази даних і які є альтернативи?

Accepted Answer

У Laravel це morphTo / morphMany - зручно: один механізм коментарів, тегів, вкладень для будь-яких моделей.
Проблеми з погляду бази:

Немає зовнішнього ключа. База не може гарантувати, що commentable_id = 42 справді існує в таблиці, зазначеній у commentable_type. Видалили пост - коментарі лишилися «сиротами»; каскадне видалення - лише кодом.
Тип - рядок з іменем класу застосунку. Перейменували модель чи простір імен - дані в базі зламалися (у Laravel рятує Relation::enforceMorphMap() з короткими псевдонімами).
З'єднання незручні: не можна зробити один JOIN - лише окремі для кожного типу, UNION чи CASE.
Індекс потрібен складений (commentable_type, commentable_id), і він більший через рядок типу.

Альтернативи:
1. Окремі колонки на кожен тип з перевіркою, що заповнена рівно одна:
CREATE TABLE comments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id  bigint REFERENCES posts (id) ON DELETE CASCADE,
    video_id bigint REFERENCES videos (id) ON DELETE CASCADE,
    body text NOT NULL,
    CHECK (num_nonnulls(post_id, video_id) = 1)
);

Справжні зовнішні ключі й каскади. Підходить, коли типів кілька й їх перелік стабільний.
2. Окремі проміжні таблиці: post_comments, video_comments - сам коментар без зв'язку, а зв'язок - у таблиці для кожного типу.
3. Спільний «батьківський» запис: таблиця commentable_entities, на яку посилаються і пости, і відео, а коментарі посилаються на неї. Елегантно, але складніше.
Коли поліморфні зв'язки нормальні: «легкі» універсальні речі (теги, лайки, журнал активності), де цілісність не критична, а типів багато й їх кількість зростає. Для критичних даних (платежі, документи) - краще варіанти зі справжніми ключами.
Докладніше в документації: Обмеження

Question 8

Що таке модель EAV і чому її уникають?

Accepted Answer

Привабливо для товарів з різними характеристиками (одяг має розмір, а холодильник - об'єм): нові атрибути додаються без зміни схеми. Так влаштований, наприклад, Magento.
Чому EAV уникають:

Немає типів: value - текст для всього. Числа порівнюються як рядки ('10' < '9'), дати - теж, і будь-яке значення можна записати в будь-який атрибут.
Немає обмежень: NOT NULL, діапазони, зовнішні ключі для значень - неможливо описати базою.
Запити жахливі: «червоні товари вагою до 2 кг» - кілька самоз'єднань тієї самої таблиці чи GROUP BY з умовами в HAVING. Кожен новий фільтр - ще один JOIN.
Продуктивність падає з ростом таблиці атрибутів, а індекси допомагають слабо.
Звіти й аналітика вимагають «розвертати» рядки в колонки.

Альтернативи:

jsonb для змінних атрибутів - найчастіша сучасна заміна:

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL,      -- спільні поля - звичайні колонки
    attributes jsonb NOT NULL DEFAULT '{}'
);

SELECT * FROM products WHERE attributes @> '{"color": "red"}';

Зберігаються типи JSON (числа - числами), є GIN-індекси, документ читається одним рядком.

Окремі таблиці для типів сутностей (наслідування таблиць: спільна products + product_clothing, product_appliances), коли типів небагато і їхні атрибути стабільні.
Звичайні колонки - якщо атрибутів насправді десяток і вони відомі заздалегідь.

Коли EAV ще має сенс: атрибути визначають самі користувачі в адмінці, їх тисячі, і потрібна метаінформація про кожен атрибут (тип, одиниці, порядок показу). Тоді EAV роблять з типізованими колонками значень (value_int, value_text, value_date) і таблицею-описом атрибутів.
Докладніше в документації: Entity-attribute-value model

Question 9

Як спроєктувати базу для мультитенантного застосунку?

Accepted Answer

Три основні моделі, від найпростішої до найізольованішої.
1. Спільні таблиці з колонкою tenant_id:
CREATE TABLE projects (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL REFERENCES tenants (id),
    name text NOT NULL,
    UNIQUE (tenant_id, name)
);
CREATE INDEX projects_tenant_idx ON projects (tenant_id);

Плюси: одна схема, прості міграції, ефективне використання ресурсів, легко рахувати аналітику по всіх тенантах.
Мінуси: головний ризик - забутий фільтр WHERE tenant_id = ?, і один клієнт бачить дані іншого. Захист - глобальні скоупи в застосунку, Row-Level Security у PostgreSQL, тести на ізоляцію. «Галасливий» тенант навантажує всіх.

2. Схема на тенанта (tenant_42.projects):

Плюси: ізоляція на рівні об'єктів бази - запит без фільтра не побачить чужих даних; легко видалити чи вивантажити дані одного клієнта.
Мінуси: міграції треба застосовувати до сотень чи тисяч схем; тисячі схем × десятки таблиць - велике навантаження на системний каталог і на pg_dump; пул з'єднань ускладнюється (search_path на кожен запит).

3. Окрема база на тенанта:

Плюси: максимальна ізоляція, окремі бекапи й відновлення, можна розмістити великого клієнта на окремому сервері, виконати вимоги щодо розташування даних.
Мінуси: найдорожче в експлуатації: міграції, моніторинг і оновлення для кожної бази; крос-тенантна аналітика вимагає окремого сховища.

Як обирати:

Багато невеликих клієнтів (SaaS для малого бізнесу) - спільні таблиці + RLS чи надійні глобальні скоупи.
Помірна кількість клієнтів з вимогами ізоляції - схеми.
Великі корпоративні клієнти з договірними вимогами - окремі бази (часто гібрид: більшість у спільних таблицях, найбільші - окремо).

Незалежно від моделі: tenant_id у всіх унікальних обмеженнях і індексах; тести, що перевіряють ізоляцію для кожної сутності; продумана поведінка фонових задач і кешу (ключі кешу з ідентифікатором тенанта).
Докладніше в документації: Схеми

Question 10

Як зберігати деревовидні дані в реляційній базі?

Accepted Answer

Категорії, оргструктура, коментарі з відповідями, меню - дерева. Є кілька моделей, кожна з компромісами між простотою запису й читання.
1. Список суміжності (adjacency list) - parent_id:
CREATE TABLE categories (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parent_id bigint REFERENCES categories (id),
    name text NOT NULL
);

Найпростіше: вставка й переміщення - зміна одного parent_id, є зовнішній ключ.
Піддерево чи шлях до кореня - через WITH RECURSIVE. У PostgreSQL це працює добре, тож для більшості задач цього достатньо.

2. Матеріалізований шлях - шлях до вузла зберігається рядком: 1.5.12.
CREATE EXTENSION IF NOT EXISTS ltree;
ALTER TABLE categories ADD COLUMN path ltree;
CREATE INDEX categories_path_gist ON categories USING gist (path);

SELECT * FROM categories WHERE path <@ '1.5';     -- усе піддерево
SELECT * FROM categories WHERE path @> '1.5.12';  -- усі предки

Піддерево й предки - одним індексованим запитом без рекурсії; глибина - nlevel(path).
Переміщення вузла - оновлення шляхів усього піддерева.

3. Вкладені множини (nested sets) - кожен вузол має lft і rgt, піддерево - усе в проміжку між ними.

Дуже швидке читання піддерев і підрахунок нащадків.
Вставка чи переміщення перераховує межі для великої частини дерева - погано для дерев, що часто змінюються. У Laravel - пакет kalnoy/nestedset.

4. Таблиця замикань (closure table) - окрема таблиця всіх пар «предок - нащадок» з глибиною.

Будь-які запити до ієрархії - простими JOIN з індексами.
Таблиця зв'язків росте як O(n × глибина); вставка й переміщення - кілька запитів.

Як обирати:

Типова ієрархія (категорії, меню) у PostgreSQL - parent_id + WITH RECURSIVE, а за потреби в швидких запитах до піддерев - додати ltree.
Дерево рідко змінюється, а читається постійно - nested sets чи closure table.
Дуже глибокі дерева з частими переміщеннями - adjacency list.

Не забути: захист від циклів (вузол не може стати нащадком самого себе) і обмеження глибини для даних від користувачів.
Докладніше в документації: ltree

Question 11

Як спроєктувати таблиці для великого потоку подій і логів?

Accepted Answer

Події, логи, метрики, кліки мають особливий профіль: лише вставки, рідкісні оновлення, читання переважно свіжих даних, і постійне видалення старих. Звичайна таблиця з таким навантаженням за рік-два стає проблемою.
Принципи:
1. Append-only. Рядки не оновлюються - лише додаються. Немає мертвих версій, менше роботи для вакууму, таблиця залишається компактною. Якщо подію треба «виправити» - нова подія, а не UPDATE.
2. Партиціонування за часом:
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    type text NOT NULL,
    user_id bigint,
    payload jsonb,
    PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

Видалення старих даних - DROP чи DETACH партиції, миттєво й без навантаження.
Запити за останні дні читають лише свіжі партиції.
Партиції створюються наперед (pg_partman чи заплановане завдання).

3. Мінімум індексів. Кожен індекс - ціна кожної вставки. Часто достатньо BRIN за часом і B-tree лише на тих полях, за якими справді шукають окремі записи.
4. Пакетний запис. Не INSERT на кожну подію з веб-запиту, а накопичення в черзі чи буфері й вставка порціями (або COPY). Це в рази зменшує навантаження.
5. Вузькі рядки. Типи з мінімальним розміром, порядок колонок з урахуванням вирівнювання, довідники замість повторюваних рядків, payload у jsonb лише для справді змінних даних.
6. Агрегати окремо. Дашборди читають не сирі події, а попередньо агреговані таблиці (за годину, день), що оновлюються пакетно чи матеріалізованими поданнями.
7. Політика зберігання. Сирі події - 30-90 днів, агрегати - роками, архів - в об'єктному сховищі (Parquet в S3).
Коли PostgreSQL уже не той інструмент: мільярди подій на день і аналітичні запити по всьому обсягу - тут колоночні сховища (ClickHouse, BigQuery) чи розширення на кшталт TimescaleDB дають на порядки кращу швидкість і стиснення.
Докладніше в документації: Партиціонування таблиць

Question 12

Що таке transactional outbox і навіщо він потрібен?

Accepted Answer

Проблема подвійного запису. Замовлення треба зберегти в базу і повідомити інші системи (черга, брокер повідомлень, вебхук). Це дві різні системи, і атомарно оновити обидві неможливо:
DB::transaction(function () use ($order) {
    $order->save();
});
$broker->publish(new OrderPlaced($order));   // впало тут - подію втрачено назавжди

Якщо поміняти місцями - можна опублікувати подію про замовлення, транзакція якого потім відкотиться.
Transactional outbox: подія записується в ту саму базу й ту саму транзакцію, що й зміна даних, - у спеціальну таблицю:
CREATE TABLE outbox (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    aggregate_type text NOT NULL,
    aggregate_id bigint NOT NULL,
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz
);

DB::transaction(function () use ($order) {
    $order->save();
    Outbox::create(['event_type' => 'OrderPlaced', 'payload' => [...]]);
});

Тепер або є і замовлення, і подія, або немає нічого.
Окремий процес-ретранслятор читає неопубліковані записи й надсилає їх у брокер:
SELECT * FROM outbox WHERE published_at IS NULL ORDER BY id LIMIT 100 FOR UPDATE SKIP LOCKED;

Після успішної відправки - позначає published_at (або видаляє запис). Альтернатива опитуванню - читати зміни таблиці з WAL через Change Data Capture (Debezium).
Що з цього випливає:

Доставка «щонайменше раз»: ретранслятор міг надіслати подію й упасти, не позначивши її. Отримувачі мають бути ідемпотентними (ID події + перевірка, чи вже оброблено).
Порядок подій зберігається в межах одного агрегата, якщо ретранслятор обробляє їх за id.
Таблицю outbox треба чистити - вона росте з кожною подією.

У Laravel частково цю задачу вирішують afterCommit для завдань і подій (відправити в чергу лише після успішного коміту), але це не захищає від падіння процесу між комітом і відправкою - для справжньої гарантії потрібен саме outbox.
Докладніше в документації: Pattern: Transactional outbox

Питання на співбесіді: Проєктування схеми