Disegnare un database
Dal foglio delle idee alle tabelle
- 15 minuti di lettura
Ernesto Pozzi, il capo deposito, ha un quaderno a righe. Ci scrive quando un treno entra in officina, chi ci mette le mani, quante ore ci vogliono. Il quaderno è pieno, e a Ernesto servono risposte che il quaderno non dà: «quali treni non vedono un meccanico da un mese?». Vuole un database.
Fino a qui hai letto e scritto in tabelle che c'erano già. Questo capitolo è il passo prima: decidere quali tabelle servono, e come si collegano. È la parte in cui si sbaglia di più, perché un errore di disegno non dà nessun errore: il database accetta tutto, e le risposte sono sbagliate per sempre.
Non c'è nessuna parola nuova di SQL. Ti servono i capitoli Tabelle, tipi e psql, Le relazioni e Scrivere: qui li metti insieme.
Il foglio delle idee
Prima delle tabelle, un foglio. Chi progetta un programma comincia da un canvas: una pagina divisa in riquadri, uno per domanda («chi lo usa?», «cosa fa?»…). Per il database ne bastano due:
- I dati: le cose di cui il programma si ricorda, e cosa si sa di ognuna.
- Le azioni: cosa fa il programma con quelle cose.
Il foglio di Ernesto:
I DATI LE AZIONI
───────────────────────────────────── ─────────────────────────────────────
le revisioni dei treni registrare una revisione
di ognuna: quale treno, quando, segnare chi ci ha lavorato
com'è andata elencare i treni non revisionati
i meccanici da più di trenta giorni
di ognuno: nome, cognome
chi ha lavorato a una revisione
e per quante oreLe azioni servono a controllare i dati: se un'azione ha bisogno di qualcosa che non è scritto nei dati, manca una tabella o una colonna. «Segnare chi ci ha lavorato» ha bisogno di sapere chi: sta nei dati? Sì, ultima riga.
Dal foglio alle tabelle, in cinque mosse
1. Ogni «cosa» è una tabella. Sottolinea i nomi nel riquadro dei dati: ognuno è una tabella, al plurale (revisioni, non revisione). Quello che hai già nel database non lo rifai: i treni sono in treni, i meccanici sono persone del personale. Le cose nuove sono le revisioni e «chi ha lavorato a una revisione».
2. Ogni tabella ha il suo id. Una colonna id SERIAL PRIMARY KEY, che si dà da solo e non cambia mai. Vale per quasi tutte; l'eccezione, la tabella ponte, la vedi tra poco.
3. Cosa si sa di una cosa è una colonna. «Quando» è una data, «com'è andata» è un testo. Ogni colonna ha un tipo (DATE, TEXT, NUMERIC(5,2)…).
4. Dove si incontrano due cose, c'è una freccia. Una revisione riguarda un treno; un treno ha molte revisioni. La chiave esterna sta dalla parte del molti: revisioni.treno_id. Lo sai dal capitolo Le relazioni: una casella non può contenere tante revisioni, quindi la freccia parte dalla revisione.
5. Se da tutte e due le parti ci sono «molti», serve una tabella ponte. A una revisione lavorano più meccanici, e un meccanico lavora a più revisioni. Nessuna delle due tabelle può portare la freccia: la porta una terza tabella, interventi, con una riga per ogni coppia revisione-persona.
Il disegno finale:
treni revisioni interventi personale
┌────┬────────┐ ┌────┬──────────┬──────┐ ┌──────────────┬──────────────┐ ┌────┬────────┐
│ id │ codice │◀───────│ id │ treno_id │ … │◀─────│ revisione_id │ personale_id │─▶│ id │ nome │
└────┴────────┘ └────┴──────────┴──────┘ │ ore │ │ └────┴────────┘
uno molti └──────────────┴──────────────┘ uno
(una revisione ha un treno) (la chiave primaria è la coppia)E il CREATE TABLE, che conosci dal capitolo Scrivere:
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'))
);
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)
);Quando serve una tabella ponte con un id suo, e quando no? Se la coppia può ripetersi (lo stesso libro prestato alla stessa persona in due giorni diversi) ci vuole un id e la coppia non è unica. Se la coppia non può ripetersi (una persona lavora a una revisione una volta sola, e le ore si sommano) la chiave primaria è la coppia, e PostgreSQL rifiuta il doppione da solo.
Niente liste dentro una colonna
La tentazione più grande, quando le cose sono «molte»: scriverle tutte in una casella. Guarda cosa succede alla patente dei macchinisti:
CREATE TABLE macchinisti_brutti (nome TEXT, patenti TEXT);
INSERT INTO macchinisti_brutti VALUES
('Sergio', 'metro, tram'), ('Tommaso', 'metro, tram'), ('Rita', 'metro'), ('Paolo', 'tram');
SELECT COUNT(*) AS guidano_il_tram FROM macchinisti_brutti WHERE patenti = 'tram'; guidano_il_tram
-----------------
1
(1 row)Sbagliato: guidano il tram Sergio, Tommaso e Paolo. Il database ha confrontato l'intero testo 'metro, tram' con 'tram', e non sono uguali. Con LIKE '%tram%' si arriva a tre, ma è un colpo di fortuna: basta una patente che si chiami «tram-treno» e il conteggio è di nuovo sbagliato. E «da quando ha la patente?» non ha nemmeno un posto dove stare.
La regola: una casella contiene un valore solo. Se ne servono due, è un'altra tabella. È già così in personale_patenti: una riga per ogni coppia persona-patente, e la data presa_il che sta nella coppia.
Niente dati copiati in due posti
Seconda tentazione: scrivere il nome della stazione in ogni corsa, invece del numero. Sembra comodo, finché qualcuno sbaglia a battere:
CREATE TABLE corse_brutte (id SERIAL PRIMARY KEY, treno TEXT, da_stazione TEXT);
INSERT INTO corse_brutte (treno, da_stazione) VALUES
('M12', 'Porta Codice'), ('M12', 'Porta Codice'), ('T28', 'Porta Codce'),
('M12', 'Piazza Query'), ('T28', 'Piazza Query');
SELECT da_stazione, COUNT(*) AS partenze FROM corse_brutte GROUP BY da_stazione ORDER BY da_stazione; da_stazione | partenze
--------------+----------
Piazza Query | 2
Porta Codce | 1
Porta Codice | 2
(3 rows)Porta Codice è finita in due righe, e i conteggi sono spezzati a metà. Con un numero (da_stazione_id) il nome sta in un posto solo, e si cambia in un posto solo. Per questo revisioni non ripete il nome del meccanico: punta a personale.
L'eccezione: la fotografia. Ci sono dati che devono essere copiati, perché la copia è un fatto storico. Alla biglietteria il biglietto ordinario costava 2,20 fino al 31 ottobre, e 2,50 da novembre. Chi l'ha comprato a ottobre ha pagato 2,20, e per sempre. In linea_menta.sql la tabella vendite non punta soltanto a tariffe (tariffa_id): ricopia il prezzo pagato in prezzo_pagato.
SELECT t.nome, v.data, v.prezzo_pagato, t.prezzo AS listino_oggi
FROM vendite v
JOIN tariffe t ON t.id = v.tariffa_id
WHERE t.nome = 'ordinario'
ORDER BY v.data, v.id
LIMIT 6; nome | data | prezzo_pagato | listino_oggi
-----------+------------+---------------+--------------
ordinario | 2026-10-26 | 2.20 | 2.50
ordinario | 2026-10-26 | 2.20 | 2.50
ordinario | 2026-10-29 | 2.20 | 2.50
ordinario | 2026-10-30 | 2.20 | 2.50
ordinario | 2026-11-02 | 2.50 | 2.50
ordinario | 2026-11-02 | 2.50 | 2.50
(6 rows)Le prime quattro righe hanno il prezzo di ottobre. Quanto ha incassato la linea? Con la fotografia, la somma giusta; con il listino di oggi, una somma inventata:
SELECT SUM(v.prezzo_pagato) AS incasso_vero, SUM(t.prezzo) AS incasso_con_il_listino_di_oggi
FROM vendite v
JOIN tariffe t ON t.id = v.tariffa_id; incasso_vero | incasso_con_il_listino_di_oggi
--------------+--------------------------------
86.20 | 88.20
(1 row)Due euro di differenza: i sei biglietti venduti prima del rincaro. Se vendite avesse solo tariffa_id, la differenza sarebbe impossibile da recuperare: il vecchio prezzo non c'è più da nessuna parte.
I nomi
Le stesse regole di tutte le tabelle della Linea Menta. Così chi legge la tua query (o chi, un giorno, scriverà le rotte della tua API) capisce senza chiedere:
| Cosa | Regola | Esempio |
|---|---|---|
| Tabelle | minuscolo, plurale, parole con _ | oggetti_smarriti, righe_ordine |
| Colonne | minuscolo, singolare, con _ | ultima_revisione |
| Chiave primaria | id | treni.id |
| Chiave esterna | <cosa>_id, al singolare | treno_id, personale_id |
| Due frecce verso la stessa tabella | il ruolo davanti | da_stazione_id, a_stazione_id |
| Date e ore | _il per le date, _alle per le ore | fatta_il, preso_alle |
| Unità di misura | nel nome | ritardo_min, ore |
| Niente accenti, spazi, maiuscole | quantita, non Quantità |
Perché le minuscole: PostgreSQL mette tutto in minuscolo, a meno che non usi le virgolette doppie. Se scrivi CREATE TABLE Revisioni la tabella si chiama revisioni, e se un giorno qualcuno scrive "Revisioni" con le virgolette, è un'altra tabella ancora. Non cominciare.
Un vincolo per ogni dato che può essere sbagliato
Il database è l'ultimo che vede un dato prima che entri. Per ogni colonna, chiediti: cosa non deve mai esserci qui? E scrivi il vincolo che lo impedisce.
| Il dato | Può essere sbagliato se… | Il vincolo |
|---|---|---|
fatta_il | è vuoto | NOT NULL |
esito | qualcuno scrive «forse» | CHECK (esito IN ('ok', 'da rifare')) |
ore | è zero o negativo | CHECK (ore > 0) |
treno_id | punta a un treno che non c'è | REFERENCES treni(id) |
personale_id + revisione_id | la stessa persona è segnata due volte | PRIMARY KEY (revisione_id, personale_id) |
codice di un treno | ce ne sono due uguali | UNIQUE |
quantita se non scritta | resta vuota | DEFAULT 1 |
Ogni riga della tabella è un errore che non può più succedere. Prova su revisioni:
INSERT INTO revisioni (treno_id, fatta_il, esito) VALUES (99, '2026-11-10', 'ok');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".Lo schema in un file, che si rilancia
Il disegno finale non si scrive nel prompt di psql: si scrive in un file, schema.sql, che fa parte del progetto e finisce su Git insieme al codice. Chi arriva dopo di te lo carica con psql -f e ha le tue tabelle.
Il file deve poter girare più volte, perché lo cambierai mentre lavori. Per questo comincia cancellando le tabelle, nell'ordine giusto: prima le figlie, poi le madri. Non puoi cancellare revisioni finché interventi la punta.
DROP TABLE IF EXISTS interventi, revisioni;
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'))
);
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)
);Nota che non cancella treni e personale: non sono sue. Si cancellano solo le tabelle che il file crea.
La prima volta PostgreSQL scrive due NOTICE, perché le tabelle da cancellare non c'erano. Non è un errore:
NOTICE: table "interventi" does not exist, skipping
NOTICE: table "revisioni" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLEAlla seconda e alle successive, solo i quattro comandi eseguiti. Quando le tabelle sono scritte, il passo dopo è farle parlare con un programma: le rotte dell'API, il contratto, e le query dentro Python con psycopg.
Le dieci domande per rileggere
Quando hai finito, passa lo schema a un compagno (e prendi il suo). Dieci domande, tutte con una risposta sì o no:
- Ogni «cosa» del foglio dei dati ha la sua tabella, e ogni tabella è una cosa sola?
- Ogni tabella ha una chiave primaria? (Le tabelle ponte la possono avere sulla coppia.)
- Ogni legame «uno a molti» ha la sua chiave esterna, dalla parte del molti?
- Ogni legame «molti a molti» passa da una tabella ponte?
- C'è una colonna con una lista dentro (
'rock, jazz','metro, tram')? - C'è un dato scritto in due posti, che un giorno potrebbe non coincidere? Se sì, è una fotografia o una copia da togliere?
- Ogni azione del foglio si può fare con queste tabelle? Prova a scrivere a mente la query.
- Per ogni colonna: il tipo è quello giusto (soldi in
NUMERIC, date inDATE), e c'è il vincolo per ciò che non deve entrare? - I nomi seguono la tabella qui sopra: plurale, minuscolo,
<cosa>_id, l'unità nel nome? - Il file si può rilanciare da capo, e le tabelle si cancellano dalla più figlia alla più madre?
Ricorda
- Si parte dal foglio: i dati (le cose e cosa si sa di ognuna) e le azioni. Un'azione che non trova i suoi dati vuol dire che manca una tabella o una colonna.
- Ogni «cosa» è una tabella, al plurale, con il suo
id. Cosa si sa di una cosa è una colonna, con un tipo e un vincolo. - Un legame uno a molti: la chiave esterna sta dalla parte del molti. Un legame molti a molti: una tabella ponte, con la chiave primaria sulla coppia (o con un
idsuo, se la coppia può ripetersi). - Una casella contiene un valore solo: le liste sono un'altra tabella.
- Un dato scritto in due posti prima o poi non coincide. L'unica copia voluta è la fotografia: il prezzo pagato (
vendite.prezzo_pagato) non segue il listino di oggi. - Per ogni dato che può essere sbagliato, un vincolo:
NOT NULL,CHECK,UNIQUE,REFERENCES,DEFAULT. - Lo schema sta in un file che si rilancia:
DROP TABLE IF EXISTSdalla più figlia alla più madre, poi iCREATE TABLE.