Conception de tables et normalisation (schéma à 3 niveaux)

Le jeu de données de cet article — orders_flat (un exemple non normalisé)

La normalisation (une technique de conception qui découpe les tables pour réduire les données dupliquées et incohérentes) est l'idée de découper une seule grosse table qui contient tout en plusieurs tables plus faciles à garder cohérentes.

Dans cet article tu verras les problèmes qui apparaissent dans une table non normalisée avec des colonnes répétitives, puis tu construiras une compréhension de 1NF / 2NF / 3NF et du schéma à 3 niveaux à travers des exercices de conception pratiques.

Le jeu de données est orders_flat (2 lignes), qui entasse des données de commande dans une seule table.

C'est une table non normalisée classique où nom client, numéro de téléphone, produit 1 et produit 2 sont tous alignés sur une seule ligne — et tu la normaliseras en trois tables customer / order_record / order_line.

Avant de passer aux exercices, jette un œil aux définitions de colonnes et aux données d'exemple de la table orders_flat.

① Exécute PRAGMA table_info(orders_flat); pour vérifier les noms et types de colonnes. Remarque comment les colonnes produit apparaissent en colonnes répétitives comme item1 / item2.

② Exécute SELECT * FROM orders_flat; pour prévisualiser toutes les lignes. Observe aussi comment les lignes avec un seul produit finissent avec une chaîne vide ou NULL dans item2.

Trois anomalies dans une table non normalisée

Une conception comme orders_flat qui entasse tout dans une seule table produit facilement des contradictions quand tu ajoutes, modifies ou supprimes des données.

Les exemples classiques sont les trois : anomalie de mise à jour / anomalie d'insertion / anomalie de suppression.

Une anomalie de mise à jour se produit quand le même fait est dupliqué sur plusieurs lignes — par exemple, si tu veux changer un numéro de téléphone, tu dois mettre à jour chaque ligne sans en oublier.

Une anomalie d'insertion se produit quand tu ne peux pas enregistrer un client qui n'a pas encore passé de commande, parce qu'il n'y a pas de ligne de commande à laquelle attacher les infos du client.

Une anomalie de suppression se produit quand supprimer une seule commande efface aussi des informations que tu voulais garder, comme le numéro de téléphone du client.

Ça arrive parce que la conception ne respecte pas « un fait à un seul endroit ».

-- Exemple d'anomalie de suppression : supprimer la commande 2 efface aussi les coordonnées de Bob
-- (observe le comportement sur une table jetable flat_demo pour ne pas toucher au vrai orders_flat)
DROP TABLE IF EXISTS flat_demo;
CREATE TABLE flat_demo(
  order_id INTEGER, customer TEXT, customer_tel TEXT, product TEXT
);
INSERT INTO flat_demo VALUES
  (1,'Alice','03-1111','Pen'),
  (2,'Bob','03-2222','Clip');

-- Bob n'a qu'une seule commande. La supprimer perd aussi son numéro 03-2222
DELETE FROM flat_demo WHERE order_id = 2;
SELECT * FROM flat_demo;
-- -> les coordonnées de Bob ont disparu de la base (anomalie de suppression)

Reproduis sur une table jetable la situation où « le numéro de téléphone d'Alice a changé et tu veux le mettre à jour, mais le même numéro est dupliqué sur plusieurs lignes ». Cet exercice tient seul — tu créeras une table dédiée flat_anomaly pour rester autonome. (Si tu l'exécutes correctement, l'explication apparaîtra.)

① Fais DROP TABLE IF EXISTS sur la table flat_anomaly, puis CREATE avec 4 colonnes : order_id / customer / customer_tel / product.

② Insère 2 lignes pour les commandes d'Alice (toutes deux avec le même numéro 03-1111 ; les produits sont Pen et Note).

③ Exécute un UPDATE qui change le numéro d'Alice en 03-9999 mais ne touche qu'une seule ligne (par exemple avec une condition comme WHERE order_id = 1), puis exécute SELECT * FROM flat_anomaly; et confirme que les deux numéros ne correspondent pas.

De 1NF à 3NF — découper étape par étape

Les tables sont organisées par la normalisation en étapes.

1NF — se débarrasser des colonnes répétitives

Supprime les colonnes répétitives numérotées comme item1 / item2.

Avant — colonnes répétitives item1 / item2

Après — une ligne par produit, avec la clé primaire composite `(order_id, product)`

2NF — déplacer dans une autre table les colonnes qui ne dépendent que d'une partie de la clé primaire

La table de l'étape précédente a la clé primaire composite (order_id, product). Mais regarde de près : customer et tel sont déterminés par `order_id` seul (le même order_id a toujours le même client).

Déplacer ces colonnes qui dépendent uniquement d'une partie de la clé primaire (dépendance partielle) dans une table séparée, c'est la 2NF. Tu découpes en un en-tête de commande order_header et des détails de commande order_line.

Avant — customer et tel dupliqués sur chaque ligne

Après — découpé en 2 tables

order_header (une ligne par commande)

order_line (clé primaire composite (order_id, product), où order_id est une clé étrangère vers order_header)

3NF — déplacer dans encore une autre table les colonnes déterminées par des colonnes non-clé

En regardant l'order_header de l'étape précédente, tel n'est pas déterminé par order_id mais par `customer` (la même Alice a toujours 03-1111).

Éliminer les dépendances qui passent par une autre colonne non-clé au lieu d'aller directement depuis la clé primaire (dépendance transitive : order_id -> customer -> tel), c'est l'objet de la 3NF.

Tu extrais un référentiel client customer et tu laisses order_record ne contenir que la clé étrangère vers le client.

Avant — tel dépend de customer

Après — découpé en 3 tables

customer (référentiel client)

order_record (en-tête de commande : référence le client via une clé étrangère)

order_line (détails de commande : pareil que l'étape précédente)

Une fois découpé à ce point, chaque fait (le téléphone d'un client, le lien entre commande et client, la quantité d'un produit dans une commande) vit à un seul endroit. Ça élimine les anomalies de mise à jour, d'insertion et de suppression.

Le schéma à 3 niveaux et la forme finale — customer / order_record / order_line

Le schéma normalisé peut être organisé en trois niveaux ayant des rôles différents.

• Niveau référentiel : entités qui changent peu et sont référencées par d'autres, comme customer
• Niveau transactionnel : tables qui enregistrent des événements métier, comme order_record
• Niveau détail : tables qui contiennent les lignes de détail d'un événement, comme order_line

En pointant le côté référent vers le référentiel avec une clé étrangère, chaque fait (comme le téléphone d'un client) finit à un seul endroit.

La forme finale est ces trois tables :

customer(customer_id clé primaire, customer_name, customer_tel), order_record(order_id clé primaire, customer_id -> customer), order_line(order_id -> order_record, product, qty, clé primaire composite (order_id, product)).

Le téléphone vit dans une seule ligne de customer, donc une mise à jour ne porte que sur une ligne ; un client sans commandes peut quand même être ajouté à customer (anomalie d'insertion résolue) ; et supprimer une commande laisse le client dans customer (anomalie de suppression résolue).

-- Avantage de la normalisation (lecture seule) : le téléphone d'un client est dans une seule ligne de customer
-- Dans la forme orders_flat, il faudrait corriger chaque ligne de commande d'Alice = anomalie de mise à jour
UPDATE customer SET customer_tel = '03-9999'
WHERE customer_name = 'Alice';

-- Corrige un seul endroit, et le nouveau numéro coule dans chaque commande au JOIN
SELECT o.order_id, c.customer_name, c.customer_tel
FROM order_record o
JOIN customer c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Imagine l'exigence : « Normaliser orders_flat en trois tables — client, commande et détails de commande. » Conçois le schéma final avec des clés étrangères qui les relient, déplace les données, et reconstruis la liste originale avec un JOIN.

① Active les vérifications de clés étrangères, puis fais DROP TABLE IF EXISTS dans l'ordre order_line -> order_record -> customer (supprimer l'enfant en premier est plus sûr).

② CREATE la table customer (customer_id INTEGER clé primaire / customer_name TEXT NOT NULL / customer_tel TEXT).

③ CREATE la table order_record (order_id INTEGER clé primaire / customer_id INTEGER NOT NULL, clé étrangère vers customer).

④ CREATE la table order_line (order_id INTEGER, clé étrangère vers order_record / product TEXT NOT NULL / qty INTEGER NOT NULL / clé primaire composite (order_id, product)).

⑤ Insère Alice(1, 03-1111) et Bob(2, 03-2222) dans customer ; commande 1 -> client 1 et commande 2 -> client 2 dans order_record ; Pen(commande 1, 2) / Note(commande 1, 1) / Clip(commande 2, 5) dans order_line.

⑥ Joins les 3 tables sur order_id et customer_id et sélectionne order_id / customer_name / customer_tel / product / qty par ordre croissant de order_id et product pour confirmer que tu peux reconstruire la liste originale.

En utilisant les tables customer / order_record / order_line de l'exercice 2, confirme une par une que les anomalies de mise à jour, d'insertion et de suppression qui plombaient la table non normalisée ne se produisent plus. C'est le dernier exercice de l'article.

① Anomalie de mise à jour résolue : change le téléphone d'Alice en '03-9999'. Confirme que c'est un UPDATE sur une seule ligne de la table customer.

② Anomalie d'insertion résolue : INSÈRE un nouveau client Charlie (customer_id 3 / customer_name 'Charlie' / customer_tel '03-3333') dans customer. Confirme que tu peux enregistrer un client sans commandes.

③ Anomalie de suppression résolue : supprime la commande 1. Supprime d'abord depuis l'enfant order_line, puis depuis order_record. Exécute SELECT * FROM customer ORDER BY customer_id; et confirme que la ligne d'Alice est toujours dans customer.

Astuces — Mets toujours une clé primaire sur chaque table

Que tu aies normalisé ou non, conçois chaque table avec une clé primaire (`PRIMARY KEY`). Une clé primaire t'apporte trois choses :

- Identification unique de ligne : deux lignes ne peuvent pas avoir la même clé, donc UPDATE / DELETE ciblent de façon fiable exactement une ligne via WHERE pk = ...

- Référençable depuis d'autres tables : une clé étrangère a besoin d'une clé primaire (ou UNIQUE) de l'autre côté. C'est la base pour relier les tables que tu as découpées par normalisation

- Indexation automatique : les colonnes de clé primaire reçoivent automatiquement un index B-Tree, accélérant les recherches par égalité et les JOIN

Si aucune colonne unique n'est unique seule, utilise une clé primaire composite comme PRIMARY KEY (order_id, product). Sauter cette étape avec « je l'ajouterai plus tard » rend les lignes en doublon indissociables et fait perdre les trois avantages ci-dessus.