Presque tous les schémas vraiment pénibles avec lesquels j’ai dû vivre ont commencé de la même façon. Quelqu’un a ouvert un fichier de migration et s’est mis à taper CREATE TABLE avant que personne n’ait convenu de ce que les données étaient réellement. Les tables sont venues en premier et la compréhension ensuite, ce qui est exactement à l’envers. Un schéma est le dernier artefact de la modélisation, pas le premier. Au moment où j’écris du SQL, la réflexion difficile est déjà faite.
Voici la méthodologie que j’utilise pour passer d’une demande floue à un schéma que j’accepte de signer. Elle n’est pas lourde. Elle ne demande pas d’outillage spécial. Elle demande surtout de ralentir le temps d’un après-midi pour avancer vite les deux années suivantes. Pour les principes de conception qui gouvernent le schéma final, associez ceci à mon article sur la normalisation et quand dénormaliser.
Étape un, rassembler les noms et les règles
Je commence par lire ou écouter comment les gens qui font réellement le travail le décrivent. Pas les ingénieurs. Les gens du métier. Je chasse deux choses. Les noms, qui deviennent des entités candidates, et les règles, qui deviennent des contraintes et des relations.
Quand un coordinateur logistique dit « une expédition peut avoir plusieurs colis mais chaque colis appartient à exactement une expédition », il vient de me tendre une relation un-à-plusieurs et une clé étrangère NOT NULL, gratuitement, en langage clair. Les experts métier font déjà la modélisation. Mon travail est de l’écrire fidèlement et de remarquer quand leurs phrases se contredisent.
Je tiens un glossaire au fur et à mesure. L’outil de modélisation le plus sous-estimé est une définition convenue de chaque terme. Quand deux personnes utilisent le mot « compte » pour dire deux choses différentes, vous ne le découvrirez qu’en production, à moins d’avoir forcé la définition tôt.
Étape deux, trouver les entités et leur identité
À partir des noms, je dégage les vraies entités. Le test que j’applique est l’identité. Cette chose a-t-elle une existence propre que je dois référencer dans le temps ? Un client en a une. Une commande en a une. Une ligne de commande en a une. La couleur « bleu » généralement non, c’est un attribut, jusqu’au jour où le métier a besoin d’un catalogue de couleurs avec ses propres règles, et là elle gagne son statut d’entité.
Pour chaque entité je pose immédiatement une question. Qu’est-ce qui rend une ligne unique ? Parfois il existe une clé naturelle, comme un code pays ISO. Plus souvent non, et j’ajoute une clé de substitution, une colonne d’identité générée sans signification métier. Je penche vers les clés de substitution pour la plupart des entités parce que les clés naturelles ont la fâcheuse habitude de changer, et une clé primaire qui change est une clé primaire qui gâche votre semaine.
Étape trois, cartographier les relations
Maintenant je connecte les entités, et je suis précis sur la cardinalité parce que c’est là que le schéma se décide.
- Un-à-plusieurs est le cas courant. Le côté « plusieurs » porte une clé étrangère pointant vers le côté « un ». Une commande a plusieurs lignes, donc order_items porte le order_id.
- Plusieurs-à-plusieurs devient toujours une table de jonction. Il n’y a pas d’autre façon honnête de le représenter. Étudiants et cours se rencontrent dans une table inscriptions qui porte les deux clés étrangères.
- Un-à-un est rare et mérite la méfiance. Cela signifie souvent soit une entité que vous avez scindée sans raison, soit une extension optionnelle qui appartient vraiment à sa propre table. Je m’oblige à justifier chaque un-à-un.
Pour chaque relation je fixe aussi les règles de participation. La clé étrangère est-elle obligatoire ou optionnelle ? Que doit-il se passer à la suppression ? Ce ne sont pas des détails après coup. Le comportement ON DELETE est une vraie décision métier déguisée en décision technique, et le métier devrait avoir voix au chapitre.
Étape quatre, les attributs et la question de la granularité
Avec les entités et les relations en place, j’attache les attributs, et pour chacun je demande ce qu’il est vraiment. Est-il atomique, ou cache-t-il plusieurs faits entassés ensemble ? Un champ « nom » que tout le monde veut chercher par prénom et nom de famille, c’est deux colonnes qui se font passer pour une. Une adresse en est presque toujours plusieurs.
La question la plus importante à ce stade est la granularité. Que représente une ligne de cette table, exactement, en une phrase ? Si je ne peux pas le dire proprement, la table est confuse et les requêtes le seront aussi. « Une ligne par commande » est une granularité claire. « Une ligne par commande, sauf parfois par expédition » est un futur rapport d’incident.
Étape cinq, écrire le schéma et laisser la base aider
Ce n’est que maintenant que j’écris du SQL, et à ce stade il s’écrit presque tout seul, parce que la réflexion est terminée. Voici le genre de chose qui découle des étapes ci-dessus pour un simple domaine d’inscription aux cours.
-- Une ligne par étudiant
CREATE TABLE students (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email CITEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL,
enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE
);
-- Une ligne par cours proposé
CREATE TABLE courses (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code TEXT NOT NULL UNIQUE, -- clé naturelle, stable par catalogue
title TEXT NOT NULL,
capacity INT NOT NULL CHECK (capacity > 0)
);
-- La table de jonction : une ligne par étudiant par cours
CREATE TABLE enrollments (
student_id BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE RESTRICT,
grade TEXT CHECK (grade IN ('A','B','C','D','F') OR grade IS NULL),
PRIMARY KEY (student_id, course_id)
);Regardez combien du modèle est désormais imposé par la base plutôt que laissé à l’espoir. Le plusieurs-à-plusieurs devient une clé primaire composite, ce qui empêche aussi un étudiant de s’inscrire deux fois au même cours sans aucun code applicatif. Les deux choix ON DELETE différents encodent une vraie règle : supprimer un étudiant retire ses inscriptions, mais vous ne pouvez pas supprimer un cours qui a encore des étudiants.
Étape six, valider contre les requêtes que vous exécuterez
Un modèle ne vaut que par les questions auxquelles il sait répondre. Avant de le déclarer terminé, je prends les cinq ou dix requêtes que l’application exécutera le plus souvent et je les écris contre le schéma sur papier. Si une question courante exige une jointure tordue à cinq tables ou une sous-requête imbriquée trois niveaux de profondeur, le modèle se bat contre la charge et je reviens une étape en arrière.
C’est aussi là que les motifs d’accès commencent à éclairer l’indexation, même si je garde cela comme une préoccupation distincte. Rendez le modèle honnête d’abord, puis rendez-le rapide. Je détaille le volet performance dans mon guide de l’indexation des bases de données.
Étape sept, prévoir le changement
Aucun modèle ne survit intact au contact d’une feuille de route, alors je conçois pour l’évolution dès le départ. J’évite les colonnes qui veulent dire des choses différentes selon les lignes. Je préfère ajouter une colonne nullable ou une nouvelle table plutôt que de surcharger une colonne existante. Je tiens une discipline de migration où chaque changement de schéma est un fichier versionné et révisable, jamais une édition manuelle d’une base en production.
L’état d’esprit qui m’a le mieux servi est celui-ci. Modéliser, c’est écrire ce qui est vrai du monde, avec assez de soin pour que la base puisse l’imposer. Le SQL n’est que la transcription. Faites la réflexion d’abord, écrivez le schéma en dernier, validez-le contre les vraies questions, et vous obtenez des tables ennuyeuses dans le meilleur sens du terme. Les schémas ennuyeux ne vous réveillent pas.