Corso MariaDB / Normalizzare i dati e progettare un database di ordini
La normalizzazione esamina quali chiavi determinano i singoli fatti e separa le tabelle per ridurre informazioni correnti ripetute e anomalie di aggiornamento. Distinguere gli attributi attuali di clienti e prodotti dai fatti dell'acquisto permette di cambiare un nome in un solo punto, conservando il prezzo unitario storico dell'ordine.
Individuare i problemi causati da fatti ripetuti
Copiare il nome corrente del cliente in ogni dettaglio ordine può lasciare più nomi per lo stesso cliente dopo un aggiornamento parziale. Eliminare l'ultimo dettaglio può cancellare l'unica informazione sul cliente; inserire un cliente nuovo può risultare impossibile prima del suo primo ordine. Separare il soggetto di ogni fatto riduce queste anomalie di aggiornamento, eliminazione e inserimento.
Un'anomalia è una situazione nella quale un'operazione ordinaria causa una perdita di informazioni o un'incoerenza indesiderata. La guida alle relazioni e alle chiavi esterne spiega i collegamenti da definire concretamente, anziché limitarsi a disegnare tabelle separate.
| Fatto | Chiave determinante | Tabella |
|---|---|---|
| Nome e città attuali del cliente | customer_id | customers |
| Nome e prezzo di vendita attuali del prodotto | product_id | products |
| Cliente, momento e stato dell'ordine | order_id | orders |
| Quantità e prezzo unitario dell'acquisto | order_id + product_id | items |
Il significato di un valore atomico dipende dagli usi previsti. Un elenco di prodotti separati da virgole non è adatto se occorre cercare o sommare la quantità di ogni prodotto. Questo non significa che qualsiasi testo o documento JSON debba essere suddiviso senza considerare i requisiti.
La spiegazione MariaDB della terza forma normale tratta le dipendenze transitive; il riferimento sulle chiavi esterne illustra i vincoli applicabili alle relazioni.
Creare dati separati e una tabella piatta di confronto
Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega salvataggio ed esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello effettivo ed eseguire SQL nel client MariaDB.
Prima di creare c37_customers, c37_products, c37_orders e c37_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono già, esaminarne definizioni e righe. Usare soltanto oggetti di prova eliminabili e conservare le tabelle applicative. Il blocco seguente prepara l'esempio completo.
USE shopcourse;
CREATE TABLE c37_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c37_order_customer FOREIGN KEY (customer_id)
REFERENCES c37_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c37_item_order FOREIGN KEY (order_id)
REFERENCES c37_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c37_item_product FOREIGN KEY (product_id)
REFERENCES c37_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c37_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c37_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c37_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c37_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Il codice conserva nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Mantenere i testi UTF-8 e usare gli ID per confrontare i risultati.
Accertarsi che non esista neppure c37_flat. CREATE TABLE AS SELECT crea una copia di confronto per osservare la ripetizione. Non copia automaticamente tutte le chiavi e tutti i vincoli della fonte e non costituisce quindi un backup completo. Verificare che il motore della tabella di confronto supporti le transazioni prima di affidarsi a ROLLBACK nella prova.
CREATE TABLE c37_flat AS
SELECT o.order_id,i.product_id,c.customer_id,c.customer_name,
p.product_name,i.quantity,i.unit_price
FROM c37_orders o JOIN c37_customers c ON c.customer_id=o.customer_id
JOIN c37_items i ON i.order_id=o.order_id
JOIN c37_products p ON p.product_id=i.product_id;
SELECT customer_id,customer_name,COUNT(*) AS repeated_rows
FROM c37_flat GROUP BY customer_id,customer_name ORDER BY customer_id;
START TRANSACTION;
UPDATE c37_flat SET customer_name='김민수 변경' WHERE order_id=101;
SELECT DISTINCT customer_id,customer_name FROM c37_flat
WHERE customer_id=1 ORDER BY customer_name;
ROLLBACK;
Il nome del cliente 1 compare in tre righe di dettaglio. Cambiare soltanto la copia relativa all'ordine 101 lascia contemporaneamente il nome originale e quello modificato, 김민수 변경, per lo stesso cliente. ROLLBACK annulla la modifica nella tabella di confronto; questo blocco non cambia la tabella separata dei clienti.
La copia c37_flat non è neppure una vista: modificare la tabella clienti dopo la creazione della copia non aggiorna automaticamente le righe copiate. La guida a VIEW distingue la definizione di una query dai dati memorizzati separatamente.
Esaminare dipendenze parziali e transitive
Nella chiave composta da order_id e product_id, la data dell'ordine è determinata dal solo order_id. Memorizzarla in ogni dettaglio ripete un fatto dipendente soltanto da una parte della chiave. Collocarla in orders separa i fatti dell'ordine da quantità e prezzo di acquisto della singola riga.
L'ordine determina il cliente e il cliente determina il proprio nome attuale. Ripetere il nome corrente in ogni ordine distribuisce l'aggiornamento in molti punti. Il riferimento customer_id offre invece un'unica fonte del nome attuale. Queste domande sulle dipendenze danno un significato concreto alla verifica della seconda e della terza forma normale.
La spiegazione MariaDB della seconda forma normale tratta l'eliminazione delle dipendenze parziali dopo la prima forma normale. La terza esamina poi le dipendenze transitive tra attributi non chiave. Partire dal significato dei dati e dalle regole effettive delle chiavi rende motivata la separazione.
Conservare il prezzo storico come fatto distinto
Non eliminare items.unit_price soltanto perché oggi coincide con products.price. Il primo registra il prezzo dell'acquisto, il secondo il prezzo di vendita corrente. Rappresentano fatti relativi a momenti diversi: il totale di un vecchio ordine richiede il prezzo della transazione anche quando cambia il catalogo.
Nell'esempio, due unità del prodotto 1 al prezzo di acquisto 3500 continuano a valere 7000 anche portando il prezzo di catalogo a 4000. Un aggiornamento del catalogo non dovrebbe riscrivere il prezzo della transazione da conservare. Distinguere valori che esprimono davvero lo stesso fatto da valori che oggi coincidono.
Anche indirizzo di spedizione o nome visualizzato possono richiedere una registrazione al momento dell'acquisto. Definire significato, momento della registrazione e regole di modifica per gli attributi correnti e per la documentazione storica. Conservare una storia necessaria è diverso dal duplicare fatti correnti senza regole di manutenzione.
Applicare chiavi e condizioni del dominio
Creare quattro tabelle non basta a rifiutare riferimenti errati. Definire PRIMARY KEY, eventuali UNIQUE, NOT NULL e chiavi esterne; verificare poi riferimenti a genitori mancanti e chiavi duplicate. Un cliente senza ordini può essere memorizzato separatamente e la cancellazione dell'ultimo dettaglio non deve eliminare la sua identità.
Questo modello semplice riunisce un prodotto in una sola riga per ordine. Varianti del prodotto, lotti o spedizioni parziali possono richiedere un ID di riga distinto e chiavi aggiuntive. Adeguare il significato della riga ai requisiti invece di considerare l'esempio una soluzione completa per ogni sistema di ordini.
Misurare i requisiti prima di denormalizzare
La normalizzazione fornisce una base per la coerenza degli aggiornamenti. La velocità dipende da query effettive, indici e volume dei dati. Se serve una tabella riepilogativa per i rapporti, stabilire la fonte autorevole, le regole di aggiornamento e il recupero dagli errori. Un numero elevato di JOIN, da solo, non giustifica la ripetizione di informazioni correnti senza una politica di manutenzione.
Prima di ripetere l'esercizio, controllare database selezionato, SHOW CREATE TABLE e contenuto di tutti gli oggetti, salvando ciò che va conservato. Eliminare soltanto gli oggetti c37_ di questo articolo: prima la copia di confronto e le tabelle figlie, poi quelle di origine referenziate. DROP TABLE elimina struttura e dati e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.
DROP TABLE shopcourse.c37_flat;
DROP TABLE shopcourse.c37_items;
DROP TABLE shopcourse.c37_orders;
DROP TABLE shopcourse.c37_products;
DROP TABLE shopcourse.c37_customers;




