Conception de bases de données et normalisation : des entités à la FNBC

C'est dans la conception de la base de données que se décident les problèmes futurs d'un projet. Une application peut se réécrire en un week-end ; un schéma en production depuis trois ans, avec des millions de lignes et une douzaine de consommateurs, non. La bonne nouvelle, c'est que l'essentiel de cette discipline s'apprend et relève de la mécanique : décrire le domaine honnêtement, noter quelles valeurs déterminent quelles autres, puis laisser ces dépendances indiquer où découper les tables.

Ce guide déroule un seul petit exemple de bout en bout. Le domaine est une conférence : des participants s'inscrivent à des sessions, les sessions ont lieu dans des salles et des intervenants présentent des sujets. Il est volontairement banal, car l'intérêt n'est pas dans le domaine mais dans la redondance qui apparaît dès qu'on l'enregistre sans soin.

Entités, associations et clés

Commencez par nommer les choses dont parle le métier. Chaque nom qui a une existence propre et ses propres attributs est une entité candidate : participant, session, salle, intervenant. Chaque verbe qui les relie est une association : un participant s'inscrit à une session ; une session se tient dans une salle.

La cardinalité de l'association détermine la façon de la stocker :

• Un-à-plusieurs — une salle accueille plusieurs sessions, une session se tient dans une seule salle. Placez la clé étrangère du côté « plusieurs » : session.room_id.
• Plusieurs-à-plusieurs — un participant s'inscrit à plusieurs sessions, une session compte plusieurs participants. Il faut alors une table dédiée, dite table de jointure ou table d'association, dont la clé primaire est le couple de clés étrangères. Les attributs propres à l'association (l'heure d'inscription, la présence effective du participant) y ont aussi leur place.
• Un-à-un — vraiment rare. Cela signale en général deux entités qui pourraient n'en faire qu'une, ou une extension optionnelle que l'on préfère isoler pour des raisons de taille ou de contrôle d'accès.

Une clé candidate est un ensemble minimal de colonnes dont les valeurs sont uniques sur toutes les lignes et jamais nulles. Une table peut en avoir plusieurs ; celle que vous retenez comme clé primaire relève de la commodité, pas de la vérité. attendee.email et attendee.badge_number peuvent très bien être toutes deux des clés candidates.

Deux termes méritent d'être définis tôt, car la normalisation s'énonce avec eux. Une colonne est un attribut premier si elle appartient à une clé candidate, et un attribut non premier sinon. Chacune des formes normales ci-dessous est une règle sur la manière dont les attributs non premiers peuvent dépendre des attributs premiers.

Dépendances fonctionnelles

Une dépendance fonctionnelle notée A → B signifie : deux lignes qui ont la même valeur pour A doivent avoir la même valeur pour B. C'est une affirmation sur le métier, pas sur les données dont vous disposez à un instant donné. « Deux sessions dans la même salle ont la même capacité de salle » est une dépendance ; « aucun participant ne partage actuellement le même nom de famille » est une coïncidence.

Imaginez qu'on vous remette ce tableur, exporté sous forme d'une seule table :

attendee_email | attendee_name | session_code | session_title | room | room_capacity | starts_at           | topics
[email protected]| Ana Ruiz      | S12          | Query plans   | R2   | 120           | 2026-05-04 09:00    | sql, performance
[email protected]| Ana Ruiz      | S19          | Sharding      | R1   | 300           | 2026-05-04 11:00    | nosql, scaling
[email protected]| Bob Neri      | S12          | Query plans   | R2   | 120           | 2026-05-04 09:00    | sql, performance

Les dépendances confirmées par le métier sont les suivantes :

attendee_email                -> attendee_name
session_code                  -> session_title, room, starts_at
room                          -> room_capacity
(attendee_email, session_code)-> everything (the composite key)

La redondance devient une règle visible, et non plus une intuition : puisque session_code → room, chaque inscription supplémentaire à S12 répète « R2 » et « 120 ». C'est cette répétition qui provoque les trois anomalies classiques. L'anomalie de mise à jour : renommer une session oblige à modifier de nombreuses lignes, et en oublier une laisse la table se contredire. L'anomalie d'insertion : impossible d'enregistrer la capacité d'une salle tant que personne ne s'est inscrit à une session qui s'y tient. L'anomalie de suppression : annuler la dernière inscription à S12 efface jusqu'à l'existence même de S12.

Appliquer les formes normales

Chaque forme normale élimine une catégorie de dépendances. Atteindre une forme implique de satisfaire aussi toutes celles qui la précèdent.

Première forme normale

La 1FN exige que chaque colonne contienne une seule valeur atomique d'un type donné, et interdit les groupes répétitifs. La colonne topics échoue : elle entasse une liste dans une chaîne, si bien qu'on ne peut ni l'indexer, ni faire de jointure dessus, ni la contraindre, ni compter les sujets sans analyser du texte. Des colonnes nommées phone1, phone2, phone3 échouent au même test, dans l'autre sens.

La solution est une table :

CREATE TABLE session_topic (
  session_code  varchar(8) NOT NULL REFERENCES session(code) ON DELETE CASCADE,
  topic         varchar(40) NOT NULL,
  PRIMARY KEY (session_code, topic)
);

Une question revient souvent : une colonne jsonb ou tableau enfreint-elle la 1FN ? En théorie oui, en pratique cela dépend. Stocker dans une colonne document des contenus réellement opaques et variables est un choix d'ingénierie raisonnable ; y stocker des données structurées que vous filtrez et joignez régulièrement revient à contourner la 1FN au prix d'efforts supplémentaires.

Deuxième forme normale

La 2FN ne s'applique que lorsque la clé primaire est composite. Elle interdit la dépendance partielle : un attribut non premier qui dépend d'une partie de la clé plutôt que de la clé entière. Ici, la clé est (attendee_email, session_code), et pourtant session_title dépend de session_code seul, et attendee_name de attendee_email seul.

Découper selon ces dépendances donne trois tables — attendee, session et la table d'association des inscriptions — et cette dernière ne conserve que ce qui dépend de la clé entière, comme signed_up_at.

Troisième forme normale

La 3FN interdit la dépendance transitive : un attribut non premier déterminé par un autre attribut non premier. Dans la table session, on a session_code → room et room → room_capacity : la capacité dépend donc transitivement de la clé. Déplacez-la :

CREATE TABLE room (
  code      varchar(8) PRIMARY KEY,
  capacity  integer NOT NULL CHECK (capacity > 0)
);

CREATE TABLE session (
  code      varchar(8) PRIMARY KEY,
  title     varchar(120) NOT NULL,
  room_code varchar(8) NOT NULL REFERENCES room(code),
  starts_at timestamptz NOT NULL
);

La capacité n'est plus stockée qu'une fois. La corriger se résume à mettre à jour une seule ligne, sans aucun risque de rendre la base incohérente.

Forme normale de Boyce-Codd

La FNBC renforce la 3FN : tout déterminant doit être une clé candidate, y compris les déterminants d'attributs premiers. La différence n'apparaît que lorsque des clés candidates se chevauchent.

Prenez une table qui enregistre quel intervenant traite quel sujet dans une session, avec la règle qu'un intervenant donné ne traite jamais qu'un seul sujet :

speaker_assignment(session_code, topic, speaker)
  (session_code, topic)   -> speaker      -- candidate key
  (session_code, speaker) -> topic        -- candidate key
  speaker                 -> topic        -- determinant that is NOT a key

La table est en 3FN — topic est premier, il n'y a donc aucune dépendance transitive vers un attribut non premier — et pourtant le lien intervenant-sujet se répète pour chaque session où l'intervenant apparaît. Décomposez-la en speaker(name, topic) et session_speaker(session_code, speaker). Soyez honnête sur le compromis : cette décomposition est sans perte, mais elle ne préserve pas la clé composite d'origine sous forme de contrainte portant sur une seule table, ce qui explique en partie pourquoi la 3FN est parfois le point d'arrêt pragmatique.

La quatrième forme normale va un cran plus loin et sépare les faits multivalués indépendants. Si une session avait à la fois une liste de sujets et une liste de sponsors sans rapport entre elles, les entasser dans une même table produirait une ligne par combinaison. Les répartir dans deux tables, c'est la 4FN, et cela relève surtout du bon sens une fois qu'on l'a vu.

Clés et intégrité référentielle

Une clé naturelle est une donnée porteuse de sens — un ISBN, un code pays, une adresse e-mail. Une clé technique (ou clé de substitution) est une valeur générée, sans signification : une colonne identity, une séquence, un UUID.

Les clés naturelles se lisent bien dans les requêtes et évitent une jointure quand la table enfant n'a besoin que du code (ORDER BY country_code ne demande aucune recherche). Elles posent problème quand l'identifiant « immuable » se révèle modifiable, quand il est assez long pour alourdir chaque index qui le référence, ou quand un organisme externe le redéfinit.

Les clés techniques sont stables et compactes, c'est pourquoi elles sont le choix par défaut le plus courant. Utilisez une colonne identity générée par la base (GENERATED ALWAYS AS IDENTITY dans PostgreSQL et dans Oracle 12c ou ultérieur), sauf si vous devez créer les identifiants avant l'insertion ou fusionner des données issues de plusieurs systèmes : un UUID convient alors bien — un générateur d'UUID est pratique pour alimenter des lignes de test. La règle essentielle quand vous utilisez une clé technique : déclarez quand même une contrainte d'unicité sur la clé naturelle, sinon la table stockera sans broncher deux fois la même entité réelle.

Quel que soit votre choix, laissez la base faire respecter les règles. Les clés étrangères garantissent qu'une inscription ne peut pas référencer une session inexistante ; les contraintes NOT NULL et CHECK encodent des faits que la validation applicative laisse filer en cas d'accès concurrents ; UNIQUE écarte des doublons qu'aucun code applicatif ne saurait prévenir de façon fiable dès qu'il tourne sur deux serveurs. Choisissez le comportement à la suppression en connaissance de cause : ON DELETE CASCADE pour les lignes qui ne peuvent exister sans leur parent, RESTRICT pour tout ce qu'un humain devrait confirmer, SET NULL uniquement là où le null signifie réellement « n'est plus rattaché ». Indexez chaque colonne de clé étrangère, sinon la suppression d'un parent parcourt toute la table enfant — le guide sur les index et les plans d'exécution explique pourquoi.

Dénormaliser délibérément

Dénormaliser, c'est stocker sciemment un fait deux fois pour rendre une lecture moins coûteuse. C'est une technique légitime, avec une condition d'entrée : disposer d'un mécanisme qui garde les copies cohérentes.

Parmi les cas raisonnables : stocker un order_total calculé, maintenu par un trigger ou par la transaction même qui écrit les lignes de commande ; recopier le prix historique sur une ligne de commande, ce qui n'est pas vraiment de la redondance puisque le prix actuel du produit est un autre fait ; entretenir une vue matérialisée ou une table de synthèse pour un tableau de bord ; et les schémas en étoile de l'analytique, dont les tables de dimensions sont volontairement dénormalisées parce que les données y sont chargées, pas modifiées — voir business intelligence.

Les cas déraisonnables ont un point commun : rien ne garde la copie exacte. La ville d'un client recopiée sur chaque commande « pour le reporting », que personne ne met à jour, devient insidieusement une fiction.

Signaux d'alerte dans un schéma

• Entité-attribut-valeur. Une table (entity_id, attribute_name, value) promet une flexibilité infinie et livre des données non typées, des règles impossibles à contraindre et des auto-jointures pour chaque requête. Réservez-la aux champs réellement définis par l'utilisateur, et préférez une colonne jsonb assortie d'une contrainte CHECK de validation.
• La table trop large. Quatre-vingts colonnes, pour la plupart nulles sur la plupart des lignes : c'est en général plusieurs entités qui partagent une même table, ou une hiérarchie d'héritage aplatie sans réflexion.
• Tout est nullable. Si presque toutes les colonnes acceptent null, le schéma a cessé de documenter le domaine. Pour chaque colonne nullable, la signification de son null devrait être explicite.
• Des booléens qui devraient être des états. is_active, is_cancelled et is_archived sur une même ligne peuvent encoder des combinaisons impossibles. Une seule colonne status avec une contrainte CHECK, non.
• Des groupes de colonnes répétés. address_line_1 à address_line_5, ou q1_total à q4_total, ce sont des lignes qui se font passer pour des colonnes.
• Pas de clés étrangères « pour les performances ». Une clé étrangère coûte une recherche dans un index par écriture ; des données orphelines imposent une taxe permanente à chaque requête et à chaque développeur, et privent en plus l'optimiseur d'informations, comme l'explique le guide des bonnes pratiques SQL.

Lorsqu'un ORM vient se greffer par-dessus, le schéma reste prioritaire : les mappings d'entités doivent décrire une bonne conception relationnelle, pas la dicter — un point traité dans le mapping des entités JPA.

Points clés à retenir

• Modélisez d'abord les entités et les associations, puis écrivez les dépendances fonctionnelles : la redondance découle des dépendances, ce sont donc elles qui indiquent où découper.
• La 1FN supprime les groupes répétitifs, la 2FN les dépendances partielles vis-à-vis d'une clé composite, la 3FN les dépendances transitives, et la FNBC exige que tout déterminant soit une clé candidate.
• La redondance se manifeste par des anomalies de mise à jour, d'insertion et de suppression — des défaillances concrètes, pas des questions de style.
• Les clés techniques sont le choix stable par défaut, mais conservez toujours une contrainte d'unicité sur la clé naturelle pour que les doublons ne puissent pas entrer.
• Laissez les contraintes faire respecter le modèle : clés étrangères avec des règles de suppression choisies, NOT NULL, CHECK et UNIQUE.
• Ne dénormalisez que pour une requête identifiée, après mesure, et uniquement avec un mécanisme qui garde la valeur dupliquée exacte.