Modelowanie relacji między tabelami dla normalizacji

Dzisiaj zagłębimy się w mega ważny temat: modelowanie relacji między tabelami. Bo normalizacja to nie tylko atomowe dane i usuwanie nadmiarowości, ale też tworzenie poprawnych relacji między tabelami.

Jeśli bazy danych to zorganizowany system przechowywania informacji, to relacje między tabelami to logiczne mosty, które pokazują, jak dane się ze sobą łączą. Wyobraź sobie bibliotekę, gdzie info o książkach jest osobno od info o autorach, ale każda książka "zna" swojego autora przez specjalną relację. Albo sklep internetowy: dane o produktach istnieją niezależnie od info o klientach, ale jak ktoś robi zamówienie, system łączy konkretnego klienta z konkretnymi produktami przez tabelę zamówień.

W klinice medycznej pacjenci są powiązani ze swoimi kartami medycznymi, lekarze — z grafikiem wizyt, a leki — z receptami. Te relacje pomagają systemowi ogarnąć, która informacja do czego należy, bez niepotrzebnego dublowania danych.

Podstawowe typy takich relacji działają jak relacje w realu: paszport należy tylko do jednej osoby (jeden-do-jednego), jeden wykładowca może prowadzić kilka kursów (jeden-do-wielu), a studenci mogą zapisywać się na różne przedmioty, przy czym każdy przedmiot odwiedzają różni studenci (wielu-do-wielu).

Jeden-do-jednego (1:1)

To relacja, gdzie jeden rekord w tabeli "A" odpowiada dokładnie jednemu rekordowi w tabeli "B". Na przykład mamy tabele "Pracownicy" i "Dane paszportowe". Jeden pracownik może mieć tylko jeden paszport i każdy paszport należy do jednego pracownika.

Przykład:

Pracownicy

Dane paszportowe

Tutaj relacja jest realizowana przez foreign key pracownik_id, który wskazuje na id w tabeli "Pracownicy".

Jeden-do-wielu (1:N)

To najczęstszy typ relacji. Tutaj każdy rekord z tabeli "A" może być powiązany z wieloma rekordami w tabeli "B", ale każdy rekord z tabeli "B" jest powiązany tylko z jednym rekordem z tabeli "A". Na przykład mamy tabele "Wykładowcy" i "Kursy". Jeden wykładowca może prowadzić kilka kursów.

Przykład:

Wykładowcy

Kursy

Relacja jest tworzona przez foreign key wykładowca_id w tabeli "Kursy".

Wielu-do-wielu (M:N)

Kiedy masz dużo-dużo wszystkiego, to jest fajnie, ale i trudno. Tutaj każdy rekord w tabeli "A" może być powiązany z wieloma rekordami z tabeli "B" i odwrotnie. Na przykład studenci mogą zapisywać się na kilka kursów, a każdy kurs może mieć kilku studentów.

Przykład:

Studenci

Kursy

Do powiązania potrzebujemy tabeli pośredniej, która będzie przechowywać powiązania między studentami a kursami:

Zapisy

Modelowanie relacji za pomocą foreign key

Foreign key to kolumna (albo zestaw kolumn), która wskazuje na kolumnę primary key w innej tabeli. To podstawa budowania relacji między tabelami.

Przykład foreign key:

CREATE TABLE Kursy (
    id SERIAL PRIMARY KEY,
    nazwa VARCHAR(255)
);

CREATE TABLE Zapisy (
    id SERIAL PRIMARY KEY,
    student_id INT,
    kurs_id INT,
    FOREIGN KEY (kurs_id) REFERENCES Kursy(id)
);

Jak uniknąć błędów przy projektowaniu foreign key? Najważniejsze to sprawdzić, żeby typy danych między kolumnami foreign i primary key się zgadzały — inaczej baza po prostu nie pozwoli utworzyć relacji. Warto też od razu przemyśleć, co ma się dziać przy usuwaniu rekordów. Na przykład, jeśli usuwasz wiersz z tabeli nadrzędnej, co z powiązanymi rekordami w podrzędnej? Jedna z popularnych opcji to użycie ON DELETE CASCADE, żeby powiązane dane były automatycznie usuwane razem z głównym rekordem. To pomaga utrzymać porządek i uniknąć "wiszących" referencji.

Implementacja relacji "Wielu-do-wielu"

Weźmy przykład: mamy studentów i kursy. Jeden student może być zapisany na kilka kursów, a na jeden kurs może być zapisanych kilku studentów. Żeby zrobić relację M:N, tworzymy trzy tabele: Studenci, Kursy i Zapisy.

CREATE TABLE Studenci (
    id SERIAL PRIMARY KEY,
    imię VARCHAR(255)
);

CREATE TABLE Kursy (
    id SERIAL PRIMARY KEY,
    nazwa VARCHAR(255)
);

CREATE TABLE Zapisy (
    id SERIAL PRIMARY KEY,
    student_id INT,
    kurs_id INT,
    FOREIGN KEY (student_id) REFERENCES Studenci(id),
    FOREIGN KEY (kurs_id) REFERENCES Kursy(id)
);

Teraz możemy dodawać rekordy do tabeli Zapisy, żeby łączyć studentów i kursy.

Zadanie praktyczne

Stwórz strukturę bazy danych dla systemu zarządzania kursami. Powinieneś mieć tabele Studenci, Kursy i Zapisy. Zrób wszystkie relacje między tabelami. Potem dodaj przykładowe dane o studentach, kursach i ich zapisach. Zobaczmy, jak to zrobić.

1. Tworzymy tabele:

CREATE TABLE Studenci (
    id SERIAL PRIMARY KEY,
    imię VARCHAR(255)
);

CREATE TABLE Kursy (
    id SERIAL PRIMARY KEY,
    nazwa VARCHAR(255)
);

CREATE TABLE Zapisy (
    id SERIAL PRIMARY KEY,
    student_id INT,
    kurs_id INT,
    FOREIGN KEY (student_id) REFERENCES Studenci(id),
    FOREIGN KEY (kurs_id) REFERENCES Kursy(id)
);

2. Wstawiamy dane:

INSERT INTO Studenci (imię) VALUES ('Otto Lin'), ('Maria Chi');
INSERT INTO Kursy (nazwa) VALUES ('Podstawy SQL'), ('Administracja BD');
INSERT INTO Zapisy (student_id, kurs_id) VALUES (1, 1), (1, 2), (2, 1);

3. Sprawdzamy dane:

SELECT
    Studenci.imię AS student, 
    Kursy.nazwa AS kurs
FROM Zapisy
JOIN Studenci ON Zapisy.student_id = Studenci.id
JOIN Kursy ON Zapisy.kurs_id = Kursy.id;

Wynik:

Trudności i specyfika modelowania relacji

Kiedy modelujesz relacje między tabelami, mogą pojawić się trudności, takie jak:

• Błędy przy usuwaniu danych (np. masz rekordy w tabeli podrzędnej, które zależą od rekordu w tabeli nadrzędnej).
• Wydajność zapytań przy dużej ilości danych. Relacje M:N są szczególnie "żarłoczne", bo wymagają dodatkowych joinów.

Pomagają w tym:

• Używanie indeksów na foreign key
• Przemyślana struktura bazy danych.
• Balans między normalizacją a wydajnością.

Przerobiliśmy modelowanie relacji między tabelami na bardzo podstawowym poziomie i zastosowaliśmy to w praktyce, tworząc strukturę bazy danych dla systemu zarządzania kursami. Chciałbym oczywiście rozkminić jakiś duży przykład, ale nie wymyśliłem jak to zrobić. Duży przykład wychodzi skomplikowany i nudny. I niewiele z niego będzie pożytku. Spróbuję wrócić do tego tematu bliżej końca kursu.