Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

SQL7. Disegnare un databasep. 158

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 ore

Le 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:

SQL
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:

SQL
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:

SQL
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.

SQL
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:

SQL
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:

CosaRegolaEsempio
Tabelleminuscolo, plurale, parole con _oggetti_smarriti, righe_ordine
Colonneminuscolo, singolare, con _ultima_revisione
Chiave primariaidtreni.id
Chiave esterna<cosa>_id, al singolaretreno_id, personale_id
Due frecce verso la stessa tabellail ruolo davantida_stazione_id, a_stazione_id
Date e ore_il per le date, _alle per le orefatta_il, preso_alle
Unità di misuranel nomeritardo_min, ore
Niente accenti, spazi, maiuscolequantita, 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 datoPuò essere sbagliato se…Il vincolo
fatta_ilè vuotoNOT NULL
esitoqualcuno scrive «forse»CHECK (esito IN ('ok', 'da rifare'))
oreè zero o negativoCHECK (ore > 0)
treno_idpunta a un treno che non c'èREFERENCES treni(id)
personale_id + revisione_idla stessa persona è segnata due voltePRIMARY KEY (revisione_id, personale_id)
codice di un trenoce ne sono due ugualiUNIQUE
quantita se non scrittaresta vuotaDEFAULT 1

Ogni riga della tabella è un errore che non può più succedere. Prova su revisioni:

SQL
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.

SQL
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 TABLE

Alla 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:

  1. Ogni «cosa» del foglio dei dati ha la sua tabella, e ogni tabella è una cosa sola?
  2. Ogni tabella ha una chiave primaria? (Le tabelle ponte la possono avere sulla coppia.)
  3. Ogni legame «uno a molti» ha la sua chiave esterna, dalla parte del molti?
  4. Ogni legame «molti a molti» passa da una tabella ponte?
  5. C'è una colonna con una lista dentro ('rock, jazz', 'metro, tram')?
  6. C'è un dato scritto in due posti, che un giorno potrebbe non coincidere? Se sì, è una fotografia o una copia da togliere?
  7. Ogni azione del foglio si può fare con queste tabelle? Prova a scrivere a mente la query.
  8. Per ogni colonna: il tipo è quello giusto (soldi in NUMERIC, date in DATE), e c'è il vincolo per ciò che non deve entrare?
  9. I nomi seguono la tabella qui sopra: plurale, minuscolo, <cosa>_id, l'unità nel nome?
  10. 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 id suo, 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 EXISTS dalla più figlia alla più madre, poi i CREATE TABLE.