Modellare la relazione MANY-TO-MANY usando una tabella intermedia

La relazione "molti-a-molti" è quando un record in una tabella può essere collegato a più record in un'altra tabella, e viceversa. Per esempio: - Uno studente (dalla tabella students) può essere iscritto a più corsi (dalla tabella courses). - Un corso può essere frequentato da più studenti.

Il problema è che gestire direttamente questa relazione è scomodo. Ecco perché ci viene in aiuto una tabella intermedia che tiene traccia di questi collegamenti.

Esempio dalla vita reale

Immagina di avere una tabella degli studenti e una dei corsi. Se provi ad aggiungere in ogni tabella una colonna per tutti i dati collegati, viene fuori un casino:

• Nella tabella students dovresti salvare la lista di tutti i corsi a cui è iscritto uno studente. Ma come la salvi? Separati da virgole? In un array? Sarebbe un incubo per le query.
• Nella tabella courses dovresti salvare la lista degli studenti, e qui proprio ti viene il mal di testa.

Quindi la soluzione giusta è creare una terza tabella che tiene le relazioni tra studenti e corsi.

La tabella intermedia: la nostra salvezza!

La tabella intermedia (a volte chiamata tabella di relazione) risolve tutto. Contiene due riferimenti esterni:

• Chiave esterna verso la tabella students.
• Chiave esterna verso la tabella courses.

Ogni record in questa tabella crea un collegamento tra uno studente specifico e un corso specifico.

Creare le tabelle per la relazione "molti-a-molti"

Facciamo un po' di pratica! Ecco come possiamo creare le tabelle per collegare studenti e corsi:

Passo 1: Creare la tabella students

Questa è la nostra tabella degli studenti. Qui salviamo gli identificatori unici degli studenti e i loro nomi.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id — è l'identificatore unico dello studente (auto-incrementato, grazie SERIAL!).
• name — il nome dello studente.

Passo 2: Creare la tabella courses

Ora creiamo la tabella dei corsi. Qui salviamo gli identificatori unici dei corsi e i loro titoli.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id — identificatore unico del corso.
• title — titolo del corso.

Passo 3: Creare la tabella intermedia enrollments

Ora creiamo la nostra magica tabella di collegamento. Contiene due colonne, ognuna è una chiave esterna che punta alle rispettive tabelle.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Vediamo la struttura:

• student_id — chiave esterna che punta a student_id nella tabella students.
• course_id — chiave esterna che punta a course_id nella tabella courses.
• PRIMARY KEY (student_id, course_id) — la chiave primaria è la combinazione delle due chiavi esterne. Così ogni collegamento è unico.

Inserimento dati

Aggiungiamo un po' di dati per vedere come funziona tutto.

Passo 1: Aggiungere studenti

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Risultato:

Passo 2: Aggiungere corsi

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

Risultato:

Passo 3: Aggiungere record in enrollments

Ora iscriviamo gli studenti ai corsi. Per esempio:

• Alice è iscritta a Mathematics e History.
• Bob è iscritto solo a Biology.
• Charlie è iscritto a tutti e tre i corsi.

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice su Mathematics
(1, 2), -- Alice su History
(2, 3), -- Bob su Biology
(3, 1), -- Charlie su Mathematics
(3, 2), -- Charlie su History
(3, 3); -- Charlie su Biology

Risultato:

Query per la relazione "molti-a-molti"

Ora che abbiamo i dati, è il momento di sfruttarli!

Come trovare tutti i corsi a cui è iscritto uno studente?

Per esempio, per sapere a quali corsi è iscritta Alice (ID = 1), fai questa query:

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

Risultato:

Come trovare tutti gli studenti iscritti a un certo corso?

Supponiamo di voler sapere chi è iscritto al corso Mathematics (ID = 1):

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

Risultato:

Come trovare studenti e i loro corsi?

Per avere la panoramica completa di chi è iscritto a cosa, fai questa query:

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Risultato:

La tabella enrollments rende il nostro schema super flessibile — possiamo aggiungere o togliere collegamenti tra studenti e corsi senza toccare le tabelle principali. Grazie alle query JOIN trovi subito quello che ti serve, tipo chi è iscritto a quale corso. E le chiavi esterne controllano automaticamente che non ci siano errori — per esempio, che nessuno iscriva uno studente a un corso che non esiste.

Errori tipici nelle relazioni "molti-a-molti"

Mancanza di vincolo di unicità: Se non metti il PRIMARY KEY, rischi di inserire lo stesso collegamento più volte.

Violazione dell'integrità dei dati: Se provi a inserire un record con uno student_id o course_id che non esiste, ottieni un errore.

Ordine sbagliato nell'eliminazione dei dati: Se elimini prima un corso da courses, i record in enrollments restano "orfani". Per evitarlo usa ON DELETE CASCADE nella definizione delle chiavi esterne.