Modelowanie relacji MANY-TO-MANY za pomocą tabeli pośredniej

Relacja "wiele-do-wielu" to taka sytuacja, gdzie jeden rekord w jednej tabeli może być powiązany z wieloma rekordami w drugiej tabeli i na odwrót. Przykład: - Jeden student (z tabeli students) może być zapisany na kilka kursów (z tabeli courses). - Jeden kurs może być uczęszczany przez kilku studentów.

Problem w tym, że trzymanie takiej relacji bezpośrednio jest mega niewygodne. Dlatego z pomocą przychodzi tabela pośrednia, która przechowuje te powiązania.

Przykład z życia wzięty

Wyobraź sobie, że masz tabelę studentów i tabelę kursów. Jeśli spróbujesz dodać do każdej tabeli kolumnę na wszystkie powiązane dane, zrobi się totalny chaos:

• W tabeli students musiałbyś trzymać listę wszystkich kursów, na które student jest zapisany. Ale jak przechowywać listę? Przecinkami? W tablicy? To będzie masakra przy robieniu zapytań.
• W tabeli courses musiałbyś trzymać listę studentów, a to już w ogóle ból głowy.

Dlatego najlepsze rozwiązanie to stworzyć trzecią tabelę, która będzie trzymać powiązania między studentami a kursami.

Tabela pośrednia: nasze wybawienie!

Tabela pośrednia (czasem nazywana tabelą powiązań) rozwiązuje wszystkie problemy. Zawiera dwa klucze obce:

• Klucz obcy do tabeli students.
• Klucz obcy do tabeli courses.

Każdy rekord w tej tabeli tworzy powiązanie między konkretnym studentem a konkretnym kursem.

Tworzenie tabel dla relacji "wiele-do-wielu"

No to lecimy z praktyką! Tak możemy stworzyć tabele do powiązań między studentami a kursami:

Krok 1: Tworzenie tabeli students

To nasza tabela studentów. Tutaj trzymamy unikalne identyfikatory studentów i ich imiona.

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

• student_id — to unikalny identyfikator studenta (autoinkrementacja, dzięki SERIAL!).
• name — imię studenta.

Krok 2: Tworzenie tabeli courses

Teraz tworzymy tabelę kursów. Tutaj trzymamy unikalne identyfikatory kursów i ich nazwy.

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

• course_id — unikalny identyfikator kursu.
• title — nazwa kursu.

Krok 3: Tworzenie tabeli pośredniej enrollments

Teraz tworzymy naszą magiczną tabelę powiązań. Ma dwie kolumny, każda jest kluczem obcym wskazującym na odpowiednie tabele.

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

Rozkładamy strukturę na czynniki pierwsze:

• student_id — klucz obcy wskazujący na student_id z tabeli students.
• course_id — klucz obcy wskazujący na course_id z tabeli courses.
• PRIMARY KEY (student_id, course_id) — klucz główny to kombinacja dwóch kluczy obcych. To gwarantuje, że każde powiązanie będzie unikalne.

Wstawianie danych

No to wrzucamy trochę danych, żeby zobaczyć jak to działa.

Krok 1: Dodawanie studentów

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

Efekt:

Krok 2: Dodawanie kursów

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

Efekt:

Krok 3: Dodawanie rekordów do enrollments

Teraz zapisujemy studentów na kursy. Przykładowo:

• Alice jest zapisana na Mathematics i History.
• Bob jest zapisany tylko na Biology.
• Charlie jest zapisany na wszystkie trzy kursy.

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

Efekt:

Zapytania dla relacji "wiele-do-wielu"

Skoro mamy już dane, czas wyciągnąć z nich coś fajnego!

Jak znaleźć wszystkie kursy, na które zapisany jest student?

Na przykład, żeby sprawdzić, na jakie kursy zapisana jest Alice (ID = 1), zrób takie zapytanie:

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

Efekt:

Jak znaleźć wszystkich studentów zapisanych na konkretny kurs?

Załóżmy, że chcesz sprawdzić, kto jest zapisany na kurs Mathematics (ID = 1):

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

Efekt:

Jak znaleźć studentów i ich kursy?

Żeby zobaczyć pełny obraz, kto na co jest zapisany, zrób takie zapytanie:

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;

Efekt:

Tabela enrollments robi nasz schemat mega elastycznym — możemy bez problemu dodawać albo usuwać powiązania między studentami a kursami, nie ruszając głównych tabel. Dzięki zapytaniom JOIN łatwo znaleźć potrzebne dane, np. kto jest zapisany na jaki kurs. A klucze obce automatycznie pilnują, żeby nie było baboli — np. żeby nikt przypadkiem nie zapisał studenta na kurs, którego w ogóle nie ma.

Typowe błędy w relacjach "wiele-do-wielu"

Brak unikalnego ograniczenia: Jeśli nie ustawisz PRIMARY KEY, możesz przypadkiem dodać to samo powiązanie kilka razy.

Naruszenie integralności danych: Próba wstawienia rekordu z nieistniejącym student_id albo course_id skończy się błędem.

Zła kolejność usuwania danych: Jeśli najpierw usuniesz kurs z courses, rekordy w enrollments zostaną "sierotami". Żeby tego uniknąć, użyj ON DELETE CASCADE w definicji kluczy obcych.