Modellare la relazione ONE-TO-MANY tra tabelle

Ora è il momento di migliorare le nostre skill e creare una delle relazioni più frequenti nei database relazionali — la relazione ONE-TO-MANY.

Immagina una piccola azienda. Un dipendente può lavorare solo in un reparto, ma in un reparto possono lavorare decine di dipendenti. Abbiamo due oggetti del mondo reale: dipendenti e reparti. La relazione tra loro si può descrivere come "un reparto può includere molti dipendenti", oppure formalmente "uno a molti" (ONE-TO-MANY).

Allo stesso modo, le relazioni ONE-TO-MANY si trovano ovunque:

• un cliente può fare molti ordini;
• un autore può scrivere molti libri;
• un insegnante può insegnare a diversi studenti.

In un database relazionale la relazione ONE-TO-MANY si realizza usando una foreign key (FOREIGN KEY). Una delle colonne della tabella "molti" (MANY) punta alla primary key della tabella "uno" (ONE).

Come creare una relazione ONE-TO-MANY

Vediamo un esempio classico: la relazione tra clienti e ordini. Un cliente può fare molti ordini, ma ogni ordine è collegato solo a un cliente. Creeremo due tabelle: customers (clienti) e orders (ordini).

Tabella customers

Questa è la nostra tabella "uno". Qui salviamo le info sui clienti.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Identificatore unico del cliente
    name TEXT NOT NULL              -- Nome del cliente
);

Tabella orders

Questa è la tabella "molti". Qui salviamo gli ordini, dove ogni ordine ha una foreign key customer_id che punta al customer_id della tabella customers.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- Identificatore unico dell'ordine
    order_date DATE NOT NULL,              -- Data dell'ordine
    customer_id INT REFERENCES customers(customer_id) -- Foreign key
);

Applicazione pratica

Inserimento dati

Ora aggiungiamo qualche dato alle nostre tabelle per vedere se la relazione funziona.

Aggiungiamo clienti nella tabella customers:

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

Risultato:

Aggiungiamo ordini nella tabella orders:

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Ordine di Ada
    ('2023-10-02', 2),  -- Ordine di Grace
    ('2023-10-03', 1);  -- Un altro ordine di Ada

Tabella orders:

Nota bene: quando aggiungi un ordine devi sempre specificare un customer_id esistente. Se provi a mettere un ID che non esiste, il database ti darà errore. È una protezione per l'integrità dei dati.

Verifica della relazione

Ora vediamo come sono collegate le nostre tabelle. Per esempio, chiediamo: quali ordini ha fatto 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';

Risultato:

Qui abbiamo usato il comando JOIN per unire le due tabelle sulla base della foreign key. Comodo, pulito — e niente dati duplicati!

Perché serve

La relazione ONE-TO-MANY è super diffusa e utilissima nella vita reale. Immagina un e-commerce con migliaia di clienti e milioni di ordini. Invece di duplicare le info del cliente in ogni ordine, salviamo i clienti unici in una tabella e gli ordini in un'altra. Così risparmiamo spazio e il database è più ordinato.

In più, collegare i dati ci permette di fare query potenti per l'analisi. Per esempio, puoi chiedere: "Quanti ordini ha fatto ogni cliente?" oppure "Quali clienti hanno fatto ordini nell'ultimo mese?".

Difficoltà e trappole

Ecco dove di solito i principianti "inciampano":

Mancanza della foreign key. Se ti dimentichi di mettere la foreign key nella tabella "molti", la relazione esisterà solo nella tua testa, ma il database non potrà applicarla davvero. Così rischi di avere un database "rotto", dove ci sono ordini collegati a clienti che non esistono.

Tentativo di cancellare una riga dalla tabella "uno". Per esempio, se cancelli un cliente da customers, i suoi ordini in orders rimarranno "orfani". Per evitarlo, puoi usare ON DELETE CASCADE, così quando cancelli un cliente si cancellano anche i suoi ordini.

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

Ora, se cancelli un cliente:

DELETE FROM customers WHERE customer_id = 1;

Tutti i suoi ordini verranno cancellati. Il database sarà pulito come una tazza di caffè appena fatta.

Errori nell'inserimento dati. Se provi a inserire un ordine con un customer_id che non esiste, riceverai un errore tipo:

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