Relazione molti a molti - tabella ponte e query SQL

Un corso può avere centinaia di studenti e ogni studente può seguirne diversi, proprio come un prodotto può comparire in molti ordini. Questo articolo spiega come progettare una relazione molti a molti, come trasformarla in tabelle SQL, come interrogarla senza duplicare i dati e quali errori evitare nei database relazionali e nei modelli di business intelligence.

Che cosa significa davvero questo tipo di relazione

Si parla di questo modello quando un record della tabella A può essere collegato a molti record della tabella B e, allo stesso tempo, ogni record della tabella B può collegarsi a molti record della tabella A. L’esempio più intuitivo è quello tra studenti e corsi: uno studente segue più corsi e un corso comprende più studenti.

Lo stesso schema compare tra prodotti e ordini, film e attori, articoli e categorie, clienti e campagne marketing. La relazione descrive quindi un fatto del mondo reale, non una semplice scelta tecnica. Il database deve rappresentare quel fatto senza ripetere inutilmente le informazioni.

Nei database relazionali non conviene memorizzare direttamente più identificativi in una singola colonna, per esempio “3, 7, 12”. È una soluzione apparentemente rapida, ma rende difficili i filtri, i join e i controlli di integrità. Secondo Microsoft Learn, il modo ordinato per gestire il collegamento consiste nel creare una tabella intermedia e due relazioni uno a molti.

La tabella ponte è il cuore del modello

La tabella intermedia, chiamata anche tabella ponte, di giunzione o di associazione, contiene una riga per ogni legame tra le due entità. Nel caso di studenti e corsi, ogni riga rappresenta una singola iscrizione.

CREATE TABLE studenti (
    id INT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL
);

CREATE TABLE corsi (
    id INT PRIMARY KEY,
    titolo VARCHAR(150) NOT NULL
);

CREATE TABLE iscrizioni (
    studente_id INT NOT NULL,
    corso_id INT NOT NULL,
    data_iscrizione DATE NOT NULL,
    voto DECIMAL(4,2),

    PRIMARY KEY (studente_id, corso_id),
    FOREIGN KEY (studente_id) REFERENCES studenti(id),
    FOREIGN KEY (corso_id) REFERENCES corsi(id)
);

La chiave primaria composta da studente_id e corso_id impedisce di registrare due volte la stessa iscrizione. Le chiavi esterne, invece, assicurano che non si possa associare un corso inesistente a uno studente inesistente.

Un vantaggio spesso sottovalutato è che la tabella ponte può contenere gli attributi del legame. La data di iscrizione, il voto, il ruolo dell’utente, la quantità acquistata o il prezzo applicato non appartengono necessariamente a una delle due tabelle principali: descrivono l’associazione tra loro.

Quando serve una chiave autonoma

La chiave composta è una buona scelta quando tra due entità può esistere un solo legame attivo. Se invece uno studente può iscriversi allo stesso corso in edizioni diverse, la coppia di identificativi non basta.

In quel caso si può aggiungere l’identificativo dell’edizione alla chiave, oppure usare una colonna autonoma come iscrizione_id e mantenere comunque un vincolo UNIQUE sui campi che devono restare unici. Io preferisco decidere questa regola prima di creare le tabelle, perché correggerla dopo aver caricato milioni di righe può essere costoso.

Come progettare e interrogare il collegamento

La progettazione funziona meglio se parte dalle domande operative. Prima individuo le due entità, poi stabilisco che cosa rappresenta esattamente una riga della tabella ponte e infine definisco le regole di unicità.

1. Identifica le entità principali, per esempio clienti e prodotti.
2. Definisci il significato del legame, come acquisto, iscrizione o partecipazione.
3. Aggiungi una riga nella tabella ponte per ogni associazione reale.
4. Imposta chiavi esterne e vincoli per proteggere la qualità dei dati.
5. Crea indici sulle colonne usate spesso nei filtri e nei join.

Per ottenere gli studenti iscritti a un corso, il database deve attraversare la tabella di collegamento. Un join esplicito rende il percorso chiaro e riduce il rischio di confondere attributi dell’entità con attributi dell’iscrizione.

SELECT s.nome, c.titolo, i.data_iscrizione
FROM studenti AS s
JOIN iscrizioni AS i ON i.studente_id = s.id
JOIN corsi AS c ON c.id = i.corso_id
WHERE c.id = 10;

Per vedere tutti i corsi frequentati da uno studente basta invertire il punto di filtro. La struttura resta la stessa, perché la tabella ponte è navigabile in entrambe le direzioni.

SELECT c.titolo, i.voto
FROM corsi AS c
JOIN iscrizioni AS i ON i.corso_id = c.id
WHERE i.studente_id = 42;

Gli indici diventano importanti quando la tabella ponte cresce. Oltre alla chiave primaria, può servire un indice su corso_id se le interrogazioni cercano spesso tutti gli studenti di un corso. La scelta va verificata con il piano di esecuzione, perché ogni indice velocizza le letture ma rende più costosi inserimenti e aggiornamenti.

Gli esempi più utili nella pratica

Prodotti e ordini

Un ordine contiene più prodotti e uno stesso prodotto può apparire in molti ordini. La tabella righe_ordine registra quantità, prezzo applicato e sconti, informazioni che cambiano da un ordine all’altro.

Questo esempio insegna una regola fondamentale: il prezzo nella riga d’ordine non va letto dalla tabella prodotti se si vuole conservare lo storico. Il prezzo attuale del catalogo e il prezzo realmente pagato sono due fatti diversi.

Articoli e categorie

Un articolo può appartenere a più categorie e una categoria può comprendere molti articoli. La tabella ponte può contenere soltanto articolo_id e categoria_id, ma può anche aggiungere un campo come ordine_visualizzazione per stabilire quale categoria mostrare per prima.

Leggi anche: Da XML a Excel con Power Query - guida pratica

Utenti e ruoli

Nei sistemi applicativi, un utente può avere più ruoli e un ruolo può essere assegnato a molti utenti. La tabella utenti_ruoli è semplice, ma sostiene autorizzazioni importanti. Qui controllo con particolare attenzione duplicati, revoche e date di validità, perché un errore di modellazione può concedere permessi non previsti.

Che cosa cambia nei modelli di business intelligence

In un database operativo il legame serve soprattutto a registrare correttamente le associazioni. In un modello BI, invece, bisogna capire qual è la granularità della riga, cioè quale evento o combinazione di eventi rappresenta ogni record.

Per esempio, una tabella ponte tra clienti e segmenti può essere sufficiente per filtrare un’anagrafica. Se però la si collega direttamente a una tabella vendite senza definire il percorso corretto, lo stesso cliente può comparire più volte e i ricavi possono essere conteggiati più di una volta.

In Power BI e in strumenti simili, una tabella ponte o una tabella dei fatti può gestire il percorso tra due dimensioni. Microsoft Learn segnala che scenari come clienti con più account o studenti iscritti a più corsi richiedono particolare attenzione ai filtri e ai percorsi di relazione.

La mia regola pratica è separare sempre tre elementi quando servono: dimensioni descrittive, eventi misurabili e tabella di associazione. Prima di costruire una misura, verifico quante righe rappresentano davvero una vendita, un’iscrizione o un’interazione. È questo controllo, più della semplice presenza delle relazioni, a evitare report apparentemente plausibili ma numericamente sbagliati.

Gli errori che compromettono dati e prestazioni

• Inserire liste di ID in una colonna. Valori come “2,5,9” impediscono join affidabili e complicano la validazione.
• Dimenticare la regola di unicità. Senza chiave composta o vincolo UNIQUE, lo stesso collegamento può essere registrato più volte.
• Usare nomi al posto degli identificativi. I nomi possono cambiare o non essere univoci; le chiavi surrogate sono più stabili.
• Non indicizzare il lato filtrato. Una tabella ponte molto grande può diventare lenta se manca un indice sulla seconda chiave esterna.
• Cancellare dati senza una politica chiara. ON DELETE CASCADE può essere utile, ma in un sistema storico può eliminare informazioni che devono restare conservate.
• Confondere il legame con l’entità. Se l’associazione ha stato, data, prezzo o quantità, probabilmente merita una struttura propria e non un semplice campo aggiuntivo casuale.

Un altro errore frequente nasce dall’uso automatico di un ORM, cioè uno strumento che mappa le tabelle agli oggetti dell’applicazione. L’ORM può semplificare il codice, ma non decide al posto nostro se due iscrizioni uguali siano davvero ammesse. La regola deve vivere anche nel database, non soltanto nell’interfaccia.

La qualità del legame decide la qualità dell’analisi

Una relazione ben progettata non è solo un collegamento tra due tabelle. È una descrizione precisa di un fatto, con chiavi coerenti, vincoli comprensibili e una granularità che resta stabile nel tempo.

Quando modello questo scenario, parto sempre da una domanda concreta: “Che cosa rappresenta una riga?”. Se la risposta è chiara, scegliere la tabella ponte, la chiave primaria e gli indici diventa molto più semplice.

Il risultato è un database più pulito e un modello analitico più affidabile. La tabella ponte può sembrare un passaggio in più, ma nella pratica è ciò che evita duplicazioni, conserva gli attributi del legame e rende leggibili le interrogazioni anche quando il progetto cresce.