Disegnare un database, esercizi spiegati
Cinque esempi, dall'officina alle tabelle
- 25 minuti di lettura
Ernesto Pozzi vuole portare il quaderno dell'officina nel database, e ti chiede di disegnarlo. Cinque esempi, uno per ogni idea della teoria. Ognuno ha lo stesso ritmo:
- Prevedi: leggi la richiesta e il codice, e scrivi su un foglio cosa risponde PostgreSQL. Prima di aprire la risposta.
- In italiano: cosa fa il codice, a parole.
- Passo per passo: le decisioni di disegno, una alla volta.
- Cosa portiamo a casa: la cosa da ricordare.
Per provarli: createdb sql_disegnare_un_database, poi psql -d sql_disegnare_un_database -f linea_menta.sql (il file è nello zip della palestra), poi psql -d sql_disegnare_un_database e incolli.
A · Dal foglio alle tabelle
Il foglio di Ernesto, già visto: le revisioni (quale treno, quando, com'è andata), chi ha lavorato a una revisione e per quante ore. I treni e il personale ci sono già in linea_menta.sql.
Prima di scrivere: quante tabelle nuove servono? E quale colonna di revisioni porta la freccia verso treni?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
Due tabelle nuove:
revisioni: una per ogni revisione. Porta la freccia verso i treni, contreno_id.interventi: una per ogni coppia revisione-persona (la tabella ponte).
I treni e il personale non si rifanno: esistono. Dopo i CREATE TABLE, \d revisioni mostra:
Table "public.revisioni"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('revisioni_id_seq'::regclass)
treno_id | integer | | not null |
fatta_il | date | | not null |
esito | text | | not null |
Indexes:
"revisioni_pkey" PRIMARY KEY, btree (id)
Check constraints:
"revisioni_esito_check" CHECK (esito = ANY (ARRAY['ok'::text, 'da rifare'::text]))
Foreign-key constraints:
"revisioni_treno_id_fkey" FOREIGN KEY (treno_id) REFERENCES treni(id)
Referenced by:
TABLE "interventi" CONSTRAINT "interventi_revisione_id_fkey" FOREIGN KEY (revisione_id) REFERENCES revisioni(id)In italiano. «Una revisione ha un id, un treno (che deve esistere), una data e un esito. L'esito può essere solo ok o da rifare.»
CREATE TABLE revisioni (
id SERIAL PRIMARY KEY,
treno_id INTEGER NOT NULL REFERENCES treni(id),
fatta_il DATE NOT NULL,
esito TEXT NOT NULL CHECK (esito IN ('ok', 'da rifare'))
);Passo per passo.
| Domanda | Decisione |
|---|---|
| Quali sono le cose? | Le revisioni sono nuove, treni e persone no |
| Come si chiama la tabella? | revisioni: minuscolo, plurale |
| Cosa si sa di una revisione? | Quando: fatta_il DATE. Com'è andata: esito TEXT |
| Revisione e treno: chi è «molti»? | Molte revisioni per treno: la freccia parte dalla revisione, treno_id |
| Cosa non deve entrare? | Una data vuota (NOT NULL), un esito come «forse» (CHECK), un treno che non c'è (REFERENCES) |
Guarda anche la riga Referenced by: PostgreSQL ti dice già che interventi punta a revisioni. Lo vedi dopo il secondo CREATE TABLE.
Cosa portiamo a casa. Ogni cosa nuova è una tabella; le cose che ci sono già non si rifanno, si puntano.
B · La freccia dalla parte del molti
Ernesto registra una revisione del treno 2 (il M12). Poi prova, per sbaglio, a registrarne una per il treno 99, una con esito «forse», e a cancellare il treno T31, che ha una revisione.
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (2, '2026-11-10', 'ok');
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (99, '2026-11-10', 'ok');
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (8, '2026-11-12', 'forse');
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (8, '2026-11-12', 'da rifare');
DELETE FROM treni WHERE codice = 'T31';Quali dei cinque comandi funzionano? E per gli altri, cosa dice PostgreSQL?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
INSERT 0 1
ERROR: insert or update on table "revisioni" violates foreign key constraint "revisioni_treno_id_fkey"
DETAIL: Key (treno_id)=(99) is not present in table "treni".
ERROR: new row for relation "revisioni" violates check constraint "revisioni_esito_check"
DETAIL: Failing row contains (3, 8, 2026-11-12, forse).
INSERT 0 1
ERROR: update or delete on table "treni" violates foreign key constraint "revisioni_treno_id_fkey" on table "revisioni"
DETAIL: Key (id)=(8) is still referenced from table "revisioni".- Funzionano il primo e il quarto.
- Il secondo: il treno 99 non esiste, la freccia non ha dove arrivare.
- Il terzo: «forse» non è nel
CHECK. L'id della riga rifiutata è 3, non 2: ilSERIALconta anche i tentativi falliti. Per questo gli id non si prevedono mai. - L'ultimo: il treno T31 (id 8) ha una revisione che lo punta, e non si può cancellare.
In italiano. Le regole del disegno sono sempre al lavoro. Il database non lascia entrare una revisione orfana (senza treno) e non lascia sparire un treno a cui ne punta una.
Passo per passo. Perché la freccia è in revisioni e non in treni?
| Provo | Cosa succede |
|---|---|
Metto la colonna revisione_id in treni | Un treno avrebbe una revisione sola: alla seconda dovrei cancellare la prima |
Metto la colonna treno_id in revisioni | Ogni revisione ha un treno; un treno ha tutte le revisioni che vuole |
La domanda per trovare il verso: «quanti ne ha, dall'altra parte?». Un treno ha molte revisioni, una revisione ha un treno solo: il numero va nella riga che ne ha uno, cioè nella revisione. In altre parole: la chiave esterna sta dove c'è il «molti».
Cosa portiamo a casa. La chiave esterna sta dalla parte del molti, e il REFERENCES fa rispettare il disegno ogni volta che si scrive.
C · La tabella ponte
A una revisione lavorano più meccanici, e un meccanico lavora a più revisioni. Ernesto vuole segnare, per ognuno, le ore. Dario Sala (id 8) e Ivo Brambati (id 2) hanno lavorato alla revisione 1.
CREATE TABLE interventi (
revisione_id INTEGER NOT NULL REFERENCES revisioni(id),
personale_id INTEGER NOT NULL REFERENCES personale(id),
ore NUMERIC(4,1) NOT NULL CHECK (ore > 0),
PRIMARY KEY (revisione_id, personale_id)
);
INSERT INTO interventi (revisione_id, personale_id, ore) VALUES (1, 2, 3.5), (1, 8, 2.0);
INSERT INTO interventi (revisione_id, personale_id, ore) VALUES (1, 2, 1.0);Ivo viene segnato due volte alla stessa revisione. Cosa risponde il database all'ultimo comando?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
INSERT 0 2
ERROR: duplicate key value violates unique constraint "interventi_pkey"
DETAIL: Key (revisione_id, personale_id)=(1, 2) already exists.Il primo comando mette due righe. Il secondo è rifiutato: la coppia (revisione 1, persona 2) c'è già.
In italiano. Una riga di interventi dice: «questa persona ha lavorato a questa revisione per tante ore». La coppia identifica la riga, quindi la chiave primaria è la coppia.
Ora una query che usa il disegno: chi ha lavorato a quale treno?
SELECT t.codice, r.fatta_il, p.nome, p.cognome, i.ore
FROM revisioni r
JOIN treni t ON t.id = r.treno_id
JOIN interventi i ON i.revisione_id = r.id
JOIN personale p ON p.id = i.personale_id
ORDER BY p.cognome; codice | fatta_il | nome | cognome | ore
--------+------------+-------+----------+-----
M12 | 2026-11-10 | Ivo | Brambati | 3.5
M12 | 2026-11-10 | Dario | Sala | 2.0
(2 rows)Passo per passo.
| Domanda | Decisione |
|---|---|
| Chi è «molti» tra revisione e persona? | Tutte e due |
| Dove va la freccia? | Né in revisioni né in personale: in una terza tabella |
| Cosa ha di suo la tabella? | Le ore: sono della coppia, non della persona né della revisione |
Serve un id? | No: la coppia non può ripetersi, e la sua chiave è già la chiave primaria |
| Come si cerca? | Quattro tabelle, quattro JOIN, ognuna con la sua freccia: dal capitolo JOIN |
Cosa portiamo a casa. Molti a molti: una tabella ponte con due chiavi esterne e la chiave primaria sulla coppia. Il doppione lo blocca PostgreSQL, non il tuo programma.
D · Niente liste dentro una colonna
Un meccanico alle prime armi propone una scorciatoia: «Perché non una colonna meccanici dentro revisioni, con i nomi separati da virgola?»
CREATE TABLE revisioni_brutte (
id SERIAL PRIMARY KEY,
treno_id INTEGER NOT NULL REFERENCES treni(id),
meccanici TEXT
);
INSERT INTO revisioni_brutte (treno_id, meccanici) VALUES (2, 'Brambati, Sala'), (3, 'Sala'), (5, 'Sala, Pozzi');Ernesto chiede: «Quante revisioni ha fatto Sala?» Quante risponde WHERE meccanici = 'Sala'? Quante sono davvero?
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
SELECT COUNT(*) AS revisioni_di_sala FROM revisioni_brutte WHERE meccanici = 'Sala'; revisioni_di_sala
-------------------
1
(1 row)Risponde 1: ha confrontato l'intero testo 'Brambati, Sala' con 'Sala', e solo la seconda riga è uguale. Ma Sala ha lavorato a tre revisioni.
In italiano. Il database non sa che quel testo è «una lista»: per lui è una parola lunga. Con LIKE '%Sala%' si aggiusta questo caso, ma poi servono altre scorciatoie per sapere le ore di ognuno, chi ha lavorato più di tutti, chi non ha lavorato mai.
Passo per passo. La cura è la stessa dell'esempio C.
| Prima | Dopo |
|---|---|
| Una casella con più valori | Una riga di interventi per ogni valore |
meccanici TEXT | personale_id con REFERENCES, e il nome sta in un posto solo |
«Quante revisioni?» con LIKE | SELECT COUNT(*) FROM interventi WHERE personale_id = 8 |
Cosa portiamo a casa. Se una casella ha una virgola dentro, è una tabella che non hai ancora fatto.
E · Il prezzo come fotografia
L'officina compra i ricambi. Ernesto vuole sapere quanto ha speso, e il prezzo dei ricambi cambia ogni mese. Nel database della Linea Menta c'è già lo stesso problema, alla biglietteria: in vendite c'è prezzo_pagato, in tariffe il listino di oggi.
Quanto ha incassato la linea, per tipo di biglietto? Prevedi se, con il listino di oggi, la somma sarebbe uguale.
SELECT t.nome,
COUNT(*) AS vendite,
SUM(v.prezzo_pagato) AS incassato,
SUM(t.prezzo) AS con_listino_oggi
FROM vendite v
JOIN tariffe t ON t.id = v.tariffa_id
GROUP BY t.nome
ORDER BY t.nome;Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.
nome | vendite | incassato | con_listino_oggi
-------------+---------+-----------+------------------
giornaliero | 6 | 47.20 | 48.00
ordinario | 14 | 33.80 | 35.00
ridotto | 4 | 5.20 | 5.20
(3 rows)- Il ridotto non è cambiato: le due colonne coincidono.
- L'ordinario e il giornaliero sì: fino al 31 ottobre costavano 2,20 e 7,60. Con il listino di oggi si «ritoccano» le vendite passate: 0,80 in più sul giornaliero, 1,20 sull'ordinario.
In italiano. «Per ogni biglietto venduto ricopio il prezzo che il cliente ha pagato. Il listino cambia: il biglietto venduto resta com'era.»
Passo per passo.
| Domanda | Risposta |
|---|---|
| Il dato è scritto in due posti? | Sì: tariffe.prezzo e vendite.prezzo_pagato |
| Un giorno potrebbero non coincidere? | Sì, ed è giusto |
| Se cambia l'originale, la copia deve cambiare? | No: è un fatto storico. Quindi è una fotografia, non una copia da togliere |
Per l'officina vale lo stesso: interventi avrebbe una colonna costo_pagato, e il listino dei ricambi cambierebbe quanto vuole.
Cosa portiamo a casa. Copiare un dato è sbagliato quando la copia deve restare uguale all'originale; è giusto quando la copia è la fotografia di un momento. Chiediti: «se l'originale cambia domani, questo dato cambia?»