data / sql
Normalisation : 1NF, 2NF, 3NF
Explication
Ce que vous allez apprendre
- Reconnaître une table qui viole la 1NF (colonnes répétées, valeurs multiples)
- Identifier une dépendance partielle et corriger vers la 2NF
- Identifier une dépendance transitive et corriger vers la 3NF
- Comprendre le compromis entre normalisation (cohérence) et dénormalisation (performance)
- Décider consciemment quand dupliquer une donnée est acceptable
Dans quel contexte ?
Un développeur reprend une base existante où la table commandes contient des colonnes produit_1, produit_2, produit_3 pour stocker jusqu'à trois produits par commande. Le jour où un client en commande un quatrième, toute la structure de la table doit être revue. Cette leçon explique comment repenser ce schéma avec les règles de normalisation, pour éviter ce genre de blocage structurel.
D'abord, un exemple concret du problème
Imagine une seule grande table qui stocke à la fois les infos client ET le nom de chaque produit acheté, répété à chaque commande. Si un produit change de nom, il faut le corriger dans des milliers de lignes.
Le risque que ça fait courir
En oublier une seule ligne à corriger crée une incohérence : deux commandes du même produit affichent maintenant deux noms différents. Ces incohérences s'appellent des "anomalies de mise à jour".
La normalisation comme méthode générale
La normalisation organise les tables pour que chaque information ne soit stockée qu'à un seul endroit, éliminant ce risque à la racine. Elle se décompose en niveaux progressifs, chacun posant une question précise.
Premier niveau : la 1NF
Chaque cellule contient-elle une seule valeur atomique, sans liste ni répétition de colonnes comme "produit_1, produit_2, produit_3" ? Si oui, la table respecte la 1NF.
Deuxième niveau : la 2NF
Pour les tables à clé composite, une colonne dépend-elle de la clé ENTIÈRE, ou seulement d'une PARTIE de cette clé ? Si une colonne ne dépend que d'une partie, elle doit être extraite dans sa propre table.
Troisième niveau : la 3NF
Une colonne dépend-elle directement de la clé, ou indirectement via une autre colonne non-clé ? Une ville qui dépend en réalité du code postal, pas de l'identifiant client, est une "dépendance transitive" à corriger.
| Niveau | Question posée | Défaut typique corrigé |
|---|---|---|
| 1NF | Chaque cellule est-elle atomique ? | colonnes produit_1, produit_2, produit_3 |
| 2NF | Chaque colonne dépend-elle de TOUTE la clé composite ? | produit_nom qui ne dépend que de produit_id |
| 3NF | Chaque colonne dépend-elle directement de la clé ? | ville qui dépend en fait de code_postal |
Piège fréquent
Stocker produit_nom directement dans lignes_commande (plutôt qu'une simple référence produit_id) semble pratique au début, mais un changement de nom de produit ne se répercute alors sur aucune commande passée : les anciennes commandes affichent l'ancien nom, les nouvelles le nouveau, sans qu'on sache toujours lequel est correct.
Mais normaliser n'est pas toujours la fin de l'histoire
Une base parfaitement normalisée minimise la redondance, mais demande souvent plus de jointures pour reconstituer une information complète, ce qui a un coût en lecture.
Un compromis assumé : la dénormalisation
Dupliquer volontairement une donnée peut accélérer des rapports très fréquents, à condition d'assumer le risque d'incohérence et de le gérer explicitement, par exemple via un trigger. Cette leçon fait le pont entre la conception des tables et les questions de performance abordées dans les leçons suivantes.
Bonne pratique
Normalise toujours par défaut lors de la conception initiale d'un schéma. Ne dénormalise que face à un problème de performance mesuré et documenté (par exemple via EXPLAIN ANALYZE, vu en leçon 16), jamais par anticipation ou par confort.
Commandes & code
Normalisation
-- Table NON normalisée : viole la 1NF (colonnes répétées / valeurs multiples)
CREATE TABLE commandes_mauvais_design (
id INTEGER PRIMARY KEY,
client_nom VARCHAR(100),
produit_1 VARCHAR(100), produit_2 VARCHAR(100), produit_3 VARCHAR(100), -- répétition !
tags VARCHAR(500) -- "urgent,fragile,cadeau" -- valeurs multiples dans une colonne
);
-- 1NF : chaque colonne est atomique, pas de groupes répétitifs
CREATE TABLE commandes_1nf (
id INTEGER PRIMARY KEY,
client_nom VARCHAR(100)
);
CREATE TABLE lignes_commande_1nf (
id INTEGER PRIMARY KEY,
commande_id INTEGER REFERENCES commandes_1nf(id),
produit VARCHAR(100) -- une ligne par produit, plus de colonnes répétées
);
CREATE TABLE commande_tags (
commande_id INTEGER REFERENCES commandes_1nf(id),
tag VARCHAR(50),
PRIMARY KEY (commande_id, tag)
);
-- 2NF : élimine les dépendances partielles (nécessite une clé composite pour s'appliquer)
-- Mauvais : dans (commande_id, produit_id), produit_nom ne dépend QUE de produit_id
CREATE TABLE lignes_commande_1nf_seule (
commande_id INTEGER,
produit_id INTEGER,
produit_nom VARCHAR(100), -- dépendance partielle : ne dépend que de produit_id
quantite INTEGER,
PRIMARY KEY (commande_id, produit_id)
);
-- Correction 2NF : extraire produit_nom dans sa propre table
CREATE TABLE produits_2nf (
id INTEGER PRIMARY KEY,
nom VARCHAR(100)
);
CREATE TABLE lignes_commande_2nf (
commande_id INTEGER,
produit_id INTEGER REFERENCES produits_2nf(id),
quantite INTEGER,
PRIMARY KEY (commande_id, produit_id)
);
-- 3NF : élimine les dépendances transitives (colonne dépendant d'une colonne non-clé)
-- Mauvais : code_postal -> ville est une dépendance transitive (via code_postal, pas via id)
CREATE TABLE clients_avant_3nf (
id INTEGER PRIMARY KEY,
nom VARCHAR(100),
code_postal VARCHAR(10),
ville VARCHAR(100) -- dépend de code_postal, pas directement de id
);
-- Correction 3NF : extraire la relation code_postal -> ville
CREATE TABLE codes_postaux (
code_postal VARCHAR(10) PRIMARY KEY,
ville VARCHAR(100)
);
CREATE TABLE clients_3nf (
id INTEGER PRIMARY KEY,
nom VARCHAR(100),
code_postal VARCHAR(10) REFERENCES codes_postaux(code_postal)
);
-- Dénormalisation volontaire (contrôlée) pour la performance en lecture
-- On duplique client_nom pour éviter un JOIN sur des rapports très fréquents
CREATE TABLE commandes_denormalisee (
id INTEGER PRIMARY KEY,
client_id INTEGER REFERENCES clients_3nf(id),
client_nom_snapshot VARCHAR(100), -- copie assumée, mise à jour par trigger si besoin
montant DECIMAL(10, 2)
);Résumé
- 1NF : colonnes atomiques, pas de listes/répétitions dans une cellule.
- 2NF : 1NF + pas de dépendance partielle envers une PARTIE d'une clé composite.
- 3NF : 2NF + pas de dépendance transitive envers une colonne non-clé.
- Normaliser réduit la redondance et les anomalies de mise à jour ; dénormaliser (volontairement) peut accélérer la lecture au prix de la cohérence facile.
Exercices pratiques
Mission : sauver un schéma de commandes bloqué au 4e produit
Objectif : Reconcevoir un schéma non normalisé en respectant 1NF, 2NF et 3NF, tout en justifiant une exception de dénormalisation.
Contexte
La table commandes_mauvais_design contient des colonnes produit_1, produit_2, produit_3 pour stocker jusqu'à trois produits par commande. Un client vient de vouloir en commander un quatrième, ce qui est structurellement impossible sans modifier la table. Tu dois reconcevoir ce schéma, puis juger une proposition de dénormalisation.