Моделювання зв'язку MANY-TO-MANY за допомогою проміжної таблиці

Зв'язок "багато-до-багатьох" — це коли один запис в одній таблиці може бути пов'язаний з кількома записами в іншій таблиці, і навпаки. Наприклад: - Один студент (з таблиці students) може бути записаний на кілька курсів (з таблиці courses). - Один курс може відвідувати кілька студентів.

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

Приклад з реального життя

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

• У таблиці students доведеться зберігати список усіх курсів, на які записаний студент. Але як зберігати список? Через кому? В масиві? Це буде складно для обробки запитів.
• У таблиці courses доведеться зберігати список студентів, а це взагалі головний біль.

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

Проміжна таблиця: наше спасіння!

Проміжна таблиця (іноді її називають таблицею зв'язків) вирішує всі проблеми. Вона містить дві зовнішні ссилки:

• Зовнішній ключ на таблицю students.
• Зовнішній ключ на таблицю courses.

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

Створення таблиць для зв'язку "багато-до-багатьох"

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

Крок 1: Створення таблиці students

Ось наша таблиця для студентів. Тут ми зберігаємо унікальні ідентифікатори студентів і їх імена.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id — це унікальний ідентифікатор студента (автоінкремент, дякуємо SERIAL!).
• name — ім'я студента.

Крок 2: Створення таблиці courses

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

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id — унікальний ідентифікатор курсу.
• title — назва курсу.

Крок 3: Створення проміжної таблиці enrollments

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

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Розберемо структуру:

• student_id — зовнішній ключ, що посилається на student_id з таблиці students.
• course_id — зовнішній ключ, що посилається на course_id з таблиці courses.
• PRIMARY KEY (student_id, course_id) — первинний ключ складається з комбінації двох зовнішніх ключів. Це гарантує, що кожен зв'язок буде унікальним.

Вставка даних

Давай додамо трохи даних, щоб перевірити, як все працює.

Крок 1: Додавання студентів

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Результат:

Крок 2: Додавання курсів

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

Результат:

Крок 3: Додавання записів у enrollments

Тепер запишемо студентів на курси. Наприклад:

• Alice записана на Mathematics і History.
• Bob записаний тільки на Biology.
• Charlie записаний на всі три курси.

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice на Mathematics
(1, 2), -- Alice на History
(2, 3), -- Bob на Biology
(3, 1), -- Charlie на Mathematics
(3, 2), -- Charlie на History
(3, 3); -- Charlie на Biology

Результат:

Запити для зв'язку "багато-до-багатьох"

Тепер, коли у нас є дані, час отримати з них користь!

Як знайти всі курси, на які записаний студент?

Наприклад, щоб дізнатися, на які курси записана Alice (ID = 1), виконай запит:

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

Результат:

Як знайти всіх студентів, записаних на конкретний курс?

Допустимо, ми хочемо дізнатися, хто записаний на курс Mathematics (ID = 1):

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

Результат:

Як знайти студентів і їх курси?

Щоб отримати повну картину, хто на що записаний, виконаємо запит:

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Результат:

Таблиця enrollments робить нашу схему гнучкою — ми можемо без проблем додавати або видаляти зв'язки між студентами і курсами, не чіпаючи основні таблиці. Завдяки JOIN-запитам легко знаходити потрібні дані, наприклад, хто записаний на який курс. А зовнішні ключі автоматично слідкують за тим, щоб не виникало помилок — наприклад, щоб ніхто випадково не записав студента на курс, якого взагалі не існує.

Типові помилки у зв'язках "багато-до-багатьох"

Відсутність унікального обмеження: Якщо не встановити PRIMARY KEY, можна випадково додати один і той самий зв'язок кілька разів.

Порушення цілісності даних: Спроба вставити запис з неіснуючим student_id або course_id призведе до помилки.

Неправильний порядок видалення даних: Якщо спочатку видалити курс з courses, записи в enrollments залишаться "сиротами". Щоб цього не сталося, використовуй ON DELETE CASCADE у визначенні зовнішніх ключів.