Database Design — Cheat Sheet

Tag: database, sql, nosql, normalization, indexing, data-modeling, postgresql

Livello: intermedio

TL;DR: Regole di modellazione relazionale e documentale, trade-off tra normalizzazione e performance, strategie di indicizzazione e consistenza transazionale.

Concetti chiave

Modellazione concettuale, logica e fisica definiscono entità, relazioni e vincoli implementativi.

Prima forma normale (1NF) impone atomicita' dei valori e assenza di gruppi ripetuti.

Seconda forma normale (2NF) richiede 1NF e dipendenza funzionale completa dalla chiave primaria.

Terza forma normale (3NF) elimina le dipendenze transitive tra colonne non chiave.

Forma normale di Boyce-Codd (BCNF) rafforza 3NF per chiavi candidate multiple sovrapposte.

Denormalizzazione calcolata riduce join costosi in scenari read-heavy a discapito della consistenza.

Indici B-Tree ottimizzano uguaglianze e range query; indici Hash coprono solo uguaglianze.

Chiavi primarie surrogate (UUID/BigInt) isolano il modello da mutazioni del business logic.

Vincoli di integrita' referenziale (foreign key) garantiscono consistenza a livello di engine.

Proprieta' ACID garantiscono atomicita', consistenza, isolamento e durabilita' nelle transazioni.

Domande tipiche e risposte pronte

Q: Quando conviene normalizzare e quando denormalizzare una base dati?R: Normalizzo fino alla 3NF in sistemi OLTP per evitare anomalie di scrittura e ridondanze; denormalizzo in modo mirato quando i profili di lettura richiedono troppe join e impattano la latenza.

Q: Come scegli tra una chiave primaria UUID v4/v7 e un BigInt sequenziale?R: Preferisco BigInt auto-increment per compattezza dell'indice B-Tree e inserimenti ordinati, ma scelgo UUID v7 quando devo generare identificatori distribuiti preservando la localita' temporale.

Q: Come si gestisce la cardinalita' many-to-many nel modello relazionale?R: Introduco una junction table con chiavi esterne composte verso le due entita', arricchendola di metadati di relazione e indici dedicati per query bidirezionali.

Q: Come decidi se un indice composito e' efficace?R: Ordino le colonne seguendo la regola del leftmost prefix, mettendo per prime quelle con filtri di eguaglianza ad alta selettivita' e infine quelle per ordinamenti o range.

Insidie e errori comuni

Sovra-indicizzazione: ogni indice degrada il throughput di insert, update e delete.

Mancanza di indici sulle foreign key: causa lock estesi o table scan nei join e cascade.

Selezione errata della collation o del charset: genera table scan per mismatch di tipi nei join.

Uso improprio di tipi generici (ad esempio VARCHAR illimitati o stringhe per date/timestamp).

Ignorare il fenomeno dell'hot spotting su indici sequenziali in architetture distribuite sharded.

Snippet e comandiSQL

```-- Creazione tabella con vincoli e foreign key

CREATE TABLE orders (

order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

customer_id BIGINT NOT NULL,

status VARCHAR(20) NOT NULL DEFAULT 'pending',

created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_customer FOREIGN KEY (customer_id)

REFERENCES customers (customer_id) ON DELETE RESTRICT

);

-- Indice composito per ottimizzare filtri cliente e data

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

```

Da ripassare

[ ] Analisi dei piani di esecuzione con EXPLAIN ANALYZE

[ ] Livelli di isolamento transazionale (Read Committed, Repeatable Read, Serializable)

[ ] Strategie di partitioning (range, list, hash) per tabelle di grandi dimensioni

[x] Regole di normalizzazione da 1NF a 3NF

[x] Definizione di chiavi primarie, candidate e vincoli referenziali