khalil Drissi Cms

Étiquette : conception bdd

  • Conception de bases de données et normalisation (et quand dénormaliser)

    Conception de bases de données et normalisation (et quand dénormaliser)

    J’ai hérité d’assez de schémas cassés pour avoir des avis tranchés. Les pires incidents que j’ai dû gérer ne venaient presque jamais d’un index manquant ou d’un disque lent. Ils venaient d’un modèle de données qui mentait. Une colonne qui voulait dire trois choses différentes selon la ligne. Un champ « statut » qui servait en secret de dépotoir à texte libre. Une clé étrangère qui existait dans la tête de quelqu’un mais jamais dans la base. Une bonne conception est l’assurance la moins chère que vous achèterez jamais, et vous l’achetez avant d’écrire la moindre requête.

    Cet article explique comment j’aborde réellement la normalisation, ce que les formes normales apportent en pratique, et la poignée de situations où je dénormalise délibérément. Si vous voulez le volet collecte des besoins, je l’ai traité à part dans mon guide sur la méthodologie de modélisation des données. Ici, on parle du schéma lui-même.

    Contre quoi la normalisation vous protège vraiment

    On parle de la normalisation comme d’un exercice académique. Elle ne l’est pas. Chaque forme normale existe pour empêcher une classe précise de bugs qui finira par vous réveiller à 3h du matin. Retirez le jargon formel et l’objectif est simple. Stocker chaque fait une seule fois, à l’endroit où il appartient, pour qu’il soit impossible que deux copies du même fait se contredisent.

    Quand la même donnée vit à deux endroits, ces deux endroits finiront par diverger. Pas peut-être. Ils divergeront. Quelqu’un met à jour l’e-mail du client dans une table et oublie l’autre. Un batch touche la moitié des lignes. Vous avez maintenant deux vérités et aucun moyen de savoir laquelle est correcte. La normalisation supprime la seconde copie pour que la contradiction devienne impossible plutôt que simplement improbable.

    Les formes normales, à ma façon

    Je ne récite pas les définitions formelles, mais je garde leur intention en tête quand j’esquisse des tables.

    • La première forme normale signifie pas de groupes répétés ni de colonnes multivaluées. Si vous nommez des colonnes telephone1, telephone2, telephone3, une table séparée attend de naître. Une liste séparée par des virgules dans un varchar est le même crime déguisé.
    • La deuxième forme normale signifie que chaque colonne non-clé dépend de toute la clé primaire, pas seulement d’une partie. Cela ne mord qu’avec des clés composites, mais quand ça mord, ça laisse une marque.
    • La troisième forme normale signifie que les colonnes non-clés dépendent de la clé et de rien d’autre que la clé. Si une colonne dépend d’une autre colonne non-clé, elle a sa propre table. L’exemple classique est de stocker une ville et son code postal ensemble alors que l’un détermine l’autre.

    Au quotidien, si j’atteins la troisième forme normale je suis généralement en bonne posture. Boyce-Codd et les formes supérieures comptent pour des cas précis de clés qui se chevauchent, mais la troisième forme normale attrape la grande majorité des vraies erreurs de modélisation que je vois en revue de code.

    Un exemple concret

    Disons que nous stockons des commandes. La version naïve entasse tout dans une seule table large, en répétant le nom et l’e-mail du client sur chaque ligne de commande. Voici la version normalisée que je livrerais réellement.

    -- Les clients possèdent leurs propres faits, une seule fois
CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       CITEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Les commandes référencent le client, elles ne le copient pas
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    status       TEXT NOT NULL
                 CHECK (status IN ('pending','paid','shipped','cancelled')),
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Les lignes ont leur propre granularité : une ligne par produit par commande
CREATE TABLE order_items (
    order_id     BIGINT NOT NULL REFERENCES orders(id),
    product_id   BIGINT NOT NULL REFERENCES products(id),
    quantity     INT NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(12,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

    Remarquez quelques choix qui ne relèvent pas strictement des formes normales mais qui accompagnent une bonne conception. La colonne status a une contrainte CHECK pour que la base elle-même impose les valeurs autorisées. Le unit_price vit sur la ligne, pas sur le produit, parce que le prix au moment de la vente est un fait différent du prix actuel. Cette distinction est exactement le genre de chose que la normalisation vous oblige à voir. Est-ce la valeur actuelle ou la valeur telle qu’elle était ? Ce ne sont pas le même fait et ils n’appartiennent pas à la même colonne.

    Les contraintes font partie de la conception, pas de la décoration

    Un schéma sans contraintes est une suggestion. Je pousse autant d’invariants que raisonnablement possible dans la base, parce que le code applicatif est le mauvais endroit pour garantir l’intégrité des données. Il y aura toujours un second écrivain un jour. Un script de migration, un outil d’admin, un collègue qui fouille dans une session psql. La base est la seule couche qu’ils partagent tous.

    J’utilise donc NOT NULL agressivement, des clés étrangères sans m’excuser, des contraintes UNIQUE sur tout ce qui doit être unique, et des contraintes CHECK pour les plages de valeurs et les énumérations. Si vous ne retenez qu’une habitude de cet article, prenez celle-là. La plupart des « données mystérieusement corrompues » que j’ai déboguées auraient été impossibles avec une contrainte qui prend trente secondes à écrire. J’approfondis ce point dans mes notes sur les bonnes pratiques de schéma.

    Quand je dénormalise volontairement

    Maintenant l’hérésie. Je dénormalise régulièrement, et je ne culpabilise pas, parce que la dénormalisation faite avec intention est une optimisation, pas une erreur. L’astuce est que vous ne dénormalisez qu’après avoir compris le motif d’accès, jamais avant. Une dénormalisation prématurée n’est qu’un modèle de données avec des bugs en plus.

    Voici les cas où j’y ai recours.

    • Agrégats à forte lecture. Si un tableau de bord lit le total d’une commande mille fois pour chaque fois où la commande change, recalculer ce total à chaque lecture est du gaspillage. Je stockerai une colonne total mise en cache et je la maintiendrai à jour avec un trigger ou dans la même transaction que l’écriture.
    • Tables de reporting et d’analyse. La normalisation transactionnelle et les motifs de requêtes analytiques tirent dans des sens opposés. Une table large dénormalisée ou un schéma en étoile peut transformer une jointure brutale à huit tables en un seul parcours. Je les garde séparées de la source de vérité et je les reconstruis à partir d’elle.
    • Jointures coûteuses sur le chemin chaud. Parfois une jointure est réellement le goulot même après indexation. Copier une colonne souvent lue pour éviter une jointure peut en valoir la peine, tant que vous maîtrisez le chemin de mise à jour.

    La règle non négociable dans chacun de ces cas est que la version normalisée reste la source de vérité. La copie dénormalisée est dérivée, jetable et reconstructible. Dès l’instant où vous avez deux sources de vérité indépendantes, vous revenez au péché originel que la normalisation servait à empêcher.

    Comment je garde la dénormalisation sûre

    Si je stocke une valeur dérivée, je rends la dérivation explicite et automatique. Une colonne en cache est mise à jour dans la même transaction que sa source, ou par un trigger, jamais par un commentaire optimiste disant « penser à mettre à jour ceci ». Une vue matérialisée a un calendrier de rafraîchissement documenté. Une table de reporting est reconstruite par un job que je peux lancer à la demande et vérifier contre la source.

    J’écris aussi un contrôle, même lent et exécuté la nuit, qui compare la valeur dérivée à un calcul frais et hurle s’ils diffèrent. La dérive est le mode de défaillance de la dénormalisation, et la seule défense est de la détecter tôt. Une fois que je peux prouver que la copie correspond à la source, le gain de performance arrive avec la conscience tranquille. Quand les copies commencent à diverger, c’est presque toujours parce qu’un plan de requête a changé ou qu’un index a disparu, exactement le terrain que je couvre dans mon guide de l’indexation.

    L’ordre dans lequel je procède

    Ma séquence par défaut n’a pas changé depuis des années. Normaliser d’abord, jusqu’à la troisième forme normale, avec de vraies contraintes. Rendre le modèle correct et le laisser correct. Puis mesurer. Ce n’est que lorsqu’un motif d’accès précis et mesuré l’exige que j’introduis une copie dénormalisée, et uniquement comme un artefact dérivé avec un chemin de mise à jour imposé. La correction d’abord, puis la vitesse, et jamais une vitesse achetée au prix d’un mensonge dans les données.

    Cet ordre compte parce qu’il est bien plus facile de dénormaliser un modèle propre que de nettoyer un modèle vaseux dès la naissance. Commencez strict. Relâchez délibérément. Votre futur vous, réveillé à 3h du matin, vous remerciera.

  • Une méthodologie pratique de modélisation des données, des besoins au schéma

    Une méthodologie pratique de modélisation des données, des besoins au schéma

    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.