Логічне проектування: Від концепції до реляційної схеми

Вступ: Що таке логічне проектування?

Логічне проектування — це міст між "ідеальним" світом вимог та "реальним" світом баз даних.

graph LR
    A[Концептуальна схема<br/>ER-діаграма] --> B{Логічне<br/>проектування}
    B --> C[Логічна схема<br/>Реляційна модель]

    style B fill:#3b82f6,color:#fff
    style A fill:#64748b,color:#fff
    style C fill:#f59e0b,color:#333

Реляційне відношення як таблиця

Перш ніж переходити до трансформації, згадаємо, що таке відношення (Relation) у логічному контексті.

Математична анатомія відношення

Формально відношення $R$ ступеня $n$ можна представити як: $R \subseteq D_1 \times D_2 \times \dots \times D_n$

Де:

• $n$ — ступінь відношення (кількість атрибутів).
• $D_1, D_2, \dots, D_n$ — домени (типи значень).
• Кортеж $t = \langle a_1, a_2, \dots, a_n \rangle$ — набір значень $a_i \in D_i$.

Правила інтерпретації відношення як таблиці

Щоб ми могли називати математичне відношення "таблицею", мають виконуватися такі правила:

Візуалізація відношення "Person" як таблиці:

classDiagram
    class Person {
        +person_id: INT (PK)
        +first_name: STRING
        +last_name: STRING
        +email: STRING (Unique)
        +status_id: INT (FK)
    }

Ключі: Фундамент цілісності

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

1. Первинні ключі (Primary Keys - PK)

Властивості PK:

1. Унікальність: Немає двох рядків з однаковим значенням ключа.
2. Нескоротність (мінімальність): Жодна частина ключа не може бути видалена без втрати унікальності.
3. NOT NULL: Значення ключа обов'язково має існувати.

Приклад вибору ключа: Для таблиці "Студенти" ми можемо обрати:

• student_id (штучний ключ, надійно) — ✅ Найкращий вибір
• passport_number (природний ключ, може змінитися) — ⚠️ Ризиковано
• email (природний ключ, унікальний) — ⚠️ Може змінитися

2. Зовнішні ключі (Foreign Keys - FK)

Механізм FK реалізує зв'язки між сутностями. Він гарантує референційну цілісність (якщо посилання існує, то об'єкт, на який воно вказує, теж має існувати).

Правила трансформації: ER → Реляційна схема

Це систематичний алгоритм перетворення концептуальної моделі на набір таблиць.

Крок 1: Відображення сильних сутностей

Кожна сильна сутність перетворюється на окрему таблицю.

ENTITY: Product
ATTRIBUTES:
  - product_id (PK)
  - title
  - price

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    title NVARCHAR(200) NOT NULL,
    price DECIMAL(10, 2)
);

Крок 2: Відображення слабких сутностей

Слабка сутність перетворюється на таблицю, яка включає атрибути слабкої сутності ПЛЮС первинний ключ "батьківської" сутності як зовнішній ключ.

PARENT: User (user_id PK)
WEAK ENTITY: Session
ATTRIBUTES: session_id, login_time

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    name NVARCHAR(100)
);

CREATE TABLE Sessions (
    session_id INT,
    user_id INT,  -- FK від батька
    login_time DATETIME,
    PRIMARY KEY (session_id, user_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
        ON DELETE CASCADE
);

Крок 3: Відображення атрибутів

Крок 4: Відображення зв'язків

Тут правила залежать від кардинальності (типу зв'язку).

1. Зв'язок 1:1 (Один-до-одного)

Є три стратегії:

1. Зовнішній ключ: Додати PK однієї таблиці як FK в іншу.
2. Унікальний зовнішній ключ: Як пункт 1, але з умовою UNIQUE на FK.
3. Об'єднання: Якщо участь обох сторін повна, можна об'єднати в одну таблицю.

2. Зв'язок 1:N (Один-до-багатьох)

Первинний ключ сторони "Один" додається як зовнішній ключ у таблицю сторони "Багато".

graph LR
    Dept[DEPARTMENTS<br/>PK dept_id] -- 1:N --- Emp[EMPLOYEES<br/>PK emp_id<br/>FK dept_id]

3. Зв'язок M:M (Багато-до-багатьох)

Завжди вимагає створення проміжної таблиці (Junction Table).

Просунуті типи зв'язків

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

1. Рекурсивні (унарні) зв'язки

Це зв'язок сутності з самою собою.

Приклад: Співробітники та Керівники Кожен співробітник має керівника, який також є співробітником.

graph TD
    Emp[EMPLOYEES] -- "керує (0..*)" --- Emp

Трансформація: Додаємо FK у ту саму таблицю, який посилається на PK цієї ж таблиці.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name NVARCHAR(100),
    manager_id INT, -- FK посилається на employee_id
    FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)
);

Приклад даних:

2. Тернарні зв'язки

Зв'язок між трьома сутностями одночасно.

Приклад: Викладач, Предмет та Група Викладач веде певний предмет у конкретної групи.

Трансформація: Тернарний зв'язок ЗАВЖДИ перетворюється на окрему таблицю, яка містить три зовнішні ключі.

CREATE TABLE Teaching (
    teacher_id INT,
    subject_id INT,
    group_id INT,
    semester NVARCHAR(20),
    PRIMARY KEY (teacher_id, subject_id, group_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id),
    FOREIGN KEY (group_id) REFERENCES Groups(group_id)
);

Обробка ієрархій (Супертипи та Підтипи)

Коли в ER-моделі є успадкування, ми маємо обрати одну з логічних стратегій. Розглянемо їх детально на прикладі сутності User та підтипів Student і Teacher.

Стратегія 1: Одна таблиця на ієрархію (Single Table per Hierarchy)

Всі атрибути супертипу та всіх підтипів зберігаються в одній великій таблиці. Додається стовпець-дискримінатор (user_type).

CREATE TABLE AllUsers (
    user_id INT PRIMARY KEY,
    name NVARCHAR(100),
    email NVARCHAR(100),
    user_type NVARCHAR(10), -- 'STUDENT' or 'TEACHER'
    -- Атрибути студента
    student_card_number NVARCHAR(20) NULL,
    -- Атрибути викладача
    degree NVARCHAR(50) NULL
);

✅ Переваги: Висока швидкість вибірки (немає JOIN). Легко додавати нові підтипи. ❌ Недоліки: Багато NULL значень (стовпці студента порожні для викладача). Порушення 3NF.

Стратегія 2: Таблиця на підтип (Table per Concrete Class)

Кожен підтип має свою таблицю з усіма атрибутами (і своїми, і успадкованими). Базової таблиці не існує.

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name NVARCHAR(100),
    email NVARCHAR(100),
    student_card_number NVARCHAR(20)
);

CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    name NVARCHAR(100),
    email NVARCHAR(100),
    degree NVARCHAR(50)
);

✅ Переваги: Немає NULL. Об'єкти підтипів повністю незалежні. ❌ Недоліки: Дублювання структури атрибутів супертипу (name, email). Складно зробити універсальний пошук по всіх користувачах.

Стратегія 3: Таблиця на кожен рівень (Table per Class)

Створюється таблиця для супертипу і по таблиці для кожного підтипу. Зв'язок 1:1 за первинним ключем.

CREATE TABLE BaseUsers (
    user_id INT PRIMARY KEY,
    name NVARCHAR(100),
    email NVARCHAR(100)
);

CREATE TABLE Students (
    user_id INT PRIMARY KEY, -- також FK
    student_card_number NVARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES BaseUsers(user_id)
);

CREATE TABLE Teachers (
    user_id INT PRIMARY KEY, -- також FK
    degree NVARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES BaseUsers(user_id)
);

✅ Переваги: Найбільш правильна з точки зору реляційної теорії. Чиста структура, немає дублювання даних. ❌ Недоліки: Потребує багато JOIN для отримання повної інформації про об'єкт.

Цілісність даних при трансформації

При створенні зовнішніх ключів ми маємо визначити правила поведінки системи при зміні або видаленні батьківських даних.

Основні типи дій (Actions):

1. CASCADE (Каскадно):
   • Видалення батька → Видалення всіх дітей.
   • Оновлення PK батька → Оновлення всіх FK дітей.
   • Приклад: Видалення Order видаляє всі OrderItems.
2. SET NULL (Встановити в NULL):
   • Видалення батька → FK дітей стає NULL.
   • Приклад: Видалення Category робить товари "без категорії".
3. RESTRICT / NO ACTION (Заборонити):
   • Не дозволяє видалити батька, поки існують пов'язані діти.
   • Приклад: Не можна видалити Student, поки у нього є Grades.
4. SET DEFAULT (Встановити за замовчуванням):
   • При видаленні батька FK стає стандартним значенням.

SQL приклад з правилами:

CREATE TABLE OrderItems (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
        ON DELETE CASCADE,     -- Видаляємо замовлення -> видаляємо деталі
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
        ON DELETE RESTRICT    -- Не видаляємо товар, якщо він є в замовленнях
);

Масштабування назв та стандартів

Для великих проєктів важливо мати чіткий Data Dictionary та стандарти типів даних.

Порівняння типів даних у різних СУБД

Глибоке іменування (Naming Standards)

Повний приклад: "Особиста бібліотека"

Трансформуємо концептуальну модель, яку ми створили в минулому уроці, у логічну схему.

Етап 1: Аналіз сутностей та їх атрибутів

Етап 2: Аналіз зв'язків та модальності

1. Person ← Status (N:1, повна):
   • В Persons обов'язковий status_id (NOT NULL).
   • ON DELETE RESTRICT (не видаляємо статуси, якщо є люди).
2. Publication → Copy (1:N, повна):
   • В Copies обов'язковий publication_id.
   • ON DELETE CASCADE (видалили опис книги -> видаляємо екземпляри).
3. Shelf → Copy (1:N, часткова):
   • В Copies shelf_id дозволяє NULL (книга може бути не на полиці, а в дорозі).
4. LogOn (1:N від Person):
   • ON DELETE CASCADE (видалили акаунт -> видалили логи).

Етап 3: Фінальна логічна схема

erDiagram
    STATUSES ||--o{ PERSONS : "має"
    PERSONS ||--o{ LOGONS : "виконує"
    PERSONS ||--o{ REQUESTS : "створює"
    PUBLICATIONS ||--o{ COPIES : "має"
    SHELVES ||--o{ COPIES : "зберігає"
    COPIES ||--o{ REQUESTS : "обслуговує"

    STATUSES {
        int status_id PK
        string status_name
    }
    PERSONS {
        int person_id PK
        int status_id FK
        string first_name
        string last_name
        string email
    }
    LOGONS {
        int session_id PK
        int person_id FK
        datetime login_time
    }
    REQUESTS {
        int request_id PK
        int person_id FK
        int copy_id FK
        date request_date
    }
    COPIES {
        int copy_id PK
        int publication_id FK
        int shelf_id FK
        string condition
    }
    PUBLICATIONS {
        int publication_id PK
        string title
        string author
        string ISBN
    }
    SHELVES {
        int shelf_id PK
        string shelf_number
    }

Бізнес-правила та обмеження (Business Rules)

Логічна схема має не лише структурувати дані, а й обмежувати їх згідно з бізнес-правилами.

1. Обмеження домену (Domain Constraints)

• Перевірка типу та діапазону.
• Приклад: price >= 0, age BETWEEN 16 AND 100.

2. Обмеження кортежу (Tuple Constraints)

• Перевірка значень у межах одного рядка.
• Приклад: end_date >= start_date.

3. Цілісність відношення (Relation Integrity)

• Унікальність PK.
• Унікальність альтернативних ключів (Unique Indexes).

Типові підводні камені трансформації

Практичні вправи

Завдання 1: Трансформація складеного атрибута

Маємо сутність Customer з атрибутом address (city, street, zip). Напишіть SQL-структуру таблиці після декомпозиції.

Завдання 2: Зв'язок M:M

Перетворіть зв'язок між Teacher та Subject у набір таблиць логічної схеми. Вкажіть первинні та зовнішні ключі.

Завдання 3: Іменування

Запропонуйте назви стовпців та ключів для системи "Аптека", де є рецепти, ліки та пацієнти.

Завдання 4: Рекурсія

Створіть логічну схему для "Меню сайту", де кожен пункт може мати багато вкладених підпунктів.

Порівняння моделей проектування

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

Глибокий розбір типів даних (Data Types Deep Dive)

Вибір правильного типу даних на логічному рівні критично впливає на продуктивність та обсяг сховища.

1. Текстові типи: VARCHAR vs NVARCHAR

2. Числові типи: DECIMAL vs FLOAT

3. Цілі числа: Коли використовувати BIGINT?

Завжди використовуйте найменший можливий тип:

• TINYINT (0-255) — статуси, вік.
• SMALLINT (-32k to 32k) — кількість товарів у невеликому магазині.
• INT (до 2 млрд) — стандарт для ID.
• BIGINT (понад 2 млрд) — лічильники переглядів YouTube, транзакції глобальних платіжних систем.

Кейс-стаді: Проектування онлайн-магазину

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

1. Бізнес-аналіз

Магазин продає Товари. Товари мають Категорії. Клієнти роблять Замовлення. Кожне замовлення містить кілька Позицій. Клієнти можуть залишати Відгуки.

2. ER-діаграма (Концептуальна)

• Сутності: Customer, Product, Category, Order, Review.
• Зв'язки:
  • Category 1:N Product (У кожній категорії багато товарів).
  • Customer 1:N Order (Клієнт робить багато замовлень).
  • Order M:M Product (У замовленні багато товарів, товар у багатьох замовленнях).
  • Customer 1:N Review (Клієнт пише багато відгуків).
  • Product 1:N Review (У товару багато відгуків).

3. Трансформація в логічну схему

А. Обробка M:M (Order <-> Product)

Створюємо таблицю OrderItems:

CREATE TABLE OrderItems (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Б. Обробка категорій (1:N)

Додаємо category_id у таблицю Products:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    title NVARCHAR(200),
    description NVARCHAR(MAX),
    price DECIMAL(10,2),
    stock_quantity INT,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

В. Обробка відгуків

Відгук пов'язаний і з Клієнтом, і з Товаром.

CREATE TABLE Reviews (
    review_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    rating TINYINT CHECK (rating BETWEEN 1 AND 5),
    comment NVARCHAR(1000),
    created_at DATETIME2,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

Візуальне представлення логічної схеми (Schema Diagram)

erDiagram
    CATEGORIES ||--o{ PRODUCTS : "містить"
    CUSTOMERS ||--o{ ORDERS : "робить"
    CUSTOMERS ||--o{ REVIEWS : "пише"
    PRODUCTS ||--o{ REVIEWS : "отримує"
    ORDERS ||--o{ ORDER_ITEMS : "складається з"
    PRODUCTS ||--o{ ORDER_ITEMS : "входить у"

    CATEGORIES {
        int category_id PK
        string name
    }
    PRODUCTS {
        int product_id PK
        int category_id FK
        string title
        decimal price
    }
    CUSTOMERS {
        int customer_id PK
        string name
        string email
    }
    ORDERS {
        int order_id PK
        int customer_id FK
        datetime order_date
        decimal total_amount
    }
    ORDER_ITEMS {
        int order_id PK, FK
        int product_id PK, FK
        int quantity
        decimal unit_price
    }
    REVIEWS {
        int review_id PK
        int customer_id FK
        int product_id FK
        int rating
    }

Документування бізнес-логіки на рівні схеми

Окрім таблиць, логічна схема має описувати обмеження цілісності:

Глибоке занурення в стратегії первинних ключів (PK)

Вибір стратегії PK на логічному рівні має довгострокові наслідки для продуктивності та зручності розробки.

1. Автоінкремент (Serial / Auto-increment)

Найпопулярніший вибір для більшості систем.

2. UUID (Universally Unique Identifier)

128-бітне випадкове число.

Продуктивність зв'язків: Чи потрібно індексувати Foreign Keys?

Це питання часто виникає на етапі переходу до фізичного проектування.

1. Багато-до-багатьох: Проміжні таблиці ЗАВЖДИ вимагають індексів на обох FK для швидкого пошуку.
2. Багато-до-одного: Більшість СУБД (як Oracle або SQL Server) НЕ створюють індекси на FK автоматично.
3. Чому це важливо? Без індексу на FK будь-який JOIN або перевірка цілісності при видаленні батька вимагатиме повного сканування (Full Table Scan) дочірньої таблиці.

Концептуальна різниця: Відношення vs. Таблиця

Хоча ми часто використовуємо ці терміни як синоніми, між ними є тонка теоретична різниця, яку важливо розуміти професіоналу.

Глосарій логічного проектування

Типові підводні камені трансформації

Практичні вправи

Завдання 1: Трансформація складеного атрибута

Маємо сутність Customer з атрибутом address (city, street, zip). Напишіть SQL-структуру таблиці після декомпозиції.

Завдання 2: Зв'язок M:M

Перетворіть зв'язок між Teacher та Subject у набір таблиць логічної схеми. Вкажіть первинні та зовнішні ключі.

Завдання 3: Іменування

Запропонуйте назви стовпців та ключів для системи "Аптека", де є рецепти, ліки та пацієнти.

Завдання 4: Рекурсія

Створіть логічну схему для "Меню сайту", де кожен пункт може мати багато вкладених підпунктів.

Завдання 5: Ієрархія

Для системи "Транспорт" (суспільний транспорт: Автобус, Трамвай, Тролейбус) оберіть оптимальну стратегію трансформації підтипів та обґрунтуйте вибір.

Інструменти підтримки логічної цілісності

Окрім ключів та обмежень, СУБД надають активні інструменти для забезпечення бізнес-логіки.

1. Тригери (Triggers)

У контексті логічного проектування тригери використовуються для:

• Складної валідації, яку неможливо описати через CHECK.
• Аудиту змін (запис у таблицю AuditLogs при зміні ціни).
• Підтримання денормалізованих полів (автоматичний перерахунок total_amount у замовленні).

2. Збережені процедури (Stored Procedures)

Це інкапсульована логіка на боці БД. Вони дозволяють:

• Виконувати складні операції в межах однієї транзакції.
• Сховати складність логічної схеми від прикладного програміста.
• Посилити безпеку (заборона прямого доступу до таблиць, тільки через процедури).

Логічний vs. Фізичний дизайн: Де межа?

Коли ми завершуємо логічну схему, ми передаємо її системному адміністратору або DBA для фізичного впровадження.

Рішення до практичних вправ

Нижче наведено варіанти відповідей, щоб ви могли перевірити себе.

Завдання 1: Трансформація складеного атрибута

Рішення:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name NVARCHAR(100),
    addr_city NVARCHAR(50),      -- декомпозиція
    addr_street NVARCHAR(100),   -- декомпозиція
    addr_zip_code NVARCHAR(10)   -- декомпозиція
);

Завдання 2: Зв'язок M:M (Teacher & Subject)

Рішення: Вимагає Junction Table TeacherSubjects.

CREATE TABLE TeacherSubjects (
    teacher_id INT,
    subject_id INT,
    PRIMARY KEY (teacher_id, subject_id),
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id),
    FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id)
);

Завдання 4: Рекурсія (Меню сайту)

Рішення:

CREATE TABLE MenuItems (
    item_id INT PRIMARY KEY,
    title NVARCHAR(100),
    url NVARCHAR(200),
    parent_item_id INT NULL, -- FK на item_id цієї ж таблиці
    FOREIGN KEY (parent_item_id) REFERENCES MenuItems(item_id)
);

Завдання 6: Проектування FK з CASCADE

Рішення:

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    title NVARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
        ON DELETE CASCADE -- Ключова частина завдання
);

Завдання 7: Дизайн для продуктивності

Уявіть таблицю BigLog, яка отримує 1000 записів на секунду. Який PK ви оберете (INT vs UUID vs Sequential UUID) та як обґрунтуєте це з точки зору структури B-Tree індексу на диску?

Фінальна порада: Простота проти Складності

Висновки

У наступному уроці: Ми вивчимо класифікацію таблиць, зрозуміємо різницю між довідниками, транзакційними та історичними таблицями!