Моделювання зв'язків між таблицями для нормалізації

Сьогодні ми зануримось у дуже важливу тему: моделювання зв'язків між таблицями. Бо нормалізація — це не тільки про атомарні дані та видалення надлишковості, але й про створення правильних зв'язків між таблицями.

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

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

Основні типи таких зв'язків працюють як стосунки в реальному житті: паспорт належить тільки одній людині (один-до-одного), один викладач може вести кілька курсів (один-до-багатьох), а студенти можуть записуватись на різні предмети, при цьому кожен предмет відвідують різні студенти (багато-до-багатьох).

Один-до-одного (1:1)

Це зв'язок, у якому один запис у таблиці "А" відповідає строго одному запису в таблиці "Б". Наприклад, у нас є таблиці "Працівники" і "Паспортні дані". Один працівник може мати тільки один паспорт, і кожен паспорт належить єдиному працівнику.

Приклад:

Працівники

Паспортні дані

Тут зв'язок здійснюється через зовнішній ключ працівник_id, який вказує на id у таблиці "Працівники".

Один-до-багатьох (1:N)

Це найпоширеніший тип зв'язку. Тут кожен запис з таблиці "А" може бути пов'язаний з кількома записами в таблиці "Б", але кожен запис з таблиці "Б" пов'язаний тільки з одним записом з таблиці "А". Наприклад, у нас є таблиці "Викладачі" і "Курси". Один викладач може вести кілька курсів.

Приклад:

Викладачі

Курси

Зв'язок створюється через зовнішній ключ викладач_id у таблиці "Курси".

Багато-до-багатьох (M:N)

Коли у тебе багато-багато всього, це весело, але складно. Тут кожен запис у таблиці "А" може бути пов'язаний з кількома записами з таблиці "Б", і навпаки. Наприклад, студенти можуть записуватись на кілька курсів, а кожен курс може відвідувати кілька студентів.

Приклад:

Студенти

Курси

Для зв'язування нам потрібна проміжна таблиця, яка буде зберігати відповідності між студентами і курсами:

Записи

Моделювання зв'язків за допомогою зовнішніх ключів

Зовнішній ключ — це колонка (або набір колонок), яка вказує на колонку первинного ключа в іншій таблиці. Це основа для побудови зв'язків між таблицями.

Приклад зовнішнього ключа:

CREATE TABLE Курси (
    id SERIAL PRIMARY KEY,
    назва VARCHAR(255)
);

CREATE TABLE Записи (
    id SERIAL PRIMARY KEY,
    студент_id INT,
    курс_id INT,
    FOREIGN KEY (курс_id) REFERENCES Курси(id)
);

Як уникнути помилок при проєктуванні зовнішніх ключів? Насамперед, важливо перевірити, щоб типи даних між стовпцями зовнішнього і первинного ключа співпадали — інакше база просто не дозволить створити зв'язок. А ще варто заздалегідь продумати, що має відбуватись при видаленні записів. Наприклад, якщо ти видаляєш рядок з батьківської таблиці, що робити з дочірніми записами? Один з популярних варіантів — використовувати ON DELETE CASCADE, щоб пов'язані дані автоматично видалялися разом з основним записом. Це допомагає підтримувати порядок і уникати «висячих» посилань.

Реалізація зв'язку "Багато-до-багатьох"

Візьмемо приклад: у нас є студенти і курси. Один студент може бути записаний на кілька курсів, а на один курс може бути записано кілька студентів. Для реалізації зв'язку M:N створимо три таблиці: Студенти, Курси і Записи.

CREATE TABLE Студенти (
    id SERIAL PRIMARY KEY,
    ім'я VARCHAR(255)
);

CREATE TABLE Курси (
    id SERIAL PRIMARY KEY,
    назва VARCHAR(255)
);

CREATE TABLE Записи (
    id SERIAL PRIMARY KEY,
    студент_id INT,
    курс_id INT,
    FOREIGN KEY (студент_id) REFERENCES Студенти(id),
    FOREIGN KEY (курс_id) REFERENCES Курси(id)
);

Тепер ми можемо додавати записи в таблицю Записи, щоб зв'язувати студентів і курси.

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

Створи структуру бази даних для системи управління курсами. У тебе мають бути таблиці Студенти, Курси і Записи. Реалізуй всі зв'язки між таблицями. Потім внеси прикладові дані про студентів, курси і їх записи. Давай подивимось, як це треба робити.

1. Створюємо таблиці:

CREATE TABLE Студенти (
    id SERIAL PRIMARY KEY,
    ім'я VARCHAR(255)
);

CREATE TABLE Курси (
    id SERIAL PRIMARY KEY,
    назва VARCHAR(255)
);

CREATE TABLE Записи (
    id SERIAL PRIMARY KEY,
    студент_id INT,
    курс_id INT,
    FOREIGN KEY (студент_id) REFERENCES Студенти(id),
    FOREIGN KEY (курс_id) REFERENCES Курси(id)
);

2. Вставляємо дані:

INSERT INTO Студенти (ім'я) VALUES ('Отто Лін'), ('Марія Чі');
INSERT INTO Курси (назва) VALUES ('Основи SQL'), ('Адміністрування БД');
INSERT INTO Записи (студент_id, курс_id) VALUES (1, 1), (1, 2), (2, 1);

3. Перевіряємо дані:

SELECT
    Студенти.ім'я AS студент, 
    Курси.назва AS курс
FROM Записи
JOIN Студенти ON Записи.студент_id = Студенти.id
JOIN Курси ON Записи.курс_id = Курси.id;

Результат:

Складнощі та особливості моделювання зв'язків

Коли ти моделюєш зв'язки між таблицями, можуть виникати складнощі, такі як:

• Помилки при видаленні даних (наприклад, у тебе є записи в дочірній таблиці, які залежать від запису в батьківській таблиці).
• Продуктивність запитів при великій кількості даних. Зв'язки M:N особливо "ненажерливі", бо вимагають додаткових join-ів.

Вирішити ці проблеми допомагають:

• Використання індексів на зовнішні ключі
• Продумана структура бази даних.
• Баланс між нормалізацією і продуктивністю.

Ми розглянули моделювання зв'язків між таблицями на дуже примітивному рівні і застосували це на практиці, створивши структуру бази даних для системи управління курсами. Хотілося б, звісно, розібрати якийсь великий приклад, але я не придумав як це зробити. Великий приклад виходить складним і нудним. І толку з нього буде мало. Я спробую повернутись до цього питання ближче до кінця курсу.