Modelowanie relacji ONE-TO-MANY między tabelami

No to czas podkręcić skilla i zrobić jedną z najczęściej spotykanych relacji w relacyjnych bazach danych — relację ONE-TO-MANY.

Wyobraź sobie małą firmę. Jeden pracownik może pracować tylko w jednym dziale, ale w jednym dziale może być dziesiątki takich pracowników. Mamy dwa obiekty ze świata rzeczywistego: pracownicy i działy. Relację między nimi można opisać jako "jeden dział może mieć wielu pracowników", albo formalnie "jeden do wielu" (ONE-TO-MANY).

Tak samo relacje ONE-TO-MANY są wszędzie:

• jeden klient może zrobić wiele zamówień;
• jeden autor może napisać wiele książek;
• jeden nauczyciel może uczyć kilku studentów.

W relacyjnej bazie danych relacja ONE-TO-MANY ogarnia się przez użycie klucza obcego (FOREIGN KEY). Jeden z kolumn tabeli "wiele" (MANY) wskazuje na klucz główny z tabeli "jeden" (ONE).

Jak stworzyć relację ONE-TO-MANY

Weźmy klasyczny przykład: relacja między klientami a zamówieniami. Jeden klient może zrobić wiele zamówień, ale każde zamówienie jest przypisane tylko do jednego klienta. Stworzymy dwie tabele: customers (klienci) i orders (zamówienia).

Tabela customers

To nasza tabela "jeden". Będzie trzymać info o klientach.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Unikalny identyfikator klienta
    name TEXT NOT NULL              -- Imię klienta
);

Tabela orders

To jest tabela "wiele". Trzyma zamówienia, gdzie każde zamówienie ma klucz obcy customer_id, który wskazuje na customer_id z tabeli customers.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- Unikalny identyfikator zamówienia
    order_date DATE NOT NULL,              -- Data zamówienia
    customer_id INT REFERENCES customers(customer_id) -- Klucz obcy
);

Praktyczne zastosowanie

Wstawianie danych

Teraz wrzucimy trochę danych do naszych tabel, żeby sprawdzić jak działa relacja.

Dodajmy klientów do tabeli customers:

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

Wynik:

Dodajmy zamówienia do tabeli orders:

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Zamówienie Ady
    ('2023-10-02', 2),  -- Zamówienie Grace
    ('2023-10-03', 1);  -- Jeszcze jedno zamówienie Ady

Tabela orders:

Pamiętaj: przy dodawaniu zamówienia zawsze podawaj istniejący customer_id. Jak spróbujesz podać nieistniejące ID, baza danych wywali błąd. To ochrona spójności danych.

Sprawdzanie relacji

Teraz zobaczmy, jak nasze tabele są połączone. Na przykład: jakie zamówienia zrobiła Ada Lovelace?

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

Wynik:

Tu użyliśmy komendy JOIN, żeby połączyć dwie tabele na podstawie klucza obcego. Wygodnie, czytelnie — i żadnych duplikatów danych!

Po co to wszystko

Relacja ONE-TO-MANY jest mega popularna i przydatna w realu. Wyobraź sobie sklep internetowy, gdzie są tysiące klientów i miliony zamówień. Zamiast powielać info o kliencie w każdym zamówieniu, trzymamy dane o unikalnych klientach w jednej tabeli, a zamówienia — w drugiej. To zmniejsza ilość danych i robi porządek w bazie.

Poza tym możliwość łączenia danych pozwala robić mocne zapytania do analizy. Na przykład, można zapytać: "Ile zamówień zrobił każdy klient?" albo "Którzy klienci zrobili zamówienia w ostatnim miesiącu?".

Trudności i pułapki

Na tym najczęściej początkujący się wykładają:

Brak klucza obcego. Jeśli zapomnisz dodać klucz obcy w tabeli "wiele", to relacja będzie tylko w twojej głowie, ale baza danych nie ogarnie jej w praktyce. To znaczy, że możesz mieć "popsutą" bazę, gdzie zamówienia wskazują na nieistniejących klientów.

Próba usunięcia rekordu z tabeli "jeden". Na przykład, jeśli usuniesz klienta z customers, to jego zamówienia w orders zostaną "wiszące". Żeby tego uniknąć, możesz użyć ON DELETE CASCADE, żeby razem z klientem automatycznie usuwały się jego zamówienia.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

Teraz, jeśli usuniesz klienta:

DELETE FROM customers WHERE customer_id = 1;

Wszystkie jego zamówienia też znikną. Baza będzie czysta jak świeża kawa.

Błędy przy wstawianiu danych. Jeśli spróbujesz wstawić zamówienie z nieistniejącym customer_id, dostaniesz błąd typu:

ERROR:  insert or update on table "orders" violates foreign key constraint