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

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

Приклад проблеми:

orders: id | customer_name | customer_email | product | price
        1  | Оля           | olia@x.com     | Книга   | 300
        2  | Оля           | olia@x.com     | Ручка   | 50

Оля змінила 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 залежить від клієнта, а не від замовлення.

Нормалізована схема:

customers: id | name | email
products:  id | name | price
orders:    id | customer_id | created_at
order_items: order_id | product_id | quantity | price_at_purchase

Зверніть увагу на price_at_purchase: ціна в момент покупки - окремий факт, а не дублювання. Якщо завтра ціна товару зміниться, старе замовлення не має змінитися.

Практичне правило: проєктувати в 3НФ за замовчуванням і свідомо денормалізувати там, де виміряно, що це потрібно для швидкодії. Вищі форми (BCNF, 4НФ, 5НФ) у прикладних застосунках потрібні рідко.

Докладніше в документації: Database normalization

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

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.

Докладніше в документації: Зовнішні ключі

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

Докладніше в документації: Первинні ключі