Cours MariaDB / Normaliser les tables et préserver les faits historiques

La normalisation consiste à déterminer quelles clés identifient quels faits, puis à répartir ces faits pour réduire les répétitions et les anomalies de modification. Distinguer les informations actuelles d’un client ou d’un produit des faits enregistrés lors d’une commande permet de modifier un nom à un seul endroit tout en conservant le prix historique d’achat.

Repérer les anomalies dues à la répétition d’un fait

Si le nom actuel d’un client est copié sur chaque ligne de commande, une modification partielle peut laisser deux noms pour le même client. Supprimer sa dernière ligne de commande peut également supprimer la seule information conservée à son sujet. Et un nouveau client sans commande peut n’avoir aucun emplacement où être enregistré. Ce sont des anomalies de mise à jour, de suppression et d’insertion : la structure mélange plusieurs sujets dans la même ligne.

Le guide des relations, clés primaires et clés étrangères montre comment exprimer les liens entre ces sujets. Le tableau suivant indique les dépendances retenues dans cet exercice. Une dépendance signifie qu’une valeur de clé détermine le fait concerné selon les règles métier choisies.

Fait Clé qui le détermine Emplacement
Nom actuel et ville du client customer_id customers
Nom actuel et prix de vente du produit product_id products
Client, horaire et statut d’une commande order_id orders
Quantité et prix unitaire à l’achat order_id + product_id items

Le caractère « atomique » d’une valeur dépend du domaine et de l’usage. Pour rechercher les produits et calculer leurs quantités séparément, une liste de produits séparés par des virgules dans une seule colonne est mal adaptée. Cela ne signifie pas qu’il faut toujours décomposer tous les textes ou tous les documents JSON : commencez par définir les opérations et les contraintes nécessaires.

Créer les tables normalisées et une copie de comparaison

Connectez-vous au serveur d’apprentissage avec course_user dans shopcourse. Le compte doit pouvoir créer, remplir, lire, modifier et supprimer les objets de cet exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 explique la connexion. Créez un dossier d’exercice, puis enregistrez le bloc complet ci-dessous en UTF-8, par exemple dans C:/mariadb-lab/c37-normalisation.sql. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c37-normalisation.sql; avec le chemin réel. Exécutez ces instructions SQL dans la connexion au serveur, pas dans l’invite du système.

Vérifiez d’abord le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms avec SHOW TABLES;. Les tables c37_customers, c37_products, c37_orders, c37_items et c37_flat appartiennent à cet exercice. Si elles existent déjà, examinez leurs définitions et leurs lignes avant de décider si elles peuvent être nettoyées. Ne remplacez pas des données à conserver.

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);

Les tables contiennent quatre clients, quatre produits, quatre commandes et six détails. Les chaînes coréennes sont conservées pour reproduire les résultats entre langues. Les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe ; les clients 1 et 3 ont la ville Séoul, les clients 2 et 4 Busan et Daejeon. Conservez les littéraux UTF-8 et suivez les identifiants.

Le bloc suivant crée c37_flat par CREATE TABLE AS SELECT. Cette copie sert à observer les répétitions : elle ne reproduit pas automatiquement toutes les clés et contraintes des sources et ne constitue pas une sauvegarde complète. Contrôlez @@SESSION.default_storage_engine avant sa création : le moteur attendu pour cet essai d’annulation est InnoDB. Après la création et avant START TRANSACTION, vérifiez-le avec SHOW CREATE TABLE c37_flat;. Si la copie utilise un moteur non transactionnel, arrêtez-vous plutôt que de supposer que ROLLBACK annulera ses changements.

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;

Le premier SELECT regroupe les répétitions par client :

customer_id customer_name repeated_rows
1 김민수 3
2 이서연 2
3 박지우 1

Le client 1 apparaît sur trois détails : deux dans la commande 101 et un dans la commande 102. L’UPDATE modifie seulement les copies de son nom appartenant à la commande 101. Le SELECT DISTINCT montre alors le nom initial 김민수 et le nom modifié 김민수 변경 pour le même identifiant. Le dernier ROLLBACK annule cette modification de la copie ; la table normalisée des clients n’a pas été modifiée. Le client 4, sans commande, n’apparaît pas dans cette copie construite par jointures internes, alors qu’il existe bien dans customers.

Suivre les dépendances partielles et transitives

Dans les détails, la clé composée est le couple order_id et product_id. L’horaire de commande dépend de order_id seul, pas du produit. Le copier dans chaque détail répéterait donc un fait qui dépend seulement d’une partie de la clé. Le placer dans orders laisse aux détails les faits du couple, comme la quantité et le prix unitaire de la transaction.

La deuxième forme normale traite notamment ces dépendances partielles, après la première forme normale. Ne concluez pas qu’une table est correctement conçue simplement parce qu’elle possède une clé primaire : examinez ce qui détermine chaque attribut selon les règles du domaine.

Pour une commande, order_id détermine customer_id ; customer_id détermine ensuite le nom actuel du client. Stocker ce nom actuel dans chaque commande impose de répercuter un changement dans plusieurs lignes. Une référence vers customers par customer_id conserve une seule source pour le nom actuel. La troisième forme normale prolonge l’analyse des dépendances, en particulier des dépendances transitives entre attributs non clés. Ces exemples servent à raisonner sur les faits, sans prétendre démontrer toutes les formes normales possibles d’un modèle complet.

Conserver le prix historique comme un fait distinct

Ne supprimez pas items.unit_price parce que sa valeur égale actuellement products.price. Le premier représente le prix unitaire enregistré lors de l’achat ; le second représente le prix de vente actuel. Si le prix du carnet change dans products après la commande, le montant historique doit toujours utiliser le prix enregistré dans items.

Dans les données d’exercice, la commande 101 comporte deux carnets à 3500 et trois stylos à 1500, soit 11500. Un nouveau prix de catalogue n’est pas une raison de recalculer cette ancienne commande avec la nouvelle valeur. Le nom et la politique de mise à jour de chaque colonne doivent exprimer le moment auquel le fait se rapporte.

La même distinction vaut pour une adresse de livraison ou un nom affiché sur un justificatif : si les besoins exigent une preuve de l’état au moment de la commande, un instantané explicite peut être conservé. Séparez cet historique de l’attribut actuel, et définissez sa date de capture et sa politique de correction. Préserver un fait historique utile diffère de copier plusieurs fois un fait actuel sans règle de maintenance.

Faire appliquer les clés et les règles métier

Créer quatre tables ne suffit pas à empêcher les références invalides. Définissez réellement les clés primaires, les contraintes UNIQUE et NOT NULL nécessaires et les clés étrangères. Dans une base d’essai, vérifiez qu’un parent absent ou une clé déjà utilisée provoque un refus, sans désactiver les contrôles pour faire passer le test. La documentation des clés étrangères MariaDB précise leurs conditions d’application.

Le couple order_id/product_id traduit ici une règle simplifiée : un produit occupe une seule ligne par commande. Si vous devez distinguer des options, des lots ou des expéditions partielles, cette règle peut être insuffisante. Un identifiant de détail et d’autres clés peuvent alors être nécessaires. Définissez d’abord ce qu’une ligne représente, puis les contraintes qui interdisent les états incohérents.

Mesurer avant une dénormalisation volontaire et nettoyer l’essai

La normalisation fournit une base de cohérence pour les modifications. La vitesse d’une requête dépend aussi des index, des volumes et des opérations réellement exécutées ; elle doit être évaluée séparément. Si une table de synthèse devient utile pour les rapports, définissez sa source de référence, sa fréquence de mise à jour et sa réparation après erreur. La présence de plusieurs JOIN ne justifie pas à elle seule de copier les mêmes informations actuelles partout.

Avant de recommencer, contrôlez la base, les définitions et les lignes réelles, puis sauvegardez tout élément à conserver. Supprimez seulement les objets jetables de cet exercice : la copie de comparaison, puis les enfants avant les tables référencées. DROP TABLE supprime structure et données et n’est pas annulé par un ROLLBACK ordinaire ; gardez ce nettoyage en dehors de l’essai d’annulation de la modification.

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;