Логічне моделювання: Від бізнес-ідей до структур даних

Вступ: Між бізнесом та технологією

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

Проте карта — це ще не будівля. Концептуальна модель вирішує питання «що?», але не відповідає на запитання «як структурувати?». Щойно ми обираємо технологію зберігання — реляційну базу даних — перед нами постає наступний рівень абстракції: логічне моделювання (Logical Modeling).

Логічна модель — це точна специфікація структури даних у термінах обраної моделі даних (у нашому випадку — реляційної). Вона відповідає на питання: скільки таблиць, як вони називаються, які стовпці мають, як пов'язані між собою? При цьому логічна модель ще не прив'язана до конкретної СУБД: вона не знає про специфіку H2, PostgreSQL або MariaDB — ці деталі з'являться лише на фізичному рівні.

graph LR
    A["🧠 Концептуальний рівень<br/>Sutnosti + Atrybuty + Zv'yazky"]
    B["🔗 Логічний рівень<br/>Tablyci + Klyuchi + Obmejennya"]
    C["💾 Фізичний рівень<br/>DDL + Indeksy + DBMS-specifics"]

    A -->|"Правила відображення<br/>(Mapping Rules)"| B
    B --> C

    style A fill:#64748b,stroke:#334155,color:#ffffff
    style B fill:#f59e0b,stroke:#b45309,color:#ffffff
    style C fill:#64748b,stroke:#334155,color:#ffffff

Перехід від концептуального до логічного рівня здійснюється за формальними правилами відображення (Mapping Rules). Ці правила є алгоритмічними: маючи ER-діаграму, можна крок за кроком отримати реляційну схему. Власне, саме цим ми й займемося у цій статті — повністю і явно трансформуємо кожну сутність і кожен зв'язок нашої аудіоплатформи.

Що таке реляційна модель

Перш ніж почати трансформацію, варто окреслити математичний фундамент, на якому стоїть реляційна модель даних.

Реляційна модель запропонована Едгаром Коддом (Edgar F. Codd) у 1970 році у фундаментальній роботі «A Relational Model of Data for Large Shared Data Banks». В її основі лежить поняття відношення (Relation) — математичного об'єкта теорії множин.

Відношення — це іменована множина кортежів (Tuples). Кожен кортеж — це впорядкований набір значень, по одному для кожного атрибута (Attribute) відношення. Атрибут описується іменем та доменом (Domain) — множиною допустимих значень.

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

Ці правила визначають межі того, що можна «покласти» у реляційну таблицю, і безпосередньо впливають на процес трансформації ER-моделі.

Правила відображення: ER → Реляційна схема

Трансформація ER-діаграми у реляційну схему підпорядковується п'яти основним правилам. Розглянемо кожне на конкретних прикладах аудіоплатформи.

Правило 1. Сутність → Таблиця

Кожна сутність відображається в окрему таблицю. Ключові атрибути стають первинним ключем (Primary Key, PK). Прості атрибути — стовпцями.

Кілька важливих наслідків цього правила:

• Складені атрибути розкладаються. Атрибут Name сутності Author стає двома стовпцями: first_name та last_name.
• Похідні атрибути зазвичай не зберігаються — вони обчислюються під час запиту: duration / 3600 для тривалості у годинах.
• Багатозначні атрибути потребують окремої таблиці — один стовпець не може зберігати список (порушення атомарності).

Застосовуємо до аудіоплатформи. Шість сутностей → шість таблиць:

Правило 2. Зв'язок 1:N → Зовнішній ключ

Зв'язок «один до багатьох» (1:N) відображається зовнішнім ключем (Foreign Key, FK), розміщеним у таблиці на стороні «багато». Окрема таблиця для такого зв'язку не створюється.

Логіка проста: якщо один Author пов'язаний із багатьма Audiobook, то кожна аудіокнига «знає» свого автора — тому author_id розташовується у таблиці audiobooks. Зворотній варіант — помістити audiobook_ids до authors — неможливий без порушення атомарності.

Усі 1:N-зв'язки аудіоплатформи та результат відображення:

Таблиця audiobooks отримує одразу два FK — author_id та genre_id. Це нормально і дуже поширено.

Модальність визначає NULL-ність FK. Повна участь → NOT NULL. Часткова → може бути NULL:

• audiobooks.author_id NOT NULL — аудіокнига без автора неможлива.
• audiobooks.genre_id NOT NULL — аудіокнига без жанру неможлива.
• collections.user_id — допускає NULL (системні або спільні колекції — свідоме архітектурне рішення).

Правило 3. Зв'язок M:N → Проміжна таблиця

Зв'язок «багато до багатьох» (M:N) неможливо виразити через FK безпосередньо між двома таблицями. Для його відображення створюється проміжна таблиця (Junction Table, або Associative Table, або Bridge Table).

Ця таблиця містить як мінімум два стовпці — зовнішні ключі до обох пов'язаних таблиць. Разом вони утворюють складений первинний ключ (Composite Primary Key).

У нашій аудіоплатформі є один класичний M:N-зв'язок: Collection ↔ Audiobook (колекція містить багато книг, книга може бути в багатьох колекціях). Він відображається у таблицю audiobook_collection:

-- audiobook_collection
-- collection_id (FK → collections.id)  ┐
-- audiobook_id  (FK → audiobooks.id)   ┘ → Складений PK

Складений ключ гарантує унікальність пар: одну й ту ж книгу не можна додати до однієї колекції двічі.

Правило 4. Зв'язок 1:1 → FK з UNIQUE або злиття таблиць

Зв'язок «один до одного» (1:1) може відображатися двома способами:

1. Злиття таблиць — якщо сутності завжди існують разом і мають схожу природу, їх можна об'єднати в одну таблицю.
2. FK з обмеженням UNIQUE — FK розміщується у таблиці «залежної» сутності та позначається UNIQUE, що гарантує неможливість двох рядків з однаковим значенням FK.

У поточній схемі аудіоплатформи зв'язків 1:1 немає. Але якби, наприклад, у кожного User був окремий SecurityProfile (пароль, двофакторна автентифікація, токени) — доцільно було б розділити їх на дві таблиці через 1:1 з FK security_profiles.user_id UNIQUE.

Правило 5. Зв'язок з атрибутами → Таблиця із власним PK

Якщо зв'язок має власні атрибути, він відображається окремою таблицею, яка зберігає FK до обох учасників та стовпці для власних атрибутів зв'язку.

Це правило стосується ListeningProgress з нашої моделі — зв'язку між User та Audiobook, що має атрибути position (позиція відтворення у секундах) та last_listened (дата і час останнього прослуховування).

Результат відображення — таблиця listening_progresses:

-- listening_progresses
-- id            (PK, сурогатний)
-- user_id       (FK → users.id)
-- audiobook_id  (FK → audiobooks.id)
-- position      (атрибут зв'язку)
-- last_listened (атрибут зв'язку)

Первинні ключі: Натуральні чи Сурогатні?

Одне з найважливіших рішень при логічному моделюванні — вибір типу первинного ключа. Існують два принципово різних підходи, кожен зі своїми сильними сторонами та ризиками.

Натуральні ключі

Натуральний ключ (Natural Key) — це атрибут, що вже існує у предметній області і є природним ідентифікатором сутності.

Приклади натуральних ключів:

• ISBN книги (унікальний номер видання)
• Email користувача (унікальний у межах системи)
• Код валюти (USD, EUR, UAH — стандарт ISO 4217)
• Номер паспорта

Привабливість натуральних ключів очевидна: вони несуть смисл, їх легко читати у відладці, і їх не потрібно окремо генерувати. Коли ми бачимо audiobooks.isbn = '978-0-13-468599-1' — ми одразу розуміємо, про яку книгу йдеться.

Але натуральні ключі мають серйозні ризики:

Сурогатні ключі

Сурогатний ключ (Surrogate Key) — штучний ідентифікатор, що не має бізнес-смислу і генерується виключно для технічних потреб. Зовнішній світ його не знає і не повинен знати.

Два найпоширеніших типи:

• AUTO_INCREMENT / SERIAL — ціле число, що автоматично збільшується (1, 2, 3...). Просто, швидко, компактне.
• UUID (Universally Unique Identifier) — 128-бітний ідентифікатор у форматі 550e8400-e29b-41d4-a716-446655440001. Глобально унікальний, незалежний від бази даних.

У нашій схемі аудіоплатформи всі таблиці використовують UUID як PK. Це не випадково:

Правило вибору. Задайте собі одне питання: «Чи може значення вашого ключа-кандидата змінитися з часом?» Якщо так — він не підходить як первинний ключ. Використовуйте сурогатний.

-- ПОГАНО: email як PK
CREATE TABLE users (
    email    VARCHAR(376) PRIMARY KEY,  -- а якщо змінить email?
    username VARCHAR(64)  NOT NULL
);

-- При зміні email — каскадне оновлення у всіх FK:
UPDATE users SET email = 'new@mail.com' WHERE email = 'old@mail.com';
-- Це оновить і всі collections.user_id, listening_progresses.user_id...

-- ДОБРЕ: UUID як PK, email — окремий UNIQUE атрибут
CREATE TABLE users (
    id           UUID         PRIMARY KEY,
    username     VARCHAR(64)  NOT NULL UNIQUE,
    email        VARCHAR(376) UNIQUE,     -- унікальний, але не PK
    password_hash VARCHAR(128) NOT NULL
);

-- Зміна email не впливає на жодну іншу таблицю
UPDATE users SET email = 'new@mail.com' WHERE id = '880e8400-...';

У другому варіанті email залишається унікальним (UNIQUE) — але PK — незмінний UUID. Будь-яка кількість атрибутів може бути позначена як альтернативні ключі (Alternate Keys) через UNIQUE обмеження, не стаючи первинними ключами.

Зовнішні ключі та Посилальна цілісність

Зовнішній ключ — це не просто стовпець зі значенням, що збігається з іншою таблицею. Це оголошена залежність між двома таблицями, яку СУБД контролює автоматично. Саме завдяки FK реляційна база даних підтримує посилальну цілісність (Referential Integrity) — стан, при якому жоден FK не вказує на неіснуючий рядок.

Зрозуміти це найлегше через контрприклад. Уявіть: у таблиці audiobooks є рядок із author_id = '550e8400-0001'. Якщо цей автор буде видалений із таблиці authors без обробки залежних записів — у audiobooks залишиться «сирота» (Orphan Record): рядок, що посилається на автора, якого більше не існує. База даних у такому стані є некоректною.

FK-обмеження запобігає цьому двома способами:

1. При вставці — перевіряє, що значення author_id існує у authors.id.
2. При видаленні батьківського запису — виконує стратегію, визначену через ON DELETE.

Каскадні дії (ON DELETE / ON UPDATE)

При визначенні FK обирається одна з чотирьох стратегій реакції на видалення або оновлення батьківського запису:

Стратегія каскадів аудіоплатформи

Розглянемо, яка стратегія обрана у нашій схемі (DDL з ddl_h2.sql) та чому:

Обмеження цілісності: Три Лінії Оборони

Термін «цілісність бази даних» (Data Integrity) означає, що дані у будь-який момент часу відповідають бізнес-правилам і не суперечать одне одному. Реляційна модель визначає три групи правил цілісності, і кожна з них реалізується через конкретні SQL-конструкції.

Сутнісна цілісність (Entity Integrity)

Жоден атрибут первинного ключа не може приймати значення NULL, і значення PK є унікальним у таблиці.

Це фундаментальне правило: якщо рядок не може бути однозначно ідентифікований — він не існує для реляційної системи. Реалізується через обмеження PRIMARY KEY, яке автоматично включає NOT NULL та UNIQUE.

-- Сутнісна цілісність: id не може бути NULL і дублюватись
CREATE TABLE authors (
    PRIMARY KEY (id),
    id UUID, -- завдяки PK автоматично NOT NULL + UNIQUE
    ...
);

Посилальна цілісність (Referential Integrity)

Значення зовнішнього ключа має або відповідати існуючому PK батьківської таблиці, або бути NULL (якщо FK дозволяє NULL).

Реалізується через обмеження FOREIGN KEY, яке ми вже детально розглянули. Це — другий рівень оборони: після сутнісної цілісності окремих таблиць забезпечується коректність зв'язків між ними.

Доменна цілісність (Domain Integrity)

Значення атрибута повинне належати допустимому домену: перебувати у дозволеному діапазоні, відповідати формату або входити у перелік припустимих значень.

Реалізується декількома механізмами:

• NOT NULL — поле не може бути порожнім.
• UNIQUE — значення унікальне у таблиці.
• CHECK — довільне логічне обмеження.
• DEFAULT — значення за замовчуванням.
• Перечислення (ENUM) — закритий набір можливих значень.

Розглянемо обмеження доменної цілісності на прикладі таблиці audiobooks:

CREATE TABLE audiobooks (
    PRIMARY KEY (id),
    id           UUID,

    -- NOT NULL: аудіокнига без назви неможлива
    title        VARCHAR(255) NOT NULL,

    -- CHECK: тривалість має бути позитивним числом
    duration     INTEGER NOT NULL,
                 CONSTRAINT audiobooks_duration_positive_check
                      CHECK (duration > 0),

    -- CHECK: рік не може бути раніше 1900 і пізніше наступного року
    release_year INTEGER NOT NULL,
                 CONSTRAINT audiobooks_release_year_check
                      CHECK (release_year >= 1900
                         AND release_year <= EXTRACT(YEAR FROM CURRENT_DATE) + 1),

    description      TEXT,       -- NULL дозволений: опис необов'язковий
    cover_image_path VARCHAR(2048) -- NULL дозволений: обкладинка необов'язкова
);

І таблиці audiobook_files, де формат файлу обмежений переліком:

-- ENUM: тільки ці формати є допустимими
CREATE TYPE file_format_enum AS ENUM ('mp3', 'ogg', 'wav', 'm4b', 'aac', 'flac');

CREATE TABLE audiobook_files (
    format file_format_enum NOT NULL,  -- тільки з переліку
    size   INTEGER,
           CONSTRAINT audiobook_files_size_positive_check
                CHECK (size IS NULL OR size > 0)  -- NULL або > 0
);

Повна логічна схема аудіоплатформи

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

@startuml
!theme plain
skinparam linetype ortho
skinparam defaultFontName "Courier New"

entity "authors" as a {
  * **id** : UUID <<PK>>
  --
  * first_name : VARCHAR(64)
  * last_name  : VARCHAR(64)
  bio         : TEXT
  image_path  : VARCHAR(2048)
}

entity "genres" as g {
  * **id** : UUID <<PK>>
  --
  * name  : VARCHAR(64) <<UK>>
  description : TEXT
}

entity "audiobooks" as ab {
  * **id** : UUID <<PK>>
  --
  * title        : VARCHAR(255)
  * author_id    : UUID <<FK>>
  * genre_id     : UUID <<FK>>
  * duration     : INTEGER
  * release_year : INTEGER
  description      : TEXT
  cover_image_path : VARCHAR(2048)
}

entity "users" as u {
  * **id** : UUID <<PK>>
  --
  * username      : VARCHAR(64) <<UK>>
  * password_hash : VARCHAR(128)
  email           : VARCHAR(376)
  avatar_path     : VARCHAR(2048)
}

entity "collections" as c {
  * **id** : UUID <<PK>>
  --
  user_id    : UUID <<FK>>
  * name     : VARCHAR(128)
  * created_at : TIMESTAMP
}

entity "audiobook_collection" as ac {
  * **collection_id** : UUID <<PK, FK>>
  * **audiobook_id**  : UUID <<PK, FK>>
}

entity "audiobook_files" as af {
  * **id** : UUID <<PK>>
  --
  * audiobook_id : UUID <<FK>>
  * file_path    : VARCHAR(2048)
  * format       : file_format_enum
  size           : INTEGER
}

entity "listening_progresses" as lp {
  * **id** : UUID <<PK>>
  --
  user_id      : UUID <<FK>>
  * audiobook_id : UUID <<FK>>
  * position   : INTEGER
  last_listened : TIMESTAMP
}

a  ||--o{ ab : "author_id"
g  ||--o{ ab : "genre_id"
u  ||--o{ c  : "user_id"
c  ||--o{ ac : "collection_id"
ab ||--o{ ac : "audiobook_id"
ab ||--|{ af : "audiobook_id"
u  ||--o{ lp : "user_id"
ab ||--o{ lp : "audiobook_id"

@enduml

Ця схема є повним результатом логічного моделювання: вона містить рівно стільки таблиць, скільки потрібно для коректного відображення концептуальної моделі, і не більше. Кожне рішення — кількість таблиць, розміщення FK, використання UNIQUE — обґрунтоване правилами, розглянутими вище.

Практичні завдання

DEPARTMENT ||--o{ EMPLOYEE : "works in"
EMPLOYEE   }o--o{ PROJECT  : "assigned to"

products: id (PK), name, price, category_id (FK), stock_quantity, is_active

Підсумок

На цьому занятті ми здійснили повний перехід від концептуальної моделі до логічної реляційної схеми аудіоплатформи. Ключові концепції, які варто засвоїти:

• П'ять правил відображення визначають, як сутності та зв'язки ER-моделі перетворюються на таблиці, FK та junction-таблиці.
• Сурогатні ключі (UUID) є надійнішим вибором для більшості систем через незмінність і глобальну унікальність. Натуральні ключі несуть ризики мінливості.
• Зовнішній ключ реалізує посилальну цілісність; стратегії ON DELETE (CASCADE, SET NULL, RESTRICT) визначають поведінку системи при видаленні пов'язаних записів.
• Три типи цілісності — сутнісна (PK), посилальна (FK), доменна (CHECK, NOT NULL, UNIQUE) — забезпечують коректність даних на рівні СУБД незалежно від додатка.

Логічна схема — це ще не SQL. Наступна стаття присвячена фізичному рівню: типам даних конкретних СУБД, повному синтаксису DDL, індексам і стратегіям наповнення таблиць тестовими даними.