Modellazione delle relazioni tra tabelle per la normalizzazione

Oggi ci addentriamo in un argomento super importante: la modellazione delle relazioni tra tabelle. La normalizzazione non riguarda solo i dati atomici e l'eliminazione delle ridondanze, ma anche la creazione delle giuste relazioni tra le tabelle.

Se i database sono un sistema organizzato per conservare informazioni, allora le relazioni tra tabelle sono i ponti logici che mostrano come i dati interagiscono tra loro. Immagina una biblioteca dove le info sui libri sono separate da quelle sugli autori, ma ogni libro "conosce" il suo autore tramite una relazione speciale. Oppure pensa a un e-commerce: i dati sui prodotti esistono indipendentemente da quelli sui clienti, ma quando qualcuno fa un ordine, il sistema collega un cliente specifico a prodotti specifici tramite la tabella degli ordini.

In una clinica medica i pazienti sono collegati alle loro cartelle cliniche, i medici agli orari delle visite, e i farmaci alle prescrizioni. Queste relazioni aiutano il sistema a capire cosa è collegato a cosa, senza duplicare dati inutilmente.

I tipi principali di queste relazioni funzionano come nella vita reale: il passaporto appartiene solo a una persona (uno-a-uno), un insegnante può tenere più corsi (uno-a-molti), e gli studenti possono iscriversi a diverse materie, mentre ogni materia è frequentata da diversi studenti (molti-a-molti).

Uno-a-uno (1:1)

Questa è una relazione dove un record nella tabella "A" corrisponde esattamente a un record nella tabella "B". Per esempio, abbiamo le tabelle "Dipendenti" e "Dati passaporto". Un dipendente può avere solo un passaporto, e ogni passaporto appartiene a un solo dipendente.

Esempio:

Dipendenti

Dati passaporto

Qui la relazione si realizza tramite la foreign key dipendente_id, che punta all'id nella tabella "Dipendenti".

Uno-a-molti (1:N)

Questo è il tipo di relazione più comune. Qui ogni record della tabella "A" può essere collegato a più record nella tabella "B", ma ogni record della tabella "B" è collegato solo a un record della tabella "A". Per esempio, abbiamo le tabelle "Docenti" e "Corsi". Un docente può tenere più corsi.

Esempio:

Docenti

Corsi

La relazione viene creata tramite la foreign key docente_id nella tabella "Corsi".

Molti-a-molti (M:N)

Quando hai un sacco di tutto, è divertente ma complicato. Qui ogni record nella tabella "A" può essere collegato a più record nella tabella "B", e viceversa. Per esempio, gli studenti possono iscriversi a più corsi, e ogni corso può essere frequentato da più studenti.

Esempio:

Studenti

Corsi

Per collegare tutto serve una tabella intermedia che tiene traccia delle associazioni tra studenti e corsi:

Iscrizioni

Modellare le relazioni con le foreign key

Una foreign key è una colonna (o un gruppo di colonne) che punta a una colonna chiave primaria in un'altra tabella. È la base per costruire le relazioni tra tabelle.

Esempio di foreign key:

CREATE TABLE Corsi (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255)
);

CREATE TABLE Iscrizioni (
    id SERIAL PRIMARY KEY,
    studente_id INT,
    corso_id INT,
    FOREIGN KEY (corso_id) REFERENCES Corsi(id)
);

Come evitare errori quando progetti le foreign key? Prima di tutto, controlla che i tipi di dato tra le colonne della foreign e della primary key coincidano — altrimenti il database non ti farà creare la relazione. E poi pensa in anticipo a cosa deve succedere quando cancelli dei record. Per esempio, se cancelli una riga dalla tabella padre, che succede alle righe figlie? Una delle opzioni più usate è ON DELETE CASCADE, così i dati collegati vengono cancellati automaticamente insieme al record principale. Questo aiuta a mantenere ordine e a evitare "link orfani".

Implementazione della relazione "Molti-a-molti"

Prendiamo un esempio: abbiamo studenti e corsi. Uno studente può essere iscritto a più corsi, e un corso può avere più studenti iscritti. Per realizzare la relazione M:N creiamo tre tabelle: Studenti, Corsi e Iscrizioni.

CREATE TABLE Studenti (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255)
);

CREATE TABLE Corsi (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255)
);

CREATE TABLE Iscrizioni (
    id SERIAL PRIMARY KEY,
    studente_id INT,
    corso_id INT,
    FOREIGN KEY (studente_id) REFERENCES Studenti(id),
    FOREIGN KEY (corso_id) REFERENCES Corsi(id)
);

Ora possiamo aggiungere record nella tabella Iscrizioni per collegare studenti e corsi.

Esercizio pratico

Crea la struttura di un database per un sistema di gestione corsi. Devi avere le tabelle Studenti, Corsi e Iscrizioni. Implementa tutte le relazioni tra le tabelle. Poi inserisci dati di esempio su studenti, corsi e le loro iscrizioni. Vediamo come si fa.

1. Creiamo le tabelle:

CREATE TABLE Studenti (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255)
);

CREATE TABLE Corsi (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(255)
);

CREATE TABLE Iscrizioni (
    id SERIAL PRIMARY KEY,
    studente_id INT,
    corso_id INT,
    FOREIGN KEY (studente_id) REFERENCES Studenti(id),
    FOREIGN KEY (corso_id) REFERENCES Corsi(id)
);

2. Inseriamo i dati:

INSERT INTO Studenti (nome) VALUES ('Otto Lin'), ('Maria Chi');
INSERT INTO Corsi (nome) VALUES ('Basi di SQL'), ('Amministrazione DB');
INSERT INTO Iscrizioni (studente_id, corso_id) VALUES (1, 1), (1, 2), (2, 1);

3. Controlliamo i dati:

SELECT
    Studenti.nome AS studente, 
    Corsi.nome AS corso
FROM Iscrizioni
JOIN Studenti ON Iscrizioni.studente_id = Studenti.id
JOIN Corsi ON Iscrizioni.corso_id = Corsi.id;

Risultato:

Difficoltà e particolarità nella modellazione delle relazioni

Quando modelli le relazioni tra tabelle, possono sorgere problemi come:

• Errori quando cancelli dati (ad esempio, hai record nella tabella figlia che dipendono da un record nella tabella padre).
• Performance delle query con tanti dati. Le relazioni M:N sono particolarmente "affamate", perché richiedono join aggiuntivi.

Per risolvere questi problemi aiutano:

• Usare indici sulle foreign key
• Struttura del database pensata bene.
• Bilanciare tra normalizzazione e performance.

Abbiamo visto la modellazione delle relazioni tra tabelle a un livello molto base e l'abbiamo applicata in pratica creando la struttura di un sistema di gestione corsi. Ovviamente sarebbe bello vedere un esempio grande, ma non mi è venuto in mente come farlo. Un esempio grande diventa complicato e noioso. E non serve a molto. Proverò a tornare su questo argomento verso la fine del corso.