Normalisation : 1NF, 2NF et 3NF

Résumé

La normalisation découpe les tables pour qu’une information ne soit stockée qu’à un seul endroit. Elle supprime trois anomalies : d’insertion, de mise à jour et de suppression. Mnémonique : la 1NF impose des valeurs atomiques et une clé ; la 2NF, que chaque colonne dépende de toute la clé ; la 3NF, qu’elle ne dépende de rien d’autre que la clé.

Prérequis

Savoir ce qu’est une clé primaire (la ou les colonnes qui identifient une ligne). La notion de dépendance, centrale pour la 2NF et la 3NF, est expliquée juste après les anomalies.

Les trois anomalies

Toutes naissent d’une même cause : un fait stocké à plusieurs endroits, ou deux faits mélangés dans une même ligne.

Anomalie	Description	Exemple
Insertion	Impossible d’ajouter un fait sans en ajouter un autre	Créer une catégorie sans produit
Mise à jour	Une modification doit être répétée sur plusieurs lignes	Changer un prix répété sur N lignes
Suppression	Supprimer une ligne fait perdre un fait indépendant	Supprimer le dernier produit d’une catégorie efface son responsable

Les exemples des sections 2NF et 3NF illustrent ces anomalies.

Que veut dire « dépendre » ?

Y dépend de X (noté X → Y) signifie que, pour une valeur donnée de X, il n’existe qu’une seule valeur possible de Y. On dit aussi que X détermine Y.

Test pratique : prendre toutes les lignes où X a la même valeur. Si Y est toujours identique, Y dépend de X.

Sur la table Produits utilisée plus bas (section 3NF) :

Dépendance testée	Les lignes qui partagent X donnent…	Verdict
`CodeProduit → NomProduit`	P100 donne toujours « Clavier »	Vraie
`CodeCategorie → ResponsableCategorie`	CAT01 donne toujours « Sophie Martin »	Vraie
`CodeCategorie → CodeProduit`	CAT01 donne P100 et P200	Fausse : la flèche ne marche pas à l’envers

Vocabulaire utile pour la suite :

Clé : colonne(s) qui identifient chaque ligne de façon unique ; clé composite : clé de plusieurs colonnes (par exemple (NumCommande, CodeProduit)).
Attribut non-clé : colonne qui ne fait pas partie de la clé.
Dépendance partielle : une colonne non-clé dépend d’une partie de la clé composite (section 2NF).
Dépendance transitive : une chaîne A → B → C où B n’est pas une clé (section 3NF).

Les trois formes en un tableau

Forme	Règle	Interdit	Test
1NF	Une valeur par cellule	Les listes (« P100, P200 »)	La cellule est-elle atomique ?
2NF	1NF, et chaque colonne non-clé dépend de toute la clé	La dépendance partielle	Une moitié de la clé suffit-elle pour trouver la valeur ? Si oui, problème
3NF	2NF, et chaque colonne non-clé dépend uniquement de la clé	La dépendance transitive	La colonne se déduit-elle d’une autre colonne non-clé ? Si oui, problème

1NF : des valeurs atomiques

Chaque cellule contient une seule valeur : pas de liste, pas de colonnes répétées (produit1, produit2).

Violation :

Commande	Client	Produits
C001	Dupont	Clavier, Souris

Conforme :

Commande	Client	Produit
C001	Dupont	Clavier
C001	Dupont	Souris

2NF : pas de dépendance partielle

Une table est en 2NF si elle est en 1NF et si chaque colonne non-clé dépend de toute la clé primaire. La règle ne concerne que les clés composées : une clé d’une seule colonne est d’office en 2NF.

Le problème

CREATE TABLE Commandes_Produits (
    NumCommande  VARCHAR(10),
    CodeProduit  VARCHAR(10),
    NomProduit   VARCHAR(100),
    Quantite     INT,
    PrixCatalogue DECIMAL(10,2),
    PRIMARY KEY (NumCommande, CodeProduit)
);

NumCommande	CodeProduit	NomProduit	Quantite	PrixCatalogue
C001	P100	Clavier	2	25,00 €
C001	P200	Souris	1	15,00 €
C002	P100	Clavier	5	25,00 €

La clé est (NumCommande, CodeProduit). Quantite dépend des deux. En revanche NomProduit et PrixCatalogue ne dépendent que de CodeProduit : c’est une dépendance partielle. Conséquences : « Clavier / 25,00 € » est répété à chaque commande, un changement de prix demande plusieurs écritures, et le même produit peut finir avec deux prix différents.

La correction

L’ordre de création compte : les tables référencées d’abord.

CREATE TABLE Produits (
    CodeProduit   VARCHAR(10) PRIMARY KEY,
    NomProduit    VARCHAR(100) NOT NULL,
    PrixCatalogue DECIMAL(10,2) NOT NULL
);
 
CREATE TABLE Commandes (
    NumCommande  VARCHAR(10) PRIMARY KEY,
    DateCommande DATE NOT NULL
);
 
CREATE TABLE Commandes_Produits (
    NumCommande  VARCHAR(10),
    CodeProduit  VARCHAR(10),
    Quantite     INT NOT NULL,
    PRIMARY KEY (NumCommande, CodeProduit),
    FOREIGN KEY (NumCommande) REFERENCES Commandes(NumCommande),
    FOREIGN KEY (CodeProduit) REFERENCES Produits(CodeProduit)
);

Prix catalogue et prix facturé

L’exemple suppose un prix unique par produit. Dans un vrai système de commandes, le prix payé à la date de la commande doit rester dans la ligne de commande (par exemple PrixFacture), sinon changer le prix du catalogue réécrit l’historique des ventes. Ce prix dépend alors bien de toute la clé (commande, produit) : la table reste en 2NF.

3NF : pas de dépendance transitive

Une table est en 3NF si elle est en 2NF et si aucune colonne non-clé ne dépend d’une autre colonne non-clé.

flowchart LR
    A["Clé : CodeProduit"] --> B["CodeCategorie<br/>(non-clé)"] --> C["NomCategorie,<br/>ResponsableCategorie<br/>(non-clé)"]
    A -. "dépendance transitive" .-> C

Le problème

À exécuter dans un schéma vierge

Les tables Produits et Categories de cette section remplacent celles de la section 2NF : exécuter les blocs dans un schéma vide, ou supprimer d’abord les tables précédentes.

CREATE TABLE Produits (
    CodeProduit          VARCHAR(10) PRIMARY KEY,
    NomProduit           VARCHAR(100),
    PrixCatalogue        DECIMAL(10,2),
    CodeCategorie        VARCHAR(10),
    NomCategorie         VARCHAR(100),
    ResponsableCategorie VARCHAR(100)
);

CodeProduit	NomProduit	PrixCatalogue	CodeCategorie	NomCategorie	ResponsableCategorie
P100	Clavier	25,00 €	CAT01	Périphériques	Sophie Martin
P200	Souris	15,00 €	CAT01	Périphériques	Sophie Martin
P300	Écran	250,00 €	CAT02	Affichage	Pierre Durand

NomCategorie et ResponsableCategorie dépendent de CodeCategorie, qui est lui-même une colonne non-clé. Les quatre conséquences :

Redondance : « Périphériques / Sophie Martin » répété pour chaque produit.
Mise à jour : changer de responsable oblige à modifier toutes les lignes de la catégorie.
Suppression : supprimer le dernier produit « Affichage » efface aussi l’information que Pierre Durand gère cette catégorie.
Insertion : impossible de créer la catégorie « Gaming » sans lui rattacher un produit.

Test appliqué : CodeCategorie → ResponsableCategorie est vraie (CAT01 donne toujours Sophie Martin), mais CodeCategorie n’est pas la clé de la table (CAT01 apparaît deux fois). La colonne ResponsableCategorie se déduit donc d’une autre colonne non-clé : la 3NF est violée.

La correction

CREATE TABLE Categories (
    CodeCategorie        VARCHAR(10) PRIMARY KEY,
    NomCategorie         VARCHAR(100) NOT NULL,
    ResponsableCategorie VARCHAR(100) NOT NULL
);
 
CREATE TABLE Produits (
    CodeProduit   VARCHAR(10) PRIMARY KEY,
    NomProduit    VARCHAR(100) NOT NULL,
    PrixCatalogue DECIMAL(10,2) NOT NULL,
    CodeCategorie VARCHAR(10) NOT NULL,
    FOREIGN KEY (CodeCategorie) REFERENCES Categories(CodeCategorie)
);

Résultat : renommer la catégorie « Périphériques » demande une seule ligne à modifier dans Categories, au lieu d’une par produit de cette catégorie.

Définition formelle

Pour aller plus loin, la 3NF se définit avec les clés candidates :

Clé candidate : toute combinaison de colonnes qui pourrait servir de clé primaire.
Super-clé : tout ensemble de colonnes qui identifie une ligne, même avec des colonnes en trop (par exemple (CodeProduit, NomProduit) est une super-clé de Produits, puisque CodeProduit suffit déjà).
3NF, définition formelle : pour toute dépendance X → A, soit X est une super-clé, soit A fait partie d’une clé candidate.

Sur CodeCategorie → ResponsableCategorie dans la table d’origine : CodeCategorie n’est pas une super-clé (CAT01 apparaît deux fois) et ResponsableCategorie n’appartient à aucune clé candidate. La 3NF est violée, ce que le test pratique avait déjà montré.

Le schéma final

erDiagram
    COMMANDES ||--o{ COMMANDES_PRODUITS : contient
    PRODUITS ||--o{ COMMANDES_PRODUITS : "est commandé dans"
    CATEGORIES ||--o{ PRODUITS : classe

-- Chiffre d'affaires par catégorie
SELECT cat.NomCategorie, cat.ResponsableCategorie,
       SUM(cp.Quantite * p.PrixCatalogue) AS CA_Total
FROM Categories cat
JOIN Produits p ON cat.CodeCategorie = p.CodeCategorie
JOIN Commandes_Produits cp ON p.CodeProduit = cp.CodeProduit
GROUP BY cat.CodeCategorie, cat.NomCategorie, cat.ResponsableCategorie
ORDER BY CA_Total DESC;

Cette requête utilise le prix catalogue pour rester simple ; avec un PrixFacture dans la ligne, c’est lui qu’il faudrait sommer.

Quand dénormaliser

On dénormalise quand les lectures dominent largement les écritures et que le temps de lecture compte plus que l’espace ou la simplicité des mises à jour.

Contexte	Normaliser	Dénormaliser
OLTP, écritures fréquentes	Oui	Non
Cohérence critique	Oui	Non
Reporting et BI, calcul par lot	Non	Oui, avec des tables pré-agrégées
Tableau de bord à lecture rapide	Non	Oui
Base documentaire (MongoDB)	Non	Documents imbriqués par conception

Exemple de table de reporting dénormalisée volontairement :

CREATE TABLE Ventes_Rapport_Mensuel (
    Mois         DATE,
    NomProduit   VARCHAR(100),
    NomCategorie VARCHAR(100),
    Responsable  VARCHAR(100),
    CA_Total     DECIMAL(12,2)
);
CREATE INDEX idx_mois ON Ventes_Rapport_Mensuel (Mois);

Au-delà de la 3NF

BCNF (Boyce-Codd) : plus stricte, chaque déterminant doit être une clé candidate. Elle diffère de la 3NF seulement quand plusieurs clés candidates se chevauchent.
4NF : traite les dépendances multivaluées (deux listes indépendantes dans une même table).
5NF : traite les dépendances de jointure (une table qui ne se reconstruit sans perte que par la jointure de plusieurs projections).

Ces formes sont rarement nécessaires en pratique. La 3NF suffit à la plupart des schémas transactionnels, mais un schéma qui présente un de ces cas doit être traité, pas ignoré.

Notes liées

Schéma en étoile, de bout en bout : le même exemple en modèle dimensionnel.
OLTP vs OLAP et stockage en colonnes : pourquoi le modèle normalisé convient à l’OLTP.
Inmon, Kimball et Data Vault : la modélisation dimensionnelle choisit son grain et ses dimensions en connaissance de cause ; ce n’est pas seulement de la dénormalisation.
Glossaire data engineering : clé primaire, dépendance fonctionnelle.
MOC Data Engineering : carte du vault.

Carnets

Explorateur

Normalisation 2NF et 3NF

Normalisation : 1NF, 2NF et 3NF

Les trois anomalies

Que veut dire « dépendre » ?

Les trois formes en un tableau

1NF : des valeurs atomiques

2NF : pas de dépendance partielle

Le problème

La correction

3NF : pas de dépendance transitive

Le problème

La correction

Définition formelle

Le schéma final

Quand dénormaliser

Au-delà de la 3NF

Notes liées

Vue Graphique

Table des Matières

Liens retour