Практичний вибір структури даних

Нормальна форма не є самоціллю. Мета проєктувальника - побудувати схему, яка правильно відтворює вимоги, не створює зайвих суперечностей і дає змогу перевірити важливі правила. У цій завершальній лекції Теми 4 ми не повторюватимемо алгоритми 1НФ-BCNF. Натомість поєднаємо вже відомі інструменти в послідовність ухвалення та перевірки рішень.

Цілі лекції

Після опрацювання матеріалу ви зможете:

перетворити змістові вимоги на факти, ключі та залежності;
розпізнати незалежні багатозначні факти й перевірити 4НФ;
практично пояснити залежність з'єднання та призначення 5НФ;
відрізнити декомпозицію без втрат від схеми, що породжує хибні кортежі;
обґрунтувати або відхилити денормалізацію за вимірюваними критеріями;
перевірити готову схему за коротким чеклістом.

Передумови

Потрібно розуміти сутності, первинні й зовнішні ключі, зв'язки 1:N і M:N, функціональні залежності, а також призначення 1НФ, 2НФ, 3НФ і BCNF. Тут ці поняття використовуються як інструменти, а не викладаються повторно.

1. Рішення починається не з таблиць

Невдала схема часто виникає через передчасне запитання: «Які таблиці створити?». Спочатку потрібно з'ясувати, які твердження система має зберігати й перевіряти.

Практична послідовність така:

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

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

2. Повний кейс: платформа добору наставників

Потрібно спроєктувати частину платформи, яка добирає наставників для навчальних проєктів.

2.1. Уточнені вимоги

Наставник має внутрішній ідентифікатор, ім'я та унікальну електронну адресу.
Наставник може спілкуватися кількома мовами.
Наставник може знати кілька технологій.
Мови й технології наставника незалежні: знання PostgreSQL не залежить від того, чи спілкується наставник українською або англійською.
Існують типи проєктів, наприклад «вебзастосунок», «аналітичний звіт», «мобільний застосунок».
Наставника можна призначити на проєкт певного типу з певною технологією лише тоді, коли одночасно виконано три парні умови:
- наставник допущений до цієї технології;
- наставник працює із цим типом проєкту;
- технологія дозволена для цього типу проєкту.
Ці три умови є достатніми: окремих винятків для конкретної трійки немає.
Система часто показує профіль наставника, але спочатку немає вимірювань, які доводять проблему продуктивності.

Вимоги 4 і 7 критично важливі. Без них однакова структура рядків могла б мати інший зміст і потребувати іншої схеми.

2.2. Факти та гранулярність

Виділимо твердження, які мають бути істинними незалежно одне від одного:

існує наставник з певними атрибутами;
наставник володіє мовою;
наставник знає технологію;
наставник працює з типом проєкту;
технологія дозволена для типу проєкту.

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

Довідники мають природні унікальні назви або коди, але для зв'язків використаємо стабільні ідентифікатори:

Mentor(mentor_id, full_name, email)
Language(language_id, code, name)
Technology(technology_id, name)
ProjectType(project_type_id, name)

Кандидатні ключі:

Mentor: mentor_id, а також email;
Language: language_id, а також code;
Technology: technology_id, а також name;
ProjectType: project_type_id, а також name.

Отже, для наставника діють функціональні залежності mentor_id → full_name, email та email → mentor_id, full_name. У схемі це означає первинний ключ для mentor_id і обмеження UNIQUE для email.

3. Незалежні множини: багатозначна залежність і 4НФ

Уявімо одну таблицю:

MentorCapability(mentor_id, language_id, technology_id)

Для наставника 17 задано дві мови й три технології. Оскільки множини незалежні, таблиця мусить містити декартовий добуток: 2 × 3 = 6 рядків.

mentor_id	language_id	technology_id
17	uk	PostgreSQL
17	uk	Python
17	uk	Docker
17	en	PostgreSQL
17	en	Python
17	en	Docker

Тут існують дві нетривіальні багатозначні залежності:

mentor_id ↠ language_id
mentor_id ↠ technology_id

Позначення X ↠ Y означає: для одного X існує множина значень Y, яка не залежить від решти атрибутів відношення. Це не те саме, що функціональна залежність: mentor_id не визначає одну мову, а визначає незалежну множину мов.

Практичний сигнал порушення 4НФ такий: в одному відношенні зберігаються дві або більше незалежні множини фактів про той самий об'єкт, через що всі комбінації штучно повторюються.

Правильна декомпозиція:

MentorLanguage(mentor_id, language_id)
MentorTechnology(mentor_id, technology_id)

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

Як перевірити 4НФ на практиці

Поставте три запитання:

Чи є в таблиці кілька множин значень для одного ключа?
Чи може елемент однієї множини змінюватися незалежно від елементів іншої?
Чи змушує таблиця зберігати всі комбінації цих множин?

Три відповіді «так» є сильною підставою розділити факти. Але якщо мова стосується конкретної технології, наприклад наставник викладає Python англійською, а PostgreSQL українською, незалежності немає. Тоді трійка має власний зміст, і механічна декомпозиція втратить його.

4. Залежність з'єднання і 5НФ

Розглянемо допустимість призначення:

EligibleMentoring(mentor_id, technology_id, project_type_id)

Звичайний рядок трійки може бути окремим фактом. Проте вимога 7 встановлює точніше правило: трійка дозволена тоді й лише тоді, коли наявні всі три парні сумісності.

Тому зберігаємо:

MentorTechnology(mentor_id, technology_id)
MentorProjectType(mentor_id, project_type_id)
TechnologyProjectType(technology_id, project_type_id)

Для початкової трійки діє залежність з'єднання:

EligibleMentoring =
  MentorTechnology
  ⋈ MentorProjectType
  ⋈ TechnologyProjectType

Залежність з'єднання означає, що відношення можна точно відновити природним з'єднанням кількох його проєкцій. 5НФ вимагає, щоб кожна нетривіальна залежність з'єднання випливала з кандидатних ключів. Практичний сенс: таблиця не повинна зберігати складені факти, які повністю визначаються меншими фактами, якщо декомпозиція справді відновлюється без втрат.

5НФ трапляється рідше за 3НФ або BCNF. Її варто перевіряти, коли:

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

Головна перевірка: чи не виникають хибні кортежі

Припустімо, є всі три пари:

(Олена, PostgreSQL)
(Олена, вебзастосунок)
(PostgreSQL, вебзастосунок)

З'єднання утворить трійку (Олена, PostgreSQL, вебзастосунок). За нашими вимогами вона правильна.

Але якби замовник сказав: «Олена знає PostgreSQL і веде вебпроєкти, але саме PostgreSQL-вебпроєкти вона тимчасово не бере», трьох пар було б недостатньо. З'єднання породило б хибний кортеж. У такій предметній області потрібно зберігати трійку або окреме правило-виняток; запропонована декомпозиція не була б безвтратною за змістом.

Отже, 5НФ не можна встановити лише за формою таблиці. Потрібно перевірити бізнес-твердження:

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

5. Підсумкова нормалізована схема кейсу

Після аналізу отримуємо:

Mentor(
  mentor_id PK,
  full_name,
  email UNIQUE
)

Language(language_id PK, code UNIQUE, name)
Technology(technology_id PK, name UNIQUE)
ProjectType(project_type_id PK, name UNIQUE)

MentorLanguage(
  mentor_id PK FK -> Mentor,
  language_id PK FK -> Language
)

MentorTechnology(
  mentor_id PK FK -> Mentor,
  technology_id PK FK -> Technology
)

MentorProjectType(
  mentor_id PK FK -> Mentor,
  project_type_id PK FK -> ProjectType
)

TechnologyProjectType(
  technology_id PK FK -> Technology,
  project_type_id PK FK -> ProjectType
)

Позначення PK біля двох атрибутів однієї таблиці означає складений первинний ключ. Він не дозволяє двічі записати той самий парний факт. Зовнішні ключі не дозволяють посилатися на неіснуючі сутності.

Таблиця EligibleMentoring не потрібна як джерело істини, тому що за затвердженою вимогою результат точно обчислюється з трьох парних таблиць. Водночас MentorTechnology виконує дві ролі: зберігає незалежну множину технологій наставника для 4НФ і бере участь у залежності з'єднання для 5НФ.

Перевірка схеми тестовими операціями

Не обмежуйтеся переглядом діаграми. Перевірте зміни:

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

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

6. Коли денормалізація виправдана

Нормалізована схема зменшує надлишковість і локалізує зміни, але читання іноді потребує кількох з'єднань. Це не є автоматичною проблемою. Сучасна СКБД призначена для JOIN, а правильність даних зазвичай важливіша за неперевірене припущення про швидкість.

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

Рішення можна розглядати лише тоді, коли відомо:

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

Компроміси

Рішення	Перевага	Ризик
Обчислювати допустимі трійки з нормалізованих таблиць	Одне джерело істини	Дорожчий запит для великої кількості комбінацій
Матеріалізувати результат	Швидке повторне читання	Потрібне оновлення; результат може відставати
Зберігати лічильник у `Mentor`	Швидке відображення профілю	Лічильник може не відповідати деталям
Кешувати готову відповідь	Менше навантаження на БД	Потрібні правила інвалідації кешу

Без метрик формулювання «зробимо одну широку таблицю, щоб не було JOIN» не є обґрунтуванням. Воно лише переносить складність із читання в кожну операцію зміни.

7. Чекліст верифікації схеми

Перед затвердженням структури дайте відповідь на кожне запитання.

Вимоги й факти

Чи кожна таблиця має одне чітке речення «один рядок означає...»?
Чи всі обов'язкові факти з вимог можна зберегти?
Чи не додано фактів, яких вимоги не передбачають?
Чи зафіксовано винятки й умови достатності правил?

Ключі й залежності

Чи визначено всі кандидатні ключі, а не лише технічний id?
Чи реалізовано природну унікальність через UNIQUE?
Чи кожна заявлена залежність підтверджена правилом предметної області?
Чи немає в одній таблиці незалежних багатозначних фактів?
Чи перевірено таблиці з трьома й більше зовнішніми ключами на залежності з'єднання?

Декомпозиція

Чи відновлює з'єднання саме початкові факти?
Чи не виникають хибні кортежі?
Чи не втрачено важливе обмеження, яке тепер неможливо перевірити?
Чи можна додати, змінити й видалити один факт без штучних повторів?

Експлуатація

Які операції читання та зміни є найважливішими?
Чи підкріплені рішення про продуктивність вимірюваннями?
Якщо додано надлишковість, де визначено джерело істини?
Як синхронізуються, перевіряються і відновлюються копії?

Схема готова не тоді, коли виглядає охайно, а тоді, коли рішення можна пояснити вимогами та перевірити прикладами і контрприкладами.

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

Завдання 1. Розпізнайте незалежні множини

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

Запишіть дві багатозначні залежності від mentor_id.
Поясніть, чому таблиця MentorAvailability(mentor_id, certificate_id, timezone_id) створює штучні комбінації.
Запропонуйте дві таблиці у 4НФ та їхні ключі.

Завдання 2. Перевірте можливість 5НФ-декомпозиції

Для призначення консультанта задані пари ConsultantSkill, ConsultantIndustry та SkillIndustry.

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

Завдання 3. Проведіть рев'ю рішення

Команда пропонує зберігати в Mentor поля languages_csv, technologies_csv, eligible_projects_json і technology_count, пояснюючи це тим, що «так профіль читається одним запитом».

Визначте, які факти та обмеження важко перевіряти в такій структурі.
Запропонуйте нормалізоване джерело істини.
Назвіть метрики, які потрібно зібрати до денормалізації.
Якщо вимірювання справді покажуть проблему, оберіть один контрольований спосіб прискорення та опишіть його синхронізацію.
Перевірте остаточне рішення за чеклістом цієї лекції.

Підсумок

Проєктування починається з вимог, гранулярності фактів і залежностей, а не з переліку таблиць.
Багатозначна залежність описує незалежну множину значень; 4НФ усуває штучне комбінування незалежних фактів.
Залежність з'єднання дозволяє точно відновити складений факт із проєкцій; 5НФ потрібна для рідкісних, але важливих складних комбінацій.
Парні сумісності можна з'єднувати замість зберігання трійки лише тоді, коли вони є достатніми за бізнес-правилом.
Хибний кортеж є доказом того, що запропонована декомпозиція не відповідає вимогам.
Денормалізація потребує виміряного вузького місця, визначеного джерела істини та механізму синхронізації.
Завершальний крок проєктування - верифікація схеми операціями, прикладами, контрприкладами й чеклістом.