Modéliser une base de données relationnelle sans se planter dès le départ

Une base de données mal modélisée ne se voit pas tout de suite. Elle se paie plus tard, quand le projet grossit et qu'il est trop tard pour tout refaire. Cet article te montre comment poser un schéma propre avant d'écrire la moindre ligne de code, avec des règles simples que tu peux appliquer aujourd'hui.

Le problème : tu construis ta base au fil de l'eau

Voici la scène classique. Tu démarres un projet, disons une petite boutique en ligne. Tu crées une table commandes avec tout ce qui te passe par la tête : le nom du client, son email, son adresse, le nom du produit, le prix, la quantité. Tout dans une seule table. Ça marche pour les trois premières commandes de test.

Puis un client passe une deuxième commande. Tu retapes son nom et son email. Il déménage. Tu dois retrouver toutes ses lignes pour changer l'adresse. Tu te trompes sur une seule, et maintenant le même client a deux adresses différentes selon la commande. Personne ne sait laquelle est la bonne.

Ensuite tu veux qu'une commande contienne plusieurs produits. Tu inventes des colonnes produit1, produit2, produit3. Le quatrième produit n'a plus de place. Tu colles alors tous les produits dans un seul champ texte séparé par des virgules. À partir de là, tu ne peux plus calculer un chiffre d'affaires par produit sans découper du texte à la main.

Chacune de ces décisions semblait raisonnable sur le moment. Mises bout à bout, elles transforment ta base en piège. C'est exactement le genre de dérive silencieuse qu'on retrouve dans les signaux d'alarme d'une dette technique qui monte.

Le principe : une base se conçoit avant d'être codée

Modéliser une base relationnelle, c'est répondre à trois questions dans l'ordre, sur papier ou sur un schéma, avant d'ouvrir ton éditeur :

• Quelles sont les entités de mon domaine ? Ce sont les choses dont je dois garder une trace : un client, une commande, un produit.
• Quels sont les attributs de chaque entité ? Pour un client : un nom, un email. Pas plus pour l'instant.
• Quelles sont les relations entre ces entités ? Un client passe plusieurs commandes. Une commande contient plusieurs produits.

Une entité par table, un identifiant par ligne

La règle de base : chaque entité devient une table, et chaque ligne d'une table possède un identifiant unique qu'on appelle la clé primaire (souvent une colonne id qui s'incrémente toute seule). Cet identifiant ne change jamais et ne se réutilise jamais.

Pour relier deux tables, tu poses une clé étrangère : une colonne qui contient l'identifiant d'une ligne dans une autre table. La table commandes aura une colonne client_id qui pointe vers le id de la table clients. L'adresse du client est stockée une seule fois, dans la table clients. Si elle change, tu la modifies à un seul endroit.

La normalisation, sans le jargon qui fait peur

Tu as peut-être croisé les termes "première forme normale", "deuxième", "troisième". Ça sonne théorique, mais derrière ce sont trois réflexes concrets.

Retiens la version courte : une donnée est stockée à un seul endroit. Si tu te surprends à copier la même information dans deux tables, quelque chose ne va pas dans ton modèle.

Lire les relations : qui a plusieurs de quoi

Avant de placer une clé étrangère, tu dois comprendre le sens de la relation. C'est ce qu'on appelle la cardinalité, et ça se résume à une question posée dans les deux sens.

Prends "client" et "commande". Un client peut-il avoir plusieurs commandes ? Oui. Une commande peut-elle appartenir à plusieurs clients ? Non. On est donc dans une relation "un à plusieurs" : un client, plusieurs commandes. La règle est simple, la clé étrangère se met toujours du côté "plusieurs". C'est donc commandes qui porte la colonne client_id, jamais l'inverse.

Pose la même question pour "commande" et "produit". Une commande contient plusieurs produits ? Oui. Un produit apparaît dans plusieurs commandes ? Oui aussi. Quand la réponse est "plusieurs" des deux côtés, aucune des deux tables ne peut porter la clé étrangère sans dupliquer des lignes. Il te faut une troisième table, celle qu'on a appelée lignes_commande.

Ce réflexe de poser la question dans les deux sens te fait éviter la plupart des erreurs de structure. Tu sauras d'instinct où placer la clé étrangère, et quand sortir une table de liaison. C'est moins une affaire de mémoire que d'habitude.

Exemple complet : la boutique en ligne

Reprenons la boutique, cette fois correctement. On a quatre entités : des clients, des produits, des commandes, et le lien entre une commande et les produits qu'elle contient. Ce dernier lien mérite sa propre table, parce qu'une commande contient plusieurs produits et qu'un produit apparaît dans plusieurs commandes. C'est une relation dite "plusieurs à plusieurs", et elle passe toujours par une table intermédiaire.

Voici le schéma en SQL, ici avec MySQL :

CREATE TABLE clients (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    nom       VARCHAR(100) NOT NULL,
    email     VARCHAR(150) NOT NULL UNIQUE,
    adresse   VARCHAR(255),
    cree_le   DATETIME DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE produits (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    nom       VARCHAR(150) NOT NULL,
    prix      DECIMAL(10,2) NOT NULL,
    stock     INT NOT NULL DEFAULT 0
);
 
CREATE TABLE commandes (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    client_id  INT NOT NULL,
    cree_le    DATETIME DEFAULT CURRENT_TIMESTAMP,
    statut     VARCHAR(30) NOT NULL DEFAULT 'en_attente',
    FOREIGN KEY (client_id) REFERENCES clients(id)
);
 
CREATE TABLE lignes_commande (
    commande_id    INT NOT NULL,
    produit_id     INT NOT NULL,
    quantite       INT NOT NULL,
    prix_unitaire  DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (commande_id, produit_id),
    FOREIGN KEY (commande_id) REFERENCES commandes(id),
    FOREIGN KEY (produit_id) REFERENCES produits(id)
);

Regarde la table lignes_commande. Elle relie une commande à un produit, avec la quantité commandée. Sa clé primaire est composée de deux colonnes, ce qui garantit qu'un même produit n'apparaît qu'une fois par commande.

Un détail mérite qu'on s'y arrête : la colonne prix_unitaire dans lignes_commande. Le prix existe déjà dans la table produits, alors pourquoi le répéter ? Parce que le prix d'un produit change avec le temps. Au moment de la commande, tu dois figer le prix payé. Ce n'est pas de la duplication : c'est une donnée historique, distincte du prix actuel. Savoir faire cette différence est tout l'art de la modélisation.

Avec ce schéma, retrouver le total d'une commande devient une requête propre :

SELECT c.id,
       cl.nom,
       SUM(lc.quantite * lc.prix_unitaire) AS total
FROM commandes c
JOIN clients cl        ON cl.id = c.client_id
JOIN lignes_commande lc ON lc.commande_id = c.id
GROUP BY c.id, cl.nom;

Aucun découpage de texte, aucune donnée dupliquée à risque. Si tu veux pratiquer ce genre de requêtes et de modélisation pas à pas, c'est tout l'objet de notre formation SQL et bases de données relationnelles avec MySQL.

Les pièges qui reviennent le plus souvent

Tout entasser dans une seule table

La grande table fourre-tout est le piège numéro un. Une table users avec quarante colonnes, dont la moitié sont vides pour la moitié des lignes, signale presque toujours plusieurs entités mélangées de force. Demande-toi : est-ce que toutes ces colonnes décrivent bien la même chose ?

Stocker une liste dans une colonne

Mettre "tag1,tag2,tag3" dans une colonne texte te coûtera cher. Tu ne pourras pas filtrer proprement, ni compter, ni faire de jointure. Une liste de valeurs liées veut dire une table de plus. C'est non négociable.

Oublier les contraintes

Les clés étrangères, les NOT NULL, les UNIQUE ne sont pas décoratifs. Ils empêchent ta base d'accepter des données incohérentes : une commande rattachée à un client qui n'existe pas, deux comptes avec le même email. Sans contraintes, c'est ton code applicatif qui doit tout vérifier, et il finira par oublier un cas.

Choisir les mauvais types

Stocker un prix en nombre flottant produit des arrondis faux à long terme. Pour de l'argent, utilise DECIMAL. Stocker une date en texte t'empêche de trier par date et de calculer des durées. Stocker un booléen en chaîne "oui" ou "non" te complique chaque filtre. Le bon type au départ t'évite des migrations pénibles ensuite, et il documente ton intention pour la personne qui lira le schéma après toi.

Oublier les index, ou en mettre partout

Une colonne sur laquelle tu filtres ou tu joins souvent mérite un index, sinon tes requêtes ralentissent dès que la table grossit. Mais un index sur chaque colonne ralentit les écritures et gaspille de l'espace. Indexe ce que tu interroges, pas le reste.

Sur-normaliser par perfectionnisme

À l'inverse, vouloir tout découper en vingt micro-tables rend chaque requête lourde de jointures. La 3NF suffit dans l'écrasante majorité des projets. La modélisation propre est un équilibre, pas une course à la pureté théorique. Le même bon sens s'applique partout, comme le rappellent nos principes de code qui distinguent un projet qu'on garde d'un projet qu'on jette.

La méthode en cinq minutes, à appliquer avant de coder

1. Liste les noms qui reviennent dans la description du projet. Ce sont tes entités candidates.
2. Donne à chaque entité une table et une clé primaire id.
3. Pour chaque relation, demande "un X a-t-il plusieurs Y ?" et place la clé étrangère du bon côté.
4. Pour les relations plusieurs à plusieurs, crée une table de liaison.
5. Vérifie qu'aucune donnée n'est stockée à deux endroits. Si oui, corrige avant la première ligne de code.

Questions fréquentes