Torna a tutte le guide
Progettazione di database··6 min di lettura

Uno a molti o molti a molti: modelli di relazioni SQL

Scopri le relazioni uno-a-molti rispetto a molti-a-molti in SQL. Scopri come implementare ciascuna con chiavi esterne, tabelle di giunzione ed esempi reali.

Chiave esterna da Customer a order e tabella di giunzione da student a course con una chiave primaria composita
In questa guida
  1. Comprendere i pattern di relazione SQL
  2. Relazioni Uno-a-Molti
  3. Relazioni Molti-a-Molti
  4. Relazioni Uno-a-Uno
  5. Come appaiono le relazioni negli ERD
  6. Scelta del modello di relazione corretto
  7. Riferimenti e letture aggiuntive

Gli esempi SQL utilizzano PostgreSQL a meno che non sia specificato un altro dialetto. Brevi frammenti presuppongono che le tabelle a cui fanno riferimento esistano già. Convalida il DDL nel tuo database di destinazione prima di applicarlo.

Comprendere i pattern di relazione SQL

I database relazionali traggono la loro potenza dalle connessioni tra le tabelle. Che tu stia progettando una piccola applicazione o un data warehouse aziendale, la scelta del corretto modello di relazione determina l'efficienza con cui puoi archiviare, interrogare e gestire i tuoi dati. I tre modelli fondamentali—uno-a-uno, uno-a-molti, e molti-a-molti— coprono molte associazioni comuni; le relazioni ricorsive e di grado superiore richiedono un'ulteriore modellazione.

In questa guida analizziamo ogni pattern con esempi PostgreSQL, esploriamo il ruolo delle tabelle di giunzione e mostriamo come queste relazioni appaiono su un Diagramma Entità-Relazione (ERD). Se vuoi visualizzare il tuo schema proprio ora, apri l' Editor di Erd Go e incollare supportati CREATE TABLE definizioni per generare un diagramma.

SQL di chiave estera mappato a una relazione ERD tra clienti e ordini
Una chiave esterna è più di una semplice sintassi: in un ERD diventa la linea di relazione che spiega come le tabelle dipendono l'una dall'altra.

Relazioni Uno-a-Molti

A uno-a-molti La relazione (1:N) è il pattern più comune nella progettazione di database. Significa che una singola riga nella genitore tabella può essere riferita da molte righe nella figlia tabella, e una chiave esterna figlia non null punta a un genitore. Una chiave esterna nullable consente un figlio senza genitore.

Esempio classico: Dipartimenti e Dipendenti

Un'azienda ha molti reparti e ogni reparto contiene più dipendenti. Tuttavia, ogni dipendente appartiene a esattamente un reparto in un dato momento.

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    department_id   INT NOT NULL,
    hire_date       DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Dettagli chiave dell'implementazione

  • La chiave esterna (department_id) risiede sempre sul molti lato—il employees tabella in questo caso.
  • ON DELETE RESTRICT impedisce di rimuovere accidentalmente un dipartimento che ha ancora dipendenti assegnati.
  • ON UPDATE CASCADE garantisce che se la chiave primaria di un dipartimento cambia, tutte le righe dei dipendenti si aggiornano automaticamente.
  • Aggiunta di un indice su department_id può aiutare le join e i filtri di dipartimento; conferma il beneficio con dati rappresentativi e un piano di esecuzione.

Interrogazione di dati uno-a-molti

-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';

-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;

Altri esempi quotidiani di uno-a-molti includono autori → post del blog, clienti → ordini, e categorie → prodotti.

Relazioni Molti-a-Molti

A molti-a-molti La relazione (M:N) esiste quando le righe su entrambi i lati possono essere associati a più righe sull'altro lato. I database relazionali non possono rappresentare ciò direttamente con una singola chiave esterna, quindi introduciamo un tabella di giunzione (chiamato anche bridge, join, o associativa tabella).

Esempio classico: Studenti e Corsi

Uno studente può iscriversi a molti corsi e ogni corso può avere molti studenti. La tabella di giunzione enrollments si frappone tra essi e contiene le chiavi esterne per entrambi i lati.

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    credits     SMALLINT NOT NULL DEFAULT 3,
    department  VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

Perché una tabella di giunzione?

  • Il chiave primaria composita (student_id, course_id) consente una riga per ogni coppia studente-corso. Se è richiesta l'iscrizione ripetuta in più periodi, includi un periodo nella chiave aziendale o modella ciascuna iscrizione separatamente.
  • Puoi allegare attributi aggiuntivi alla relazione stessa, come grade o enrolled_at, che non appartengono a nessuna entità da sola.
  • ON DELETE CASCADE su entrambe le chiavi esterne significa che la rimozione di uno studente o di un corso pulisce automaticamente le righe di iscrizione.

Interrogazione di dati molti-a-molti

-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;

-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';

Esempi reali di molti-a-molti includono utenti ↔ ruoli, prodotti ↔ tag, attori ↔ film, e ordini ↔ prodotti (tramite un order_items tabella di giunzione che memorizza anche quantità e prezzo).

Relazioni Uno-a-Uno

A uno-a-uno La relazione (1:1) significa che ogni riga nella Tabella A corrisponde al massimo a una riga nella Tabella B, e viceversa. Questo pattern è meno comune ma utile per suddividere colonne raramente accessibili o sensibili in una tabella separata.

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(60) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birthdate   DATE,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Nota che user_profiles.user_id è sia il chiave primaria e il chiave esterna. Questo vincolo applica la mappatura uno a uno a livello di database: ogni utente può avere al massimo una riga di profilo.

Come appaiono le relazioni negli ERD

Un Diagramma Entità-Relazione utilizza linee e simboli tra le tabelle per trasmettere la cardinalità a colpo d'occhio. Le due notazioni più popolari sono Zampe di gallina e Chen notazione.

RelazioneSimbolo a zampe di gallinaCome leggere
Uno-a-Uno||—||Esattamente uno su ciascun lato
Uno-a-Molti||—<Uno a sinistra, molti a destra (estremità a zampa di gallina)
Molti-a-Molti>—<Molti su entrambi i lati; implementato tramite tabella di giunzione

Questi simboli di testo sono una scorciatoia per la cardinalità massima. L'opzionalità richiede un marcatore aggiuntivo di zero o uno; una chiave esterna da sola non richiede che un genitore abbia dei figli.

Quando modelli una relazione molti-a-molti in uno strumento ERD, in genere vedi tre caselle: due tabelle entità e la tabella di giunzione tra di esse, ciascuna collegata da una linea uno-a-molti.

Incolla uno qualsiasi degli esempi SQL di questo articolo in Editor di Erd Go per vedere il diagramma generato automaticamente, completo di frecce per le chiavi esterne e indicatori di cardinalità.

Scelta del modello di relazione corretto

La selezione della cardinalità errata porta a duplicazione dei dati, query complesse e bug di integrità. Utilizza questo schema decisionale:

  • Una riga sul lato A può essere correlata a molte righe sul lato B, ma non viceversa? → Uno-a-Molti. Inserisci la chiave esterna sul lato B.
  • Le righe su entrambi i lati possono essere correlate a molte righe sull'altro? → Molti-a-Molti. Crea una tabella di giunzione con una chiave composita.
  • La mappatura è rigorosamente 1:1 e hai bisogno della separazione delle tabelle? → Uno-a-Uno. Usa la stessa chiave primaria come chiave esterna.

In caso di dubbio, abbozza prima il tuo schema. Un ERD rapido rivela i problemi di relazione molto prima rispetto a quando ci si imbatte in errori di vincolo in produzione. Prova a costruire il tuo diagramma in Editor di Erd Go— richiede pochi secondi e può far risparmiare ore di debugging.

Riferimenti e letture aggiuntive

Altri post