Un database relazionale si chiama così perché le tabelle sono collegate tra loro. Saper riconoscere che tipo di relazione esiste tra due entità, e tradurla nella struttura giusta, è forse la competenza più importante nella progettazione di uno schema. Un errore in questa fase si porta dietro problemi per tutta la vita dell'applicazione.
Come riconoscere il tipo di relazione
Il metodo più semplice è porsi due domande, una per ciascuna direzione. Per clienti e ordini:
- un cliente può avere più ordini? Sì.
- un ordine può appartenere a più clienti? No.
Una direzione «molti» e una «uno»: è una relazione uno a molti. Per studenti e corsi:
- uno studente può seguire più corsi? Sì.
- un corso può avere più studenti? Sì.
Due direzioni «molti»: è una relazione molti a molti.
Uno a molti
È la relazione più comune. Si rappresenta con una chiave esterna nella tabella del lato «molti», che punta alla chiave primaria del lato «uno»:
CREATE TABLE ordini (
id BIGINT PRIMARY KEY,
id_cliente BIGINT NOT NULL REFERENCES clienti(id),
data_ordine DATE NOT NULL
);
La chiave esterna garantisce che ogni ordine punti a un cliente esistente. Il vincolo NOT NULL aggiunge un'altra regola: non può esistere un ordine senza cliente. Se invece la relazione è facoltativa, per esempio un ticket di assistenza che può non essere ancora assegnato a un tecnico, la colonna resta annullabile.
L'errore tipico è fare il contrario: mettere nella tabella dei clienti colonne come ordine1, ordine2, ordine3. Si ha subito un limite arbitrario e le query diventano complicate.
Molti a molti
Una relazione molti a molti non si può rappresentare con una sola chiave esterna. Serve una terza tabella, detta tabella ponte, di associazione o di collegamento, che contiene le chiavi delle due entità:
CREATE TABLE iscrizioni (
id_studente BIGINT NOT NULL REFERENCES studenti(id),
id_corso BIGINT NOT NULL REFERENCES corsi(id),
PRIMARY KEY (id_studente, id_corso)
);
La chiave primaria composta impedisce di iscrivere due volte lo stesso studente allo stesso corso. Per trovare i corsi di uno studente si uniscono le tre tabelle.
La tabella ponte con attributi
Spesso la relazione stessa ha delle proprietà. L'iscrizione ha una data, un voto finale, uno stato. Queste informazioni non appartengono né allo studente né al corso, ma alla loro combinazione, e vanno nella tabella ponte:
CREATE TABLE iscrizioni (
id_studente BIGINT NOT NULL REFERENCES studenti(id),
id_corso BIGINT NOT NULL REFERENCES corsi(id),
data_iscrizione DATE NOT NULL,
voto SMALLINT CHECK (voto BETWEEN 18 AND 30),
PRIMARY KEY (id_studente, id_corso)
);
A questo punto la tabella ponte è diventata un'entità a pieno titolo. Lo stesso accade con le righe d'ordine, che collegano ordini e prodotti e contengono quantità e prezzo.
Una domanda da porsi: lo stesso studente può iscriversi due volte allo stesso corso, per esempio in anni diversi? Se sì, la chiave composta non basta più, e serve aggiungere l'anno alla chiave o usare una chiave surrogata con un vincolo di unicità appropriato.
Uno a uno
Nella relazione uno a uno ogni riga di una tabella corrisponde al massimo a una riga dell'altra. Si rappresenta con una chiave esterna dichiarata anche UNIQUE, oppure usando la stessa chiave primaria in entrambe le tabelle.
Se due entità sono davvero uno a uno, ci si può chiedere perché non unirle in una tabella sola. Ci sono buone ragioni per tenerle separate:
- dati facoltativi: solo una parte degli utenti ha un profilo aziendale con molti campi specifici;
- dati sensibili: informazioni riservate in una tabella con permessi più ristretti;
- dati pesanti: documenti o testi lunghi che rallenterebbero le letture della tabella principale.
Relazioni con se stessi
Una tabella può avere una chiave esterna che punta a se stessa. È il modo naturale di rappresentare gerarchie:
CREATE TABLE dipendenti (
id BIGINT PRIMARY KEY,
nome TEXT NOT NULL,
id_responsabile BIGINT REFERENCES dipendenti(id)
);
Il direttore ha id_responsabile vuoto; tutti gli altri puntano al proprio superiore. Lo stesso schema vale per categorie e sottocategorie o per commenti e risposte. Per percorrere tutta la gerarchia si usano le query ricorsive, disponibili nella maggior parte dei database con la sintassi WITH RECURSIVE.
Che cosa succede in cancellazione
Quando si cancella un cliente, che cosa deve succedere ai suoi ordini? La chiave esterna permette di dichiararlo:
| Opzione | Effetto | Quando usarla |
|---|---|---|
RESTRICT / NO ACTION |
Impedisce la cancellazione se esistono righe collegate | Dati con valore storico o contabile |
CASCADE |
Cancella anche le righe collegate | Dati che non hanno senso da soli, come le righe di un carrello |
SET NULL |
Svuota la chiave esterna nelle righe collegate | Relazioni facoltative, come il tecnico assegnato |
CASCADE è comodo ma pericoloso: una sola cancellazione può propagarsi a catena su molte tabelle. Per dati con valore storico, come clienti con fatture, conviene impedire la cancellazione e usare piuttosto una colonna che indichi il record come disattivato.
In sintesi
- Il tipo di relazione si riconosce chiedendosi «quanti?» in entrambe le direzioni.
- Uno a molti: chiave esterna nella tabella del lato «molti».
- Molti a molti: tabella ponte, che può avere attributi propri e diventare un'entità.
- Uno a uno: chiave esterna unica, utile per dati facoltativi, sensibili o pesanti.
- Le azioni in cancellazione vanno scelte con cura;
CASCADEsolo per dati dipendenti.
