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

Come progettare uno schema di database da zero: passo dopo passo

Progetta uno schema di database relazionale da zero. Una guida passo passo dai requisiti a tabelle, relazioni e vincoli con SQL.

I requisiti diventano entità, relazioni e uno schema SQL con chiavi di clienti e ordini
In questa guida
  1. Perché la progettazione dello schema è importante
  2. Passaggio 1 — Raccogli i requisiti
  3. Passaggio 2 — Identifica le entità
  4. Passaggio 3 — Definisci attributi e tipi di dati
  5. Passaggio 4 — Stabilisci le relazioni
  6. Passaggio 5 — Applica vincoli e indici
  7. Passaggio 6 — Normalizza (quindi denormalizza dove necessario)
  8. 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.

Perché la progettazione dello schema è importante

Uno schema di database ben progettato è la base di ogni applicazione affidabile. Fallo bene e le query sono veloci, i Dati rimangono consistenti e le funzionalità future si inseriscono senza dolorose migrazioni. Fallo male e combatterai anomalie dei Dati, prestazioni lente e infinite soluzioni alternative per tutta la vita del progetto.

Questa guida illustra un processo in sei passaggi per progettare uno schema da zero — e termina con un esempio reale completo che puoi adattare per i tuoi progetti.

Passaggio 1 — Raccogli i requisiti

Prima di aprire qualsiasi strumento, parla con gli stakeholder. Comprendi cosa l'applicazione fa, quali dati gestisce e come tali dati si relazionano. Fai domande come:

  • Quali sono le "cose" principali tracciate dal sistema? (Utenti, ordini, prodotti …)
  • Quali dati devono essere unici? (Email, SKU, slug …)
  • Quali query eseguirà più spesso l'applicazione?
  • Ci sono vincoli normativi? (GDPR, HIPAA …)
  • Qual è il volume di dati previsto nei prossimi 1–3 anni?

Scrivi le risposte: diventeranno le tue specifiche e guideranno ogni decisione successiva.

Passaggio 2 — Identifica le entità

Un entità è qualsiasi cosa di cui il sistema debba memorizzare i Dati. Ogni entità si mappa in genere su una tabella del database. Per una piattaforma di blog, l'elenco iniziale delle entità potrebbe essere il seguente:

  • Utente — autori e lettori
  • Post — articoli pubblicati sul blog
  • Categoria — raggruppamenti per i post (es. "React", "SQL")
  • Commento — risposte dei lettori ai post
  • Tag — etichette dettagliate (molti-a-molti con i post)

Disegnali come caselle su una lavagna o usa l' Editor ERD per discutere del modello concettuale prima di scegliere i tipi di colonna.

Passaggio 3 — Definisci attributi e tipi di dati

Per ogni entità, elenca gli attributi di cui ha bisogno e scegli il tipo di dati SQL più appropriato. Sii il più specifico possibile — preferisci VARCHAR(255) su TEXT quando conosci la lunghezza massima e preferisci TIMESTAMPTZ su TIMESTAMP quando i fusi orari sono importanti.

-- Users
id          SERIAL PRIMARY KEY
username    VARCHAR(50)  NOT NULL UNIQUE
email       VARCHAR(255) NOT NULL UNIQUE
password_hash TEXT      NOT NULL          -- store a password hash, never plaintext
bio         TEXT
avatar_url  VARCHAR(500)
created_at  TIMESTAMPTZ  DEFAULT now()

-- Posts
id          SERIAL PRIMARY KEY
author_id   INT          NOT NULL          -- FK → users.id
category_id INT                            -- FK → categories.id
title       VARCHAR(200) NOT NULL
slug        VARCHAR(220) NOT NULL UNIQUE
body        TEXT         NOT NULL
status      VARCHAR(20)  DEFAULT 'draft'   -- draft | published | archived
published_at TIMESTAMPTZ
created_at  TIMESTAMPTZ  DEFAULT now()
updated_at  TIMESTAMPTZ  DEFAULT now()

Passaggio 4 — Stabilisci le relazioni

Determina come le entità si relazionano tra loro. I tre tipi fondamentali sono:

  • Uno-a-Molti (1:N) — Un utente ha molti post; un post appartiene a un solo utente.
  • Molti-a-Molti (M:N) — Un post può avere molti tag; un tag può appartenere a molti post. Questo richiede una tabella di giunzione.
  • Uno-a-Uno (1:1) — Un utente ha un solo profilo. Spesso uniti in un'unica tabella a meno che i dati del profilo non siano di grandi dimensioni o raramente consultati.
-- Junction table for the posts ↔ tags many-to-many relationship
CREATE TABLE post_tags (
    post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    tag_id  INT NOT NULL REFERENCES tags  (id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Passaggio 5 — Applica vincoli e indici

I vincoli proteggono la qualità dei dati. Come minimo dovresti considerare:

  • NOT NULL su ogni colonna che deve avere un valore.
  • UNIQUE su identificatori naturali (email, slug, nomi utente).
  • CHECK vincoli per regole di dominio (ad es., lo stato deve essere uno di tre valori).
  • DEFAULT valori per le colonne timestamp e status.
  • Chiavi estere con esplicito ON DELETE azioni.

Quindi aggiungi indici sulle colonne su cui effettuerai frequentemente filtri, ordinamenti o join. Le colonne di chiave esterna sono candidate ideali.

-- Index foreign-key columns for faster joins
CREATE INDEX idx_posts_author   ON posts (author_id);
CREATE INDEX idx_posts_category ON posts (category_id);
CREATE INDEX idx_comments_post  ON comments (post_id);

-- Partial index: only published posts (PostgreSQL)
CREATE INDEX idx_posts_published
    ON posts (published_at DESC)
    WHERE status = 'published';

Passaggio 6 — Normalizza (quindi denormalizza dove necessario)

Normalizzazione elimina i dati ridondanti organizzando colonne e tabelle in base a un insieme di "forme normali". La maggior parte degli schemi dovrebbe raggiungere Terza Forma Normale (3NF):

  • 1NF — ogni colonna contiene valori atomici; nessun gruppo ripetuto.
  • 2NF — nessun attributo non principale dipende solo da una parte di una chiave candidata.
  • 3NF — ogni dipendenza non banale ha un determinante che è una superchiave o un attributo dipendente che appartiene a una chiave candidata.

Dopo la normalizzazione, puoi selettivamente denormalizzare per le prestazioni — ad esempio, memorizzando un comment_count su posts tabella per evitare un costoso COUNT(*) a ogni caricamento della pagina. Denormalizza deliberatamente e documentalo.

Schema completo della piattaforma di blog

-- Full SQL for the blog platform schema

CREATE TABLE users (
    id          SERIAL       PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password_hash TEXT      NOT NULL,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    created_at  TIMESTAMPTZ  DEFAULT now()
);

CREATE TABLE categories (
    id   SERIAL       PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    slug VARCHAR(110) NOT NULL UNIQUE
);

CREATE TABLE posts (
    id           SERIAL       PRIMARY KEY,
    author_id    INT          NOT NULL REFERENCES users (id),
    category_id  INT          REFERENCES categories (id) ON DELETE SET NULL,
    title        VARCHAR(200) NOT NULL,
    slug         VARCHAR(220) NOT NULL UNIQUE,
    body         TEXT         NOT NULL,
    status       VARCHAR(20)  NOT NULL DEFAULT 'draft'
                     CHECK (status IN ('draft','published','archived')),
    published_at TIMESTAMPTZ,
    created_at   TIMESTAMPTZ  DEFAULT now(),
    updated_at   TIMESTAMPTZ  DEFAULT now()
);

CREATE TABLE tags (
    id   SERIAL       PRIMARY KEY,
    name VARCHAR(60)  NOT NULL UNIQUE
);

CREATE TABLE post_tags (
    post_id INT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    tag_id  INT NOT NULL REFERENCES tags  (id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE comments (
    id         SERIAL      PRIMARY KEY,
    post_id    INT         NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    user_id    INT         NOT NULL REFERENCES users (id),
    body       TEXT        NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Indexes
CREATE INDEX idx_posts_author    ON posts    (author_id);
CREATE INDEX idx_posts_category  ON posts    (category_id);
CREATE INDEX idx_comments_post   ON comments (post_id);
CREATE INDEX idx_comments_user   ON comments (user_id);

Incolla lo schema completo in Editor SQL a ERD per ispezionare le sue relazioni. Usa il Editor ERD per un disegno concettuale e una conversione SQL revisionata.

Riferimenti e letture aggiuntive

Altri post