Створення таблиці enrollments для зв'язку студентів і курсів

Ще трохи позанудствуємо. Давай так само детально пройдемося по кроках створення структури MANY-TO-MANY. Готовий?

Зв'язок MANY-TO-MANY між студентами і курсами не можна представити напряму в одній таблиці. Один студент може бути записаний на кілька курсів, а один курс може відвідуватися одразу кількома студентами.

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

Як виглядає таблиця enrollments?

Ти вже знаєш, що таблиця enrollments буде центральним вузлом зв'язку між таблицями students і courses. Ось її структура:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Унікальний ID запису
    student_id INT REFERENCES students(student_id), -- Зовнішній ключ на таблицю students
    course_id INT REFERENCES courses(course_id),    -- Зовнішній ключ на таблицю courses
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Коли студент був зарахований на курс
);

Розберемо кожен рядок:

• enrollment_id: Це унікальний ідентифікатор кожного запису. Адже кожен зарахований на курс студент має бути унікально ідентифікований.
• student_id: Вказує, який студент зарахований. Це зовнішній ключ, який посилається на таблицю students (стовпець student_id).
• course_id: Вказує, на який курс студент зарахований. Цей стовпець пов'язаний з таблицею courses (стовпець course_id).
• enrollment_date: Корисне доповнення, яке показує дату зарахування. Ми використовуємо DEFAULT CURRENT_DATE, щоб автоматично вказувати поточну дату при створенні запису.

Створення таблиць students і courses

Перш ніж рухатися далі, переконаймося, що у нас вже є таблиці students і courses:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- Унікальний ідентифікатор студента
    name TEXT NOT NULL,                 -- Ім'я студента
    email TEXT NOT NULL UNIQUE          -- Email студента, щоб не було дублікатів
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- Унікальний ідентифікатор курсу
    title TEXT NOT NULL,                -- Назва курсу
    description TEXT,                   -- Опис курсу
    start_date DATE                     -- Дата початку курсу
);

Зверни увагу, що тут ми додали корисні деталі, наприклад, унікальні e-mail для студентів і опис курсу в таблиці courses.

Зв'язуємо все разом

Тепер, коли наші таблиці готові, створимо таблицю enrollments:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Унікальний ID зарахування
    student_id INT NOT NULL REFERENCES students(student_id), -- Зовнішній ключ
    course_id INT NOT NULL REFERENCES courses(course_id),    -- Зовнішній ключ
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Дата зарахування
);

Вставка даних у таблиці

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

Вставка студентів:

INSERT INTO students (name, email)
VALUES
    ('Alex Lin', 'alex.lin@example.com'),
    ('Maria Chi', 'maria.chi@example.com'),
    ('Otto Song', 'otto.song@example.com');

Вставка курсів:

INSERT INTO courses (title, description, start_date)
VALUES
    ('Основи програмування', 'Курс для початківців програмістів.', '2023-11-01'),
    ('Бази даних', 'Вивчаємо SQL і реляційні бази даних.', '2023-11-15'),
    ('Веб-розробка', 'Створення сайтів і веб-додатків.', '2023-12-01');

Вставка записів про зарахування:

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin на "Основи програмування"
    (1, 2), -- Alex Lin на "Бази даних"
    (2, 2), -- Maria Chi на "Бази даних"
    (3, 3); -- Otto Song на "Веб-розробка"

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

Перевіряємо зв'язки за допомогою запитів

Отримання всіх зарахувань:

SELECT e.enrollment_id, s.name AS student_name, c.title AS course_title, e.enrollment_date
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses c ON e.course_id = c.course_id;

Результат:

Завдання для самостійної практики

Спробуй додати ще кількох студентів і курсів, а потім зарахувати їх у таблицю enrollments. Наприклад, додай курс "Машинне навчання" і зарахуй туди 1-2 студентів. Використовуй наведений вище запит для перевірки результату.

Можливі типові помилки

Працюючи із зовнішніми ключами та проміжними таблицями, є кілька пасток, у які часто потрапляють:

1. Відсутність запису в батьківській таблиці: Якщо ти спробуєш додати запис у enrollments з student_id або course_id, яких немає в таблицях students чи courses, отримаєш помилку. Зовнішній ключ суворо це контролює.

2. Порушення цілісності даних при видаленні: Якщо ти видалиш студента або курс, які вже використовуються в таблиці enrollments, без налаштованого ON DELETE CASCADE, це призведе до помилки.

3. Дублювання записів: Переконайся, що ти не додаєш студента на один і той самий курс кілька разів, якщо це не передбачено бізнес-логікою.

Тепер у тебе є робоча модель для представлення зв'язку MANY-TO-MANY між студентами і курсами в PostgreSQL. Така структура дуже часто використовується в реальних додатках, наприклад, у системах управління навчанням, CRM та багатьох інших. Вперед до наступної лекції!