Progettare Database Relazionali: La Guida Pratica dall’ERD all’Integrità dei Dati

7 Novembre 2025 lascienzadeidati Dentro l'Azienda, La libreria SQLite3 0

Cerca:

Generic selectors
Exact matches only
Search in title
Search in content
Post Type Selectors
come progettare un database relazionale da zero
Views: 891

Progettare un database da zero somiglia più al lavoro di un urbanista che a quello di un semplice costruttore. È facile tirare su tabelle e colonne, così come è facile tracciare strade. La vera sfida è far sì che la città (il database) funzioni tra dieci anni, gestendo il traffico (i dati) senza crollare e, soprattutto, senza far sparire interi quartieri (i dati storici).

Molti tutorial si fermano alle definizioni: chiave primaria, chiave esterna, terza forma normale. Questo articolo fa un passo in più. Non ci concentriamo solo su come creare le tabelle, ma su perché certe decisioni apparentemente innocue, come un ON DELETE CASCADE, possono distruggere l’integrità del tuo sistema.

Partiremo da un foglio bianco e arriveremo a uno schema SQL per un e-commerce, ma l’obiettivo reale è imparare a costruire sistemi che rispettino il loro bene più prezioso: i dati.

1. Panoramica rapida: cos’è un database relazionale

Un database relazionale organizza i dati in tabelle (relation = tabella) dove ogni riga è una istanza e ogni colonna è un attributo. Le relazioni fra tabelle sono implementate tramite chiavi (PK, FK). Lo scopo della modellazione è rappresentare il dominio reale in modo coerente, minimale (senza ridondanze inutili) e efficiente.

2. Concetti fondamentali

Entità

Un’entità è una cosa del mondo reale che vogliamo rappresentare (es. Cliente, Prodotto, Ordine). In termini pratici: ogni entità diventa una tabella.

  • Ogni tabella rappresenta un tipo di entità.

  • Gli attributi dell’entità diventano colonne della tabella.

Relazioni

Una relazione descrive come due (o più) entità sono correlate.

  • 1:1 (uno-a-uno): raro, spesso si può unire in una sola tabella; utile se si vogliono separare dati opzionali o sensibili.

  • 1:N (uno-a-molti): la relazione più comune. Lato N contiene la FK verso la PK del lato 1.

  • N:M (molti-a-molti): si risolve con una tabella di associazione (junction table) che contiene due FK — spesso con una propria PK surrogata.

Attributi e tipi

Definisci tipi adeguati (VARCHAR(n), TEXT, INTEGER, DATE, TIMESTAMP, DECIMAL(p,s), BOOLEAN, UUID, ecc.) e limiti di lunghezza.

Evita colonne generiche tipo TEXT se puoi essere più specifico.

Pubblicità

3. Regole per le chiavi

Chiave primaria (PK)

  • Deve essere unica e non nulla per ogni riga.

  • Consiglio: usa sempre surrogate key (ID) — cioè una colonna id generata (AUTO_INCREMENT INT o BIGINT, oppure UUID).
    Vantaggi delle surrogate key:

    • Stabilità (non cambia se cambiano gli attributi naturali).

    • Performance sugli indici (numerici, brevi).

    • Semplicità quando ci sono più attributi naturali.
      Svantaggi (da conoscere):

    • Perdita di “significato umano” nella chiave — ma è proprio lo scopo: separare identificatore da contenuto.

  • Regola pratica: definisci id come PK su tutte le tabelle, anche quando esiste una chiave naturale.

Chiave esterna (FK)

  • Una FK è un vincolo che fa riferimento a una PK (o a unicità) di un’altra tabella.

  • Deve rispettare integrità referenziale: non puoi inserire una FK che punti a una PK inesistente (a meno che non sia NULL se permesso).

  • Scegli il comportamento in caso di delete/update (NO ACTION / RESTRICT, CASCADE, SET NULL, SET DEFAULT) in base alle regole di business.

Regola esplicita:

Usa sempre surrogate key (ID) anche se esiste una chiave naturale.
— quindi ogni tabella avrà id come PK.

4. Passi di progettazione (workflow pratico)

  1. Raccogli i requisiti: entità, attributi, processi, regole del dominio.

  2. Identifica le entità principali e le relazioni fra loro.

  3. Disegna un ERD (Entity–Relationship Diagram) con entità/relazioni e cardinalità.

  4. Scegli le PK surrogate (id) per tutte le tabelle.

  5. Spezza le relazioni N:M con tabelle di associazione.

  6. Applica normalizzazione fino alla 3NF (o oltre se occorre). Valuta denormalizzazione solo per performance ben motivate.

  7. Definisci vincoli e indici (FK, UNIQUE, NOT NULL, CHECK, indici su colonne usate nelle query).

  8. Scrivi la DDL (CREATE TABLE) e prova con dati reali.

  9. Testa casi limite: cancellazioni, aggiornamenti, transazioni concorrenti.

  10. Documenta lo schema e le convenzioni di naming.

5. Normalizzazione

Breve riassunto pratico:

1) Prima Forma Normale — 1NF

Regola: ogni campo contiene un singolo valore atomico (no liste, no colonne ripetute, niente colonne multiple come phone1, phone2 non generalizzate).

Esempio non 1NF (tabella customers)

customer_id name phones
1 Maria Rossi 333-1111111, 333-2222222
2 Luca Bianchi 333-3333333

Problema: la colonna phones contiene una lista di valori separati da virgola. Impossibile fare ricerche efficaci, indicizzazione, vincoli di unicità, e aggiornamenti atomici.

Come normalizzare (portare in 1NF)

Separare i telefoni in una tabella customer_phones (una riga per numero).

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);CREATE TABLE customer_phones (

phone_id SERIAL PRIMARY KEY,

customer_id INT NOT NULL REFERENCES customers(customer_id),

phone VARCHAR(20) NOT NULL

);

Risultato: ogni cella è atomica; si possono aggiungere, rimuovere e indicizzare numeri singoli.

2) Seconda Forma Normale — 2NF

Regola: essere in 1NF e nessun attributo non-primo (cioè non parte della chiave primaria) deve dipendere solo da una parte di una PK composita. In pratica: con PK composte, evita attributi che dipendono solo su una parte della PK.

Nota pratica: se usi surrogate key (id) quasi sempre eviti PK composite e molti problemi di 2NF spariscono.

Esempio non 2NF (tabella order_items_products)

Supponiamo uno schema con PK composita (order_id, product_id):

order_id product_id product_name quantity unit_price
1001 P01 Penna Blu 2 1.50
1001 P02 Quaderno A4 1 3.00
1002 P01 Penna Blu 5 1.40

Problema: product_name dipende solo da product_id, non dall’intera PK (order_id, product_id). Questo significa duplicazione di product_name e inconsistenza se viene aggiornato solo in alcune righe.

Dipendenza funzionale rilevante: product_id → product_name

Come normalizzare (portare in 2NF)

Separare l’informazione del prodotto in products e tenere in order_items solo ciò che riguarda l’ordine.

CREATE TABLE products (
product_id VARCHAR(20) PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
current_price DECIMAL(10,2) NOT NULL
);CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

customer_id INT NOT NULL,

order_date TIMESTAMP NOT NULL DEFAULT now()

);CREATE TABLE order_items (

order_id INT NOT NULL REFERENCES orders(order_id),

product_id VARCHAR(20) NOT NULL REFERENCES products(product_id),

quantity INT NOT NULL,

unit_price DECIMAL(10,2) NOT NULL,

PRIMARY KEY (order_id, product_id)

);

Forse potrebbe interessarti anche:  Guida Pratica a Polars: Esercizi con Soluzioni per Analizzare Dati Velocemente

Nota sul unit_price: unit_price è intenzionalmente duplicato rispetto a products.current_price per congelare il prezzo storico (denormalizzazione voluta). Questo non viola la 2NF se consideriamo unit_price come proprietà dell’item nell’ordine (dipende dall’intera PK (order_id,product_id)), non solo da product_id.

3) Terza Forma Normale — 3NF

Regola: essere in 2NF e nessuna dipendenza transitiva: cioè, nessun attributo non PK deve dipendere da un altro attributo non PK.

Formalmente: se A → B e B → C, allora non vogliamo che A → C via B quando B è un attributo non-primo.

Esempio non 3NF (tabella employees)

emp_id name dept_id dept_name dept_location
10 Anna D01 Contabilità Milano
11 Marco D02 Vendite Roma
12 Giulia D01 Contabilità Milano

Problema: dept_name e dept_location dipendono da dept_id, che è un attributo non-primo (non fa parte della PK emp_id). Questo è una dipendenza transitiva:
emp_id → dept_id e dept_id → dept_name, quindi emp_id → dept_name transitivamente. Questo causa ridondanza e possibili inconsistenze se i dati del dipartimento cambiano.

Come normalizzare (portare in 3NF)

Estrarre la tabella dei dipartimenti:

CREATE TABLE departments (
dept_id VARCHAR(10) PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL,
dept_location VARCHAR(100)
);

CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,

name VARCHAR(100) NOT NULL,
dept_id VARCHAR(10) NOT NULL REFERENCES departments(dept_id)
);

Risultato: le informazioni relative al dipartimento sono mantenute in un’unica tabella departments; aggiornamenti al nome o alla sede del dipartimento sono fatti in un solo posto.

Riassunto pratico di come verificare le 3 forme normali

  • 1NF: rimuovi colonne che contengono collezioni/array/CSV; crea tabelle separate per elementi ripetuti.

  • 2NF: se hai una PK composta, verifica che nessun attributo non-primo dipenda solo da una parte della PK. Se succede, estrai la dipendenza in una nuova tabella.

  • 3NF: verifica che NON esistano dipendenze del tipo A → B e B → C dove A è la PK e C è un attributo non-primo; se ci sono, estrai B e C in una nuova tabella.

Considerazioni pratiche / eccezioni utili

  • Surrogate key (id): usare sempre id semplifica molto 2NF perché riduce l’uso di PK composite. Tuttavia attenzione: anche con id devi rispettare 3NF (non lasciare attributi non-primo dipendenti da altri non-primo).

  • Denormalizzazione controllata: campi come unit_price in order_items sono accettabili e spesso necessari per congelare informazioni storiche. Documenta queste eccezioni.

  • Performance vs Purezza: la normalizzazione previene ridondanza e inconsistenza; denormalizzare (es. duplicare alcuni campi) può aiutare performance e reporting. Valuta trade-off e aggiungi indici/materialized views dove serve.

  • 3NF è spesso un buon punto di equilibrio per sistemi OLTP; per data warehousing si usano modelli differenti (star schema, denormalizzazione più spinta).

Esempio completo (mini-flow): da non-normalizzato → 1NF → 2NF → 3NF

Immagina una tabella iniziale sales_raw con colonne: sale_id, customer_name, customer_phones, product_id, product_name, product_price, quantity, dept_name.

  1. Portare in 1NF: separare telefoni in customer_phones.

  2. Portare in 2NF: estrarre products (product_id → product_name, price) e lasciare order_items con unit_price.

  3. Portare in 3NF: estrarre departments se dept_name dipende da dept_id.

6. Esempio reale completo: E-commerce (semplificato)

Pubblicità

Domini / entità principali

  • customers (clienti)

  • products (prodotti)

  • orders (ordini)

  • order_items (righe ordine) — gestisce N:M tra orders e products

  • categories (categorie prodotto)

  • product_categories (associazione prodotto-categoria) — N:M

Regole:

  • Tutte le tabelle hanno id surrogate (BIGINT AUTO_INCREMENT o UUID).

  • orders ha FK su customers.

  • order_items ha FK su orders e products.

  • Quando un cliente viene cancellato, business decision: generalmente NON fare cascade delete sugli ordini (meglio RESTRICT o SET NULL per tracciare storico).

 Principio generale: i dati “storici” non si cancellano mai!

Quando un’entità è coinvolta in transazioni o registrazioni storiche (es. ordini, fatture, iscrizioni, ticket, log), non deve mai essere cancellata fisicamente dal database.
Questo vale, per esempio, per:

  • Prodotti già venduti (anche se non più disponibili),

  • Clienti che hanno effettuato ordini (anche se non più attivi),

  • Corsi in cui uno studente si è iscritto,

  • Dipendenti che hanno partecipato a progetti chiusi.

Il motivo è semplice:

  • Gli order_items rappresentano fatti storici.

  • Se elimini la riga di products, la FK di order_items diventerebbe “orfana” o costringerebbe il database a cancellare anche le righe ordine (in caso di ON DELETE CASCADE), distruggendo la tracciabilità.

👉 In un database progettato per integrità e auditabilità, i dati storici non si cancellano, si “disattivano”.

Soluzione corretta: Soft Delete o flag di stato

Invece di cancellare una riga da products, si gestisce la disattivazione con un campo, per esempio:

ALTER TABLE products
ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

Quando un prodotto non è più disponibile, imposti:

UPDATE products
SET is_active = FALSE
WHERE id = 123;

Questo approccio ha diversi vantaggi:

Vantaggio Descrizione
Integrità storica Gli ordini storici rimangono coerenti e interrogabili.
Audit e tracciabilità Puoi sempre sapere cosa è stato venduto, anche se il prodotto non è più in vendita.
Sicurezza dati Nessuna cancellazione fisica involontaria.
Compatibilità con FK Nessun problema di ON DELETE o di chiavi orfane.

Opzionale: Archiviazione o Versionamento

In sistemi più complessi (ERP, e-commerce enterprise, gestionali) si adotta un approccio di versionamento o archiviazione storica:

  1. Versionamento
    Quando cambia un prodotto (es. prezzo, nome, descrizione), non si aggiornano direttamente i valori, ma si crea una nuova versione con un timestamp valido:

    products_history
    (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT REFERENCES products(id),
    name TEXT,
    price NUMERIC(10,2),
    valid_from TIMESTAMP,
    valid_to TIMESTAMP
    )

    Così puoi sempre risalire alle condizioni del prodotto al momento della vendita.

  2. Archiviazione logica
    Per alleggerire la tabella principale, si può spostare il record in una tabella archived_products, mantenendo la FK valida:

    INSERT INTO archived_products SELECT * FROM products WHERE is_active = FALSE;
    DELETE FROM products WHERE is_active = FALSE;

    Ma questo si fa solo se si garantisce che order_items.product_id punti comunque a una copia coerente dei dati.

Quindi, nel nostro schema (aggiornamento alla sezione “products”):

products

CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE,
name TEXT NOT NULL,
description TEXT,
price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE, -- ✅ nuovo campo per soft delete
created_at TIMESTAMP NOT NULL DEFAULT now()
);

order_items

CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT, -- ✅ mantieni storicità
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
total_line NUMERIC(12,2) NOT NULL
);

🔒 ON DELETE RESTRICT impedisce la cancellazione di un prodotto se esistono righe order_items collegate, evitando inconsistenze storiche.

Best practice: mai cancellare “fatti” o “istanze storiche”

Puoi memorizzarlo come regola di modellazione:

Regola aurea:

  • Le entità che rappresentano “stati correnti” (es. un carrello temporaneo) possono essere cancellate.

  • Le entità che rappresentano “fatti avvenuti” (ordini, vendite, fatture, partecipazioni) non vanno mai cancellate.

  • Le entità collegate a “fatti avvenuti” (prodotti, clienti, account) non vanno cancellate, ma eventualmente disattivate.

Alternative avanzate: Soft Delete con timestamp

In sistemi enterprise o GDPR-friendly si preferisce usare:

ALTER TABLE products
ADD COLUMN deleted_at TIMESTAMP NULL;

Quando il prodotto viene disattivato:

UPDATE products
SET deleted_at = now()
WHERE id = 123;

E le query operative filtrano con:

SELECT * FROM products WHERE deleted_at IS NULL;

Esempio completo: comportamento previsto

Azione Effetto
Cliente acquista un prodotto Viene creato order_items con product_id, unit_price e total_line salvati.
Prodotto viene ritirato dal catalogo is_active → FALSE (non appare più nelle ricerche), ma resta nel DB.
Query storica degli ordini Tutti gli ordini mostrano ancora il nome e il prezzo corretto (possiamo anche storicizzare unit_price).
Tentativo di DELETE FROM products Fallisce se ci sono order_items → grazie a ON DELETE RESTRICT.

Peculiarità e Spunti Applicativi

Questi due blocchi di CREATE TABLE sono un caso di studio perfetto per comprendere come le scelte di progettazione influenzano il comportamento e l’affidabilità di un database relazionale. Analizziamo i tre motivi principali per cui questo schema è interessante e realistico.

Forse potrebbe interessarti anche:  Gestione Scorte Integrata: Esercizio Pratico su ABC, ESP e Modello di Poisson per Manager

1️⃣ Il contrasto tra CASCADE e RESTRICT

order_id ... ON DELETE CASCADE
product_id ... ON DELETE RESTRICT

Questa coppia di vincoli mostra due filosofie opposte ma complementari:

  • CASCADE (per order_id):
    Se un ordine viene cancellato, tutte le sue righe in order_items vengono eliminate automaticamente.
    Ha perfettamente senso: le righe dell’ordine non hanno significato senza l’ordine a cui appartengono. Sono parte dello stesso “fatto” e condividono la stessa vita nel database.

  • RESTRICT (per product_id):
    Qui la logica cambia completamente. Se provi a eliminare un prodotto che compare in uno qualunque degli order_items, il database rifiuterà l’operazione.
    Questa è una protezione attiva dell’integrità storica. A livello applicativo, puoi marcare il prodotto come non disponibile (is_active = false), ma non devi mai cancellarlo fisicamente.

    🔒 È la regola del “soft delete” applicato a livello logico, rinforzato dal database.
    L’applicazione decide cosa mostrare, il DB decide cosa non si può distruggere.

2️⃣ La Denormalizzazione Intelligente (unit_price)

Un principiante potrebbe chiedere:

“Perché mettere unit_price in order_items se il prezzo è già nella tabella products?”

Domanda giustissima — ma la risposta è illuminante:
Questa non è una violazione della normalizzazione, è una denormalizzazione consapevole.

Il prezzo di un prodotto può cambiare nel tempo (sconti, saldi, variazioni stagionali).
Quando un cliente effettua un acquisto, bisogna “fotografare” il prezzo di quel momento.

  • products.price → rappresenta il prezzo attuale.

  • order_items.unit_price → rappresenta il prezzo storico al momento della vendita.

Senza questo campo, ogni modifica ai prezzi renderebbe incoerenti le vecchie fatture:
tutti gli ordini cambierebbero prezzo magicamente ogni volta che aggiorni il catalogo.
Un errore concettuale e contabile gravissimo.

💡 Denormalizzare consapevolmente significa sapere quando vale la pena sacrificare un po’ di purezza teorica per garantire coerenza storica e correttezza dei dati.

3️⃣ La gestione del Soft Delete (is_active)

L’uso combinato di is_active e ON DELETE RESTRICT crea una doppia protezione:

  • is_active (livello applicativo):
    L’applicazione filtra e mostra solo i prodotti attivi.
    Nessun utente vedrà prodotti obsoleti o ritirati.

  • ON DELETE RESTRICT (livello database):
    Impedisce che qualcuno, magari per errore o tramite uno script, cancelli fisicamente il prodotto.
    Così il database rimane coerente e lo storico degli ordini intatto.

È una strategia “cintura e bretelle”:
anche se il livello applicativo fallisce, il DB garantisce che la cronologia delle transazioni resti integra.

In sintesi

Questo mini-scenario orders / order_items / products riassume le regole auree di un buon schema relazionale:

Principio Applicazione pratica
Integrità referenziale Vincoli CASCADE e RESTRICT usati con criterio
Storicitazione dei dati Denormalizzazione consapevole (unit_price)
Protezione logica e fisica Combinazione is_active + ON DELETE RESTRICT
Evolvibilità Tutte le entità hanno ID surrogato come PK
Coerenza applicativa Nessuna cancellazione di dati storici

Errori Comuni nella Progettazione di un Database Relazionale

Ogni database nasce con le migliori intenzioni… ma basta una piccola disattenzione nella progettazione per creare problemi enormi nel tempo: dati incoerenti, query lente, cancellazioni accidentali o difficoltà di manutenzione.
Ecco gli errori più frequenti — e come evitarli.

1️⃣ Usare chiavi naturali invece di chiavi surrogate

❌ Errore: usare campi “logici” come chiave primaria — ad esempio email, codice_fiscale, numero_ordine.

All’inizio sembra comodo (“tanto è univoco!”), ma presto diventa un incubo:

  • L’utente può cambiare email.

  • Un ordine può essere importato con un nuovo numero.

  • Un codice fiscale può essere errato o duplicato per test.

✅ Soluzione:
Usa sempre chiavi surrogate, di solito un campo id auto-increment (INT AUTO_INCREMENT o SERIAL).
Le chiavi naturali restano comunque uniche, ma diventano vincoli secondari (UNIQUE), non la PK.

2️⃣ Usare DELETE CASCADE ovunque

❌ Errore: impostare ON DELETE CASCADE in tutte le relazioni “per comodità”.

Questo è pericoloso: potresti cancellare un ordine e perdere anche i dati storici collegati a prodotti o utenti.
Il CASCADE va usato solo dove una relazione è di dipendenza forte (es. order_items dipende da orders).

✅ Soluzione:
Usa CASCADE solo per tabelle che non hanno significato autonomo.
Per tutto il resto, preferisci RESTRICT o SET NULL, e usa soft delete (is_active, deleted_at, ecc.) a livello applicativo.

Forse potrebbe interessarti anche:  SQLite e Python: Guida Pratica dal JOIN ai Trigger (con Esercizi)

3️⃣ Ignorare la normalizzazione (o applicarla in modo cieco)

❌ Errore 1: mettere tutti i dati in un’unica tabella (es. utenti e ordini insieme).
❌ Errore 2: spingersi alla 5ª forma normale “a tutti i costi”.

Entrambi gli estremi sono dannosi:

  • Se non normalizzi, i dati si duplicano e diventano incoerenti.

  • Se normalizzi troppo, il modello diventa inutilmente complesso e le query rallentano.

✅ Soluzione:
Normalizza fino alla 3ª forma normale (3NF).
Dopo di che valuta caso per caso: a volte una denormalizzazione controllata (come unit_price) è la scelta migliore.

4️⃣ Dimenticare la cronologia dei dati

❌ Errore: aggiornare direttamente i valori “vivi” (es. cambiare il prezzo di un prodotto senza storicizzare).

Così si perde la traccia temporale dei cambiamenti, e le analisi diventano impossibili.

✅ Soluzione:
Aggiungi sempre almeno uno di questi elementi:

  • created_at, updated_at, deleted_at

  • Campi storici (es. unit_price, status_at_order_time)

  • Tabelle di audit o log (order_history, price_history)

5️⃣ Non distinguere tra dati attivi e dati storici

❌ Errore: cancellare fisicamente record “vecchi”.

Mai cancellare i dati legati a eventi passati: ordini, transazioni, contratti.
La storia è parte del valore informativo del database.

✅ Soluzione:
Implementa un soft delete o un flag is_active.
Il record resta nel database ma è escluso dalle query operative.

6️⃣ Non documentare le regole di business nel modello

❌ Errore: pensare che lo schema si spieghi da solo.

Dopo pochi mesi, nessuno ricorderà perché hai usato RESTRICT invece di CASCADE, o cosa rappresenta davvero unit_price.

✅ Soluzione:
Documenta sempre nel codice SQL o nel manuale tecnico:

  • I motivi delle scelte di chiavi e vincoli

  • Le logiche di aggiornamento e cancellazione

  • Le relazioni forti e deboli tra entità

Un buon schema documentato vale quanto un buon codice commentato.

Esercizio Pratico Finale: “Progetta il Database di una Libreria Online”

Obiettivo

Creare da zero un modello relazionale per una libreria online, capace di gestire:

  • Catalogo libri e autori

  • Clienti e ordini

  • Prezzi storici e disponibilità

  • Conservazione dello storico (nessuna cancellazione reale)

 1. Identificazione delle Entità

Entità principali (forti):

  • books — rappresenta ogni libro nel catalogo

  • authors — rappresenta gli autori

  • customers — rappresenta i clienti registrati

  • orders — rappresenta gli ordini effettuati

Entità derivate (deboli o di relazione):

  • order_items — connette un ordine ai libri acquistati

  • book_authors — connette i libri ai loro autori (relazione molti-a-molti)

 2. Scelta delle Chiavi

Tipo di chiave Regola Esempio
Primaria (PK) Sempre surrogata (id auto-increment) book_id, author_id, order_id, customer_id
Esterna (FK) Sempre referenzia PK di un’altra tabella order_items.order_id → orders.order_id
Univoca (UNIQUE) Per chiavi naturali (es. ISBN, email) books.isbn o customers.email
Soft delete Campo booleano o timestamp is_active o deleted_at

3. Modellazione delle Tabelle (Schema SQL)

🔸 Tabella authors

CREATE TABLE authors (
author_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
bio TEXT,
is_active BOOLEAN DEFAULT TRUE
);

🔸 Tabella books

CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
isbn VARCHAR(13) UNIQUE NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔸 Tabella di relazione book_authors (molti-a-molti)

CREATE TABLE book_authors (
book_id INT NOT NULL,
author_id INT NOT NULL,
PRIMARY KEY (book_id, author_id),
FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE,
FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE RESTRICT
);

✳️ Il libro può essere cancellato (insieme alle relazioni),
ma l’autore rimane: ON DELETE RESTRICT protegge l’integrità biografica.

🔸 Tabella customers

CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);

🔸 Tabella orders

CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
total DECIMAL(10,2) DEFAULT 0.00,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

✅ CASCADE ha senso: se un cliente viene cancellato (evento raro, ma possibile),
i suoi ordini vengono rimossi insieme. Gli ordini dipendono dall’esistenza del cliente.

🔸 Tabella order_items

CREATE TABLE order_items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
book_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL, -- Prezzo storico del libro
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE RESTRICT
);

📘 Come nel caso “products / order_items”:

  • order_id → CASCADE (dipendenza forte)

  • book_id → RESTRICT (storico protetto)

  • unit_price → denormalizzazione intelligente per preservare il prezzo dell’epoca

 4. Analisi delle Scelte

Aspetto Decisione progettuale Motivazione
Chiavi Tutte surrogate (ID) Uniformità, semplicità, indipendenza logica
Integrità referenziale CASCADE per ordini, RESTRICT per libri Coerenza storica
Soft delete is_active in tabelle principali Non si perde mai lo storico
Denormalizzazione unit_price in order_items Congela i prezzi
Molti-a-molti book_authors Correttezza semantica
Audit logico created_at Tracciabilità temporale

Questo esercizio racchiude in miniatura tutti i principi fondamentali della modellazione relazionale:

  • Entità forti e deboli ben distinte

  • Chiavi surrogate e FK coerenti

  • Denormalizzazione ragionata dove serve

  • Integrità storica garantita da RESTRICT e soft delete

  • Scalabilità e leggibilità semantica del modello

💬 “Il database non è solo un contenitore di dati, ma la mappa logica di un sistema reale.”
Quando lo progetti bene, non serve ricordare le regole: le applica da solo.

In Conclusione

Progettare un database relazionale non è solo disegnare tabelle:
è tradurre regole del mondo reale in vincoli e comportamenti coerenti nel tempo.
Ogni scelta — chiavi, relazioni, cancellazioni, cronologia — è una decisione semantica, non solo tecnica.

“Un database ben progettato non è quello che funziona subito,
ma quello che continua a funzionare dopo cinque anni di cambiamenti.”

Pubblicità

Related Posts: