Tworzenie tabeli enrollments do powiązania studentów i kursów

Jeszcze trochę ponudzimy. Przejdźmy tak samo dokładnie przez kroki tworzenia struktury MANY-TO-MANY. Gotowi?

Relacji MANY-TO-MANY między studentami a kursami nie da się ogarnąć bezpośrednio w jednej tabeli. Jeden student może być zapisany na kilka kursów, a jeden kurs może być ogarniany przez kilku studentów naraz.

Żeby to ogarnąć, tworzymy pośrednią tabelę enrollments, która będzie trzymać info o zapisach, czyli która para student-kurs istnieje. Dzięki temu nie tylko dbamy o spójność danych, ale też łatwo możemy rozbudować funkcjonalność, np. dodać datę zapisu.

Jak wygląda tabela enrollments?

Pewnie już wiesz, że tabela enrollments będzie centralnym węzłem między tabelami students i courses. Oto jej struktura:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Unikalny ID rekordu
    student_id INT REFERENCES students(student_id), -- Klucz obcy do tabeli students
    course_id INT REFERENCES courses(course_id),    -- Klucz obcy do tabeli courses
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Kiedy student został zapisany na kurs
);

Rozkminiamy każdą linijkę:

• enrollment_id: To unikalny identyfikator każdego rekordu. Każdy student zapisany na kurs musi być jednoznacznie zidentyfikowany.
• student_id: Pokazuje, który student jest zapisany. To klucz obcy, który wskazuje na tabelę students (kolumna student_id).
• course_id: Pokazuje, na jaki kurs student jest zapisany. Ta kolumna jest powiązana z tabelą courses (kolumna course_id).
• enrollment_date: Przydatny dodatek, który pokazuje datę zapisu. Używamy DEFAULT CURRENT_DATE, żeby automatycznie ustawić bieżącą datę przy tworzeniu rekordu.

Tworzenie tabel students i courses

Zanim pójdziemy dalej, upewnijmy się, że mamy już tabele students i courses:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,      -- Unikalny identyfikator studenta
    name TEXT NOT NULL,                 -- Imię studenta
    email TEXT NOT NULL UNIQUE          -- Email studenta, żeby nie było duplikatów
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,       -- Unikalny identyfikator kursu
    title TEXT NOT NULL,                -- Nazwa kursu
    description TEXT,                   -- Opis kursu
    start_date DATE                     -- Data startu kursu
);

Zwróć uwagę, że tutaj dodaliśmy przydatne szczegóły, np. unikalne e-maile dla studentów i opis kursu w tabeli courses.

Łączymy wszystko razem

Teraz, gdy nasze tabele są gotowe, tworzymy tabelę enrollments:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,           -- Unikalny ID zapisu
    student_id INT NOT NULL REFERENCES students(student_id), -- Klucz obcy
    course_id INT NOT NULL REFERENCES courses(course_id),    -- Klucz obcy
    enrollment_date DATE DEFAULT CURRENT_DATE   -- Data zapisu
);

Wstawianie danych do tabel

No dobra, tabele są gotowe, ale nudzą się bez danych. Dodajmy kilku studentów, kursy i ich zapisy:

Wstawianie studentów:

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

Wstawianie kursów:

INSERT INTO courses (title, description, start_date)
VALUES
    ('Podstawy programowania', 'Kurs dla początkujących programistów.', '2023-11-01'),
    ('Bazy danych', 'Uczymy się SQL i relacyjnych baz danych.', '2023-11-15'),
    ('Web development', 'Tworzenie stron i aplikacji webowych.', '2023-12-01');

Wstawianie rekordów o zapisach:

INSERT INTO enrollments (student_id, course_id)
VALUES
    (1, 1), -- Alex Lin na "Podstawy programowania"
    (1, 2), -- Alex Lin na "Bazy danych"
    (2, 2), -- Maria Chi na "Bazy danych"
    (3, 3); -- Otto Song na "Web development"

Tutaj student_id i course_id odpowiadają identyfikatorom w odpowiednich tabelach.

Sprawdzamy powiązania za pomocą zapytań

Pobieranie wszystkich zapisów:

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;

Wynik:

Zadanie do samodzielnej praktyki

Spróbuj dodać jeszcze kilku studentów i kursów, a potem zapisz ich w tabeli enrollments. Na przykład dodaj kurs "Uczenie maszynowe" i zapisz tam 1-2 studentów. Użyj powyższego zapytania, żeby sprawdzić wynik.

Typowe wtopy

Przy pracy z kluczami obcymi i tabelami pośrednimi jest kilka pułapek, w które łatwo wpaść:

1. Brak rekordu w tabeli nadrzędnej: Jeśli spróbujesz dodać rekord do enrollments z student_id albo course_id, których nie ma w tabelach students lub courses, dostaniesz błąd. Klucz obcy pilnuje tego na maksa.

2. Zaburzenie spójności danych przy usuwaniu: Jeśli usuniesz studenta albo kurs, które już są używane w tabeli enrollments, bez ustawionego ON DELETE CASCADE, to skończy się błędem.

3. Duplikowanie rekordów: Upewnij się, że nie dodajesz studenta na ten sam kurs kilka razy, jeśli nie przewiduje tego logika biznesowa.

Teraz masz gotowy model do ogarniania relacji MANY-TO-MANY między studentami a kursami w PostgreSQL. Ta struktura jest mega często używana w prawdziwych apkach, jak systemy zarządzania nauką, CRM i wiele innych. Do następnego wykładu!