Perché la progettazione del database è critica
Un database mal progettato è difficile da correggere a produzione avviata. Duplicare dati, avere relazioni implicite o scegliere tipi sbagliati crea problemi a cascata: query lente, dati inconsistenti, migrazioni dolorose. Un database ben progettato invece si adatta alla crescita del prodotto con modifiche minimali.
La progettazione si svolge in tre fasi distinte: modello concettuale (cosa esiste nel dominio), modello logico (come le entità si relazionano), modello fisico (il DDL SQL vero e proprio con tipi, indici, vincoli).
Fase 1 — Identificare le entità
Parti dai requisiti funzionali. Ogni sostantivo rilevante del dominio è un candidato a diventare una tabella. Esempio: un e-commerce parla di clienti, prodotti, ordini, categorie, indirizzi di spedizione, metodi di pagamento.
Per ogni entità, identifica gli attributi (le colonne) distinguendo tra:
- Attributi identificativi: l'ID univoco, tipicamente la primary key
- Attributi descrittivi: nome, email, prezzo — le proprietà "di contenuto"
- Attributi di stato: attivo/inattivo, published, deleted_at
- Attributi temporali: created_at, updated_at — quasi sempre necessari
Regola pratica: se un attributo può avere valori multipli per la stessa entità (es. un utente con più numeri di telefono), non metterlo come colonna — crea una tabella separata.
Fase 2 — Definire le relazioni
Le relazioni tra entità si classificano in tre tipi, ognuno con una traduzione SQL precisa:
Uno-a-molti (1:N) — la più comune
Un cliente può avere molti ordini, ma ogni ordine appartiene a un solo cliente. Si implementa aggiungendo una colonna FK nella tabella "molti" che punta alla PK della tabella "uno".
-- Tabella "uno"
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
full_name VARCHAR(100) NOT NULL,
-- altri campi...
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tabella "molti" con FK che punta a customers
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
status ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
total_cents INT NOT NULL DEFAULT 0,
-- altri campi...
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);
Molti-a-molti (N:M) — richiede una tabella ponte
Un ordine può contenere molti prodotti, e un prodotto può comparire in molti ordini. La soluzione è una tabella di associazione con chiave primaria composta sui due FK.
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price_cents INT NOT NULL, -- snapshot del prezzo al momento dell'acquisto
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);
Perché salvare il prezzo nella tabella ponte? Il prezzo di un prodotto cambia nel tempo. Se memorizzi solo il riferimento al prodotto, ricalcolare il totale storico di un ordine diventerebbe impossibile. Sempre snapshot i dati che cambiano.
Uno-a-uno (1:1) — usato per separare dati voluminosi o opzionali
Un utente ha un solo profilo esteso. Si può mettere tutto in una tabella, ma separare i dati usati di rado (bio, avatar, preferenze) riduce la dimensione delle righe nelle query frequenti.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY, -- PK = FK = relazione 1:1
bio TEXT,
avatar_url VARCHAR(500),
birth_date DATE,
-- altri campi opzionali pesanti...
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
Fase 3 — Scegliere le chiavi primarie
La scelta della PK ha impatto su performance, sicurezza e scalabilità.
| Tipo | Pro | Contro | Quando usarlo |
|---|
| INT AUTO_INCREMENT | Compatto, veloce, sequenziale | Espone volume dati, no-go in sistemi distribuiti | Tabelle interne, join frequenti |
| UUID v4 | Non prevedibile, merge-safe, distribuito | 16 byte, indici più lenti, leggibilità scarsa | ID esposti in URL, sistemi multi-database |
| UUID v7 / ULID | Ordinabile nel tempo, compatibile UUID | Meno supporto nativo | Sistemi distribuiti moderni |
| Chiave naturale | Significativa, no colonna extra | Può cambiare, difficile da aggiornare | Codici stabili (es. ISO country code) |
| Chiave composta | Nessuna colonna extra, semantica chiara | FK più verbose | Tabelle ponte N:M |
Fase 4 — La normalizzazione
La normalizzazione è un processo formale per eliminare ridondanze. Le tre forme normali principali:
Prima Forma Normale (1NF)
Ogni colonna deve contenere un solo valore atomico. Non sono ammesse colonne con valori multipli o colonne ripetute come telefono1, telefono2.
-- ❌ Violazione 1NF
CREATE TABLE contacts (
id INT PRIMARY KEY,
name VARCHAR(100),
phones VARCHAR(500) -- "333-111, 333-222" — non atomico!
);
-- ✅ Corretto: tabella separata
CREATE TABLE contact_phones (
contact_id INT NOT NULL,
phone VARCHAR(20) NOT NULL,
type ENUM('mobile','home','work') DEFAULT 'mobile',
PRIMARY KEY (contact_id, phone),
FOREIGN KEY (contact_id) REFERENCES contacts(id)
);
Seconda Forma Normale (2NF)
Tutti gli attributi non-chiave devono dipendere dall'intera chiave primaria, non da una sua parte. Rilevante solo con chiavi composite.
-- ❌ Violazione 2NF: product_name dipende solo da product_id,
-- non dalla chiave composta (order_id, product_id)
CREATE TABLE order_items_bad (
order_id INT,
product_id INT,
product_name VARCHAR(255), -- dipendenza parziale!
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- ✅ Corretto: product_name sta in products
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
unit_price_cents INT,
PRIMARY KEY (order_id, product_id)
);
Terza Forma Normale (3NF)
Nessun attributo non-chiave deve dipendere da un altro attributo non-chiave (dipendenze transitive).
-- ❌ Violazione 3NF: zip_code → city → region (dipendenza transitiva)
CREATE TABLE addresses_bad (
id INT PRIMARY KEY,
zip_code CHAR(5),
city VARCHAR(100), -- dipende da zip_code, non dall'ID
region VARCHAR(100) -- dipende da city, non dall'ID
);
-- ✅ Corretto: zip_codes separati
CREATE TABLE zip_codes (
zip_code CHAR(5) PRIMARY KEY,
city VARCHAR(100) NOT NULL,
region VARCHAR(100) NOT NULL
);
CREATE TABLE addresses (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
street VARCHAR(255),
zip_code CHAR(5),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);
Schema completo: sistema blog con tutti i pattern
Ecco uno schema realistico che integra tutti i concetti visti — adattabile a qualsiasi progetto editoriale. I campi marcati con -- TODO sono volutamente lasciati da completare secondo le esigenze specifiche.
-- ══ BLOG DATABASE SCHEMA ══
-- Dialetto: MySQL 8+ / MariaDB 10.6+
-- Adatta i tipi per PostgreSQL rimuovendo ENGINE= e CHARSET=
CREATE DATABASE IF NOT EXISTS `blog_db`
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `blog_db`;
-- ── 1. Utenti ──────────────────────────────
CREATE TABLE `users` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`email` VARCHAR(255) NOT NULL UNIQUE,
`username` VARCHAR(50) NOT NULL UNIQUE,
`password_hash` VARCHAR(255) NOT NULL,
`display_name` VARCHAR(100),
`role` ENUM('reader','author','editor','admin') NOT NULL DEFAULT 'reader',
`is_active` BOOLEAN NOT NULL DEFAULT TRUE,
`email_verified` BOOLEAN NOT NULL DEFAULT FALSE,
-- TODO: avatar_url, bio, social links, preferences JSON...
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 2. Categorie ────────────────────────────
CREATE TABLE `categories` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`slug` VARCHAR(100) NOT NULL UNIQUE,
`name` VARCHAR(100) NOT NULL,
`description` TEXT,
`parent_id` INT DEFAULT NULL, -- auto-referenza per categorie annidate
`sort_order` INT NOT NULL DEFAULT 0,
FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 3. Tag ─────────────────────────────────
CREATE TABLE `tags` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`slug` VARCHAR(100) NOT NULL UNIQUE,
`name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 4. Post ─────────────────────────────────
CREATE TABLE `posts` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`slug` VARCHAR(255) NOT NULL UNIQUE,
`title` VARCHAR(500) NOT NULL,
`excerpt` TEXT,
`content` LONGTEXT NOT NULL,
`status` ENUM('draft','review','published','archived') NOT NULL DEFAULT 'draft',
`author_id` INT NOT NULL,
`category_id` INT,
`featured_img` VARCHAR(500),
`meta_title` VARCHAR(70), -- SEO: max 70 chars
`meta_desc` VARCHAR(160), -- SEO: max 160 chars
-- TODO: schema markup JSON, reading_time, views_count, og_image...
`published_at` TIMESTAMP NULL DEFAULT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
INDEX `idx_status_published` (`status`, `published_at`),
INDEX `idx_author` (`author_id`),
FULLTEXT INDEX `ft_search` (`title`, `excerpt`, `content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 5. Post ↔ Tag (N:M) ─────────────────────
CREATE TABLE `post_tags` (
`post_id` INT NOT NULL,
`tag_id` INT NOT NULL,
PRIMARY KEY (`post_id`, `tag_id`),
FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 6. Commenti (albero) ────────────────────
CREATE TABLE `comments` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`post_id` INT NOT NULL,
`user_id` INT,
`parent_id` INT DEFAULT NULL, -- per commenti annidati/risposte
`author_name` VARCHAR(100), -- per commenti anonimi
`author_email` VARCHAR(255),
`body` TEXT NOT NULL,
`status` ENUM('pending','approved','spam','deleted') NOT NULL DEFAULT 'pending',
-- TODO: likes_count, ip_address, user_agent...
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
FOREIGN KEY (`parent_id`) REFERENCES `comments`(`id`) ON DELETE CASCADE,
INDEX `idx_post_status` (`post_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 7. Sessioni utente ──────────────────────
CREATE TABLE `sessions` (
`id` CHAR(36) PRIMARY KEY, -- UUID
`user_id` INT NOT NULL,
`token_hash` VARCHAR(64) NOT NULL UNIQUE,
`ip` VARCHAR(45),
`user_agent` TEXT,
`expires_at` TIMESTAMP NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ── 8. Media / uploads ──────────────────────
CREATE TABLE `media` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`uploader_id` INT,
`filename` VARCHAR(255) NOT NULL,
`mime_type` VARCHAR(100) NOT NULL,
`size_bytes` INT UNSIGNED NOT NULL,
`url` VARCHAR(500) NOT NULL,
`alt_text` VARCHAR(255),
-- TODO: width, height, blurhash, storage_provider...
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (`uploader_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Indici: quando e come usarli
Un indice accelera le letture ma rallenta le scritture e occupa spazio. Le regole pratiche:
- Sempre: le colonne usate in
WHERE, JOIN, ORDER BY frequenti
- Spesso: le FK (MySQL non le indicizza automaticamente, PostgreSQL sì)
- Considera: indici composti per query con filtri multipli — l'ordine conta (left-prefix rule)
- Evita: colonne con bassa cardinalità (es.
boolean) — il planner spesso ignora l'indice
- Usa FULLTEXT: per ricerca testuale su
TEXT/VARCHAR lunghi
ON DELETE: scegliere la strategia giusta
Quando elimini un record padre, cosa succede ai figli? La scelta è parte del modello concettuale:
RESTRICT / NO ACTION: blocca l'eliminazione se esistono righe figlio — il più sicuroCASCADE: elimina automaticamente i figli — utile per commenti di un post eliminatoSET NULL: imposta la FK a NULL — per relazioni opzionali (es. categoria eliminata → post senza categoria)SET DEFAULT: imposta un valore di default — raro, non supportato da tutti i DB
Soft delete: la tecnica dei dati "cancellati ma non eliminati"
Molti sistemi preferiscono non cancellare mai i dati fisicamente, ma marcarli come eliminati. Si aggiunge una colonna deleted_at TIMESTAMP NULL DEFAULT NULL: se NULL il record è attivo, altrimenti contiene il timestamp dell'eliminazione. Tutte le query aggiungono WHERE deleted_at IS NULL.
Vantaggi: cronologia completa, possibilità di "undelete", conformità GDPR facilitata (puoi anonimizzare invece di cancellare). Svantaggio: le query sono leggermente più complesse e le tabelle crescono.
FAQ
Quante tabelle deve avere un database? ▾
Non esiste un numero ideale. Ogni entità del dominio diventa una tabella, più le tabelle di associazione per le relazioni N:M. Un progetto piccolo ha 5–15 tabelle, un ERP anche 500+. La metrica giusta non è il numero, ma l'assenza di ridondanza.
Quando usare UUID invece di INT AUTO_INCREMENT? ▾
UUID è preferibile quando il sistema è distribuito, si fa merge di database, o non vuoi esporre sequenze prevedibili negli URL. INT AUTO_INCREMENT è più compatto, veloce sugli indici e più leggibile nei log. UUID v7 e ULID offrono il meglio di entrambi (ordinabili, non prevedibili).
Devo sempre normalizzare al massimo? ▾
No. In sistemi OLAP (data warehouse, analytics) la denormalizzazione migliora le performance di lettura. In sistemi OLTP (applicazioni web transazionali) la 3NF è spesso il giusto compromesso. Valuta sempre il profilo di lettura/scrittura.
Cos'è una chiave primaria composta e quando usarla? ▾
È una PK formata da due o più colonne. Si usa tipicamente nelle tabelle ponte delle relazioni N:M: la combinazione (order_id, product_id) è univoca per definizione del dominio, quindi non serve aggiungere un ID artificiale. Aggiunge anche un vincolo di unicità implicito sulla coppia.