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

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

Типові випадки:

• Лічильники: 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

Задача: знати, хто, коли й що змінив - для розслідувань, вимог регуляторів, відновлення помилково змінених даних.

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

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 - не дефолт для кожної таблиці. Він доречний там, де відновлення й історія справді потрібні, і з продуманими частковими індексами й очищенням.

Докладніше в документації: Часткові індекси

Поліморфний зв'язок - одна колонка посилається на рядки різних таблиць, а друга колонка каже, на яку:

comments: id | commentable_type | commentable_id | body
          1  | App\Models\Post  | 42             | ...
          2  | App\Models\Video | 42             | ...

У 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, на яку посилаються і пости, і відео, а коментарі посилаються на неї. Елегантно, але складніше.

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

Докладніше в документації: Обмеження

EAV (Entity-Attribute-Value) - зберігати довільні атрибути не колонками, а рядками «сутність - атрибут - значення»:

product_attributes: product_id | attribute | value
                    1          | color     | red
                    1          | weight    | 1.2
                    2          | voltage   | 220

Привабливо для товарів з різними характеристиками (одяг має розмір, а холодильник - об'єм): нові атрибути додаються без зміни схеми. Так влаштований, наприклад, 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