Modéliser des données : du besoin au modèle relationnel

Modéliser des données, c'est décider, avant de créer la moindre table, quelles informations un système doit retenir, comment elles se rattachent les unes aux autres et quelles règles elles respectent. Cette leçon s'adresse à qui sait déjà lire quelques requêtes SQL et veut concevoir un modèle relationnel simple, puis le justifier. Elle vaut pour tous les moteurs, PostgreSQL comme MySQL. Le fil rouge est un suivi d'incidents sur un parc de serveurs. Vous verrez les entités et leurs cardinalités, les clés, les trois premières formes normales, le moment où l'on dénormalise à bon escient, puis ce qui change entre modèle logique et modèle physique.

Ce que vous allez apprendre

• Décomposer un besoin en entités, attributs et relations, avec leurs cardinalités
• Lire et produire un diagramme entité-association
• Choisir une clé primaire et dire ce que garantit une clé étrangère
• Repérer une violation de 1NF, 2NF ou 3NF et la corriger
• Décider de dénormaliser, en connaissant le prix de ce choix
• Distinguer ce qui dépend du moteur dans un modèle

Quels prérequis, et où s'écrit le SQL ?

La formation SQL est le prérequis : vous savez ce qu'est une table, une ligne et une colonne, et vous avez déjà lu un schéma avec Lire le schéma d'une base PostgreSQL. Celui des tables servers et alerts que vous y avez lu est le résultat du même raisonnement, sur le même domaine d'infrastructure, avec des identifiants anglais. Cette leçon n'écrit aucune syntaxe SQL : elle enseigne comment concevoir un modèle, la formation SQL enseigne comment écrire les tables, notamment dans Contraintes et évolution du schéma SQL.

Qu'est-ce qu'un modèle de données, et pourquoi ne pas commencer par les tables ?

Un modèle de données décrit les informations qu'un système conserve, leurs liens et leurs règles, indépendamment de tout stockage. Dans une base relationnelle (voir Bases relationnelles), ces informations s'organisent en tables dont chaque ligne décrit une chose et chaque colonne une de ses propriétés. Le modèle répond à « que faut-il retenir, et comment cela se tient-il ? » avant « comment l'écrire ? ».

Créer les tables en premier est tentant, mais une erreur de conception se paie ensuite dans chaque requête, chaque écran et chaque migration (le changement de structure d'une base déjà remplie). Faire évoluer une table pleine est une opération qui verrouille, qui échoue si les données violent la règle ajoutée, et que la leçon Contraintes et évolution du schéma SQL mesure. Un modèle relu avant le premier enregistrement coûte une discussion ; le même défaut découvert en production coûte une migration.

Comment repérer entités, attributs et relations ?

On part d'une phrase du besoin : « chaque incident touche un serveur, est traité par un technicien et peut dégrader plusieurs applications ». Les noms désignent des candidats pour les entités, les propriétés deviennent des attributs, les verbes des relations.

• Une entité est une catégorie de choses qu'on veut suivre, avec une existence propre et plusieurs occurrences : le serveur, l'incident, le technicien.
• Un attribut est une propriété d'une entité : le nom d'hôte d'un serveur, la date d'ouverture d'un incident.
• Une relation (ou association) est un lien entre entités : un technicien traite des incidents.

Un test départage entité et attribut : si la chose a ses propres propriétés ou est partagée par plusieurs autres, c'est une entité. Le « site » d'un serveur n'est qu'un texte tant qu'on ne retient que son nom ; dès qu'il faut sa ville ou son adresse, plusieurs serveurs s'y rattachent et il devient une entité à part.

Quelles cardinalités entre deux entités ?

La cardinalité dit combien d'occurrences d'un côté se rattachent à une occurrence de l'autre. Elle se pose en deux questions, une par sens, et son minimum compte autant que son maximum : « un site héberge combien de serveurs ? », puis « un serveur est hébergé par combien de sites ? ». Un serveur sans incident est normal (minimum zéro), un incident sans serveur ne l'est pas (minimum un).

Type	Exemple du parc	Où se range le lien
un-à-un	un serveur et sa carte de gestion à distance (BMC), qui permet de l'administrer sans y être connecté	une clé étrangère d'un côté, rendue unique
un-à-plusieurs	un site héberge des serveurs ; un serveur subit des incidents	une clé étrangère du côté « plusieurs »
plusieurs-à-plusieurs	un incident touche plusieurs applications, une application subit plusieurs incidents	une table de liaison

Glissez pour voir

Deux termes reviennent sans cesse et méritent une définition avant la suite. Une clé primaire est la colonne, ou le groupe de colonnes, qui identifie chaque ligne d'une table. Une clé étrangère est une colonne qui reprend la clé primaire d'une autre table pour désigner l'une de ses lignes : c'est ainsi que le moteur retient un lien. La section sur les clés les reprend en détail.

Le un-à-un est rare : il isole des colonnes sensibles ou rarement lues. Si deux entités naissent et meurent toujours ensemble, une seule table suffit le plus souvent.

Pourquoi une relation plusieurs-à-plusieurs exige-t-elle une table de liaison ?

Une relation plusieurs-à-plusieurs ne tient dans aucune des deux tables : écrire la liste des applications dans une cellule, ou répéter des colonnes application_1, application_2, viole la première forme normale, définie plus bas. La table de liaison résout cela proprement : une ligne par couple (incident, application), avec une clé primaire composée des deux colonnes, chacune étant aussi une clé étrangère.

Elle n'est donc pas une complication inutile : c'est la forme qui s'interroge dans les deux sens et qui accueille les attributs du lien lui-même, comme la gravité de l'impact, qui n'appartient ni à l'incident ni à l'application.

incident	application	gravité
101	nginx	majeure
101	php-fpm	mineure
103	postgresql	majeure

Glissez pour voir

Comment lire et produire un diagramme entité-association ?

Un diagramme entité-association dessine les entités en boîtes, les relations en traits et leurs cardinalités en étiquettes. Il se lit relation par relation, à voix haute, dans les deux sens : « un site héberge zéro à N serveurs ; un serveur dépend d'un seul site ». Chaque étiquette du diagramme donne le minimum et le maximum du côté « plusieurs », comme « 0 à N » : un site peut n'héberger aucun serveur, et un incident peut être ouvert avant qu'on sache quelles applications il touche. Le côté « un » se lit dans la clé étrangère, toujours renseignée dans ce modèle : chaque serveur a exactement un site.

Plusieurs notations existent (Merise, pattes d'oie, UML) et disent la même chose ; le diagramme de cette page note la relation et ses bornes en clair, et la constance compte plus que la notation : choisissez-en une et tenez-vous-y.

Dans le schéma ci-dessous, chaque boîte est une table avec ses colonnes ; les repères PK et FK marquent les clés primaires et étrangères. Chaque trait part du côté « un » vers le côté « N ». Regardez la table impacts : ses deux premières colonnes sont à la fois PK et FK, c'est la table de liaison entre incidents et applications.

Source · SVG

Échap pour fermer, + et − pour zoomer, défilement pour se déplacer. 100 % − Ajuster + Fermer

Pour en produire un, procédez en quatre temps :

1. Relevez les noms du besoin et gardez ceux qui ont des propriétés propres : ce sont les entités.
2. Listez leurs attributs et repérez celui qui identifie chaque occurrence.
3. Posez les deux questions de cardinalité pour chaque verbe reliant deux entités.
4. Traduisez : clé étrangère côté « plusieurs », table de liaison pour chaque plusieurs-à-plusieurs.

Quelles clés choisir : primaire, candidate, étrangère ?

Une clé candidate est un ensemble minimal de colonnes qui identifie chaque ligne : pour un serveur, le nom d'hôte ou le numéro de série conviennent. On en retient une comme clé primaire, qui doit être unique, jamais vide et stable dans le temps. La clé étrangère, définie plus haut, est ce qui matérialise une relation un-à-plusieurs.

Le schéma suivant montre la mécanique sur un autre domaine, des clients et leurs commandes. Regardez la colonne client_id de commandes : c'est la clé étrangère, et elle ne peut contenir que des valeurs de la clé primaire id de clients.

Source · SVG

Échap pour fermer, + et − pour zoomer, défilement pour se déplacer. 100 % − Ajuster + Fermer

Clé naturelle ou clé technique ?

Une clé naturelle vient du monde réel (le nom d'hôte), une clé technique (ou substitut) est un numéro généré qui ne signifie rien. Aucune des deux ne gagne partout : le compromis réel tient dans ce tableau, sur la stabilité et la lisibilité.

	Clé naturelle (nom d'hôte)	Clé technique (identifiant généré)
Lisible dans les tables liées	oui	non, il faut une jointure
Si le serveur est renommé	toutes les clés étrangères sont à répercuter	rien ne change
Taille	variable, souvent large	petite et fixe
Risque propre	la règle métier d'unicité peut évoluer	des doublons passent si l'unicité du nom n'est pas aussi exigée

Glissez pour voir

L'usage courant combine les deux : une clé technique comme clé primaire pour sa stabilité, et une contrainte d'unicité sur la clé naturelle pour garder la règle métier. La clé technique ne dispense pas de chercher ce qui identifie vraiment une chose.

À quoi sert l'intégrité référentielle ?

L'intégrité référentielle est la garantie, appliquée par le moteur, que chaque valeur de clé étrangère désigne une ligne existante (ou reste vide quand la relation est facultative). Elle ne tient que si la clé étrangère est déclarée à la base : sans déclaration, la colonne n'est qu'un nombre que rien ne contrôle.

Sans elle, des lignes orphelines apparaissent : un incident qui pointe vers un serveur retiré de l'inventaire. Les jointures internes perdent alors ces lignes sans erreur, et les chiffres de reporting deviennent faux sans qu'aucune alerte ne sonne. Un contrôle dans l'application ne suffit pas : un script d'import, une seconde application ou deux sessions concurrentes le contournent, alors que le moteur, lui, est le point de passage obligé.

Reste une décision de modélisation : que faire des lignes dépendantes quand on supprime la ligne référencée ? Les trois réponses courantes sont refuser, supprimer en cascade ou mettre à vide. Pour l'historique des incidents, on refuse : supprimer un serveur ne doit pas effacer sa trace. L'écriture de ce choix est détaillée dans Contraintes et évolution du schéma SQL.

Qu'est-ce qu'une forme normale ?

Une forme normale est une règle de conception qui interdit un type précis de redondance ; la normalisation consiste à décomposer les tables jusqu'à les respecter. La redondance, c'est un même fait écrit à plusieurs endroits, et elle provoque trois anomalies : de mise à jour (une copie oubliée), d'insertion (impossible d'enregistrer un fait sans un autre) et de suppression (un fait perdu en effaçant un autre). Les formes sont cumulatives : la 2NF suppose la 1NF, la 3NF la 2NF.

Un outil de lecture sert pour les trois : une colonne a une dépendance (dite fonctionnelle) envers une autre si connaître la seconde détermine la première ; le numéro d'un incident détermine ainsi son serveur. Chaque colonne doit dépendre « de la clé, de toute la clé et de rien que la clé ». La 1NF vérifie le premier point, la 2NF le deuxième, la 3NF le troisième. D'autres formes existent (BCNF, 4NF) pour des cas rares ; les trois premières suffisent pour un modèle courant.

Tous les exemples partent de cette table plate de suivi, où chaque ligne est un incident :

num_incident	serveur	site	ville_site	applications	technicien	equipe
101	web-01	DC-Lyon	Lyon	nginx, php-fpm	Awa Diallo	Systèmes
102	web-01	DC-Lyon	Lyon	nginx	Awa Diallo	Systèmes
103	db-02	DC-Paris	Paris	postgresql	Karim Benali	Bases de données

Glissez pour voir

1NF : une seule valeur par cellule

La première forme normale exige une valeur atomique par cellule (pas de liste), aucun groupe de colonnes répétées (comme application_1, application_2, application_3, qui fixent une limite arbitraire) et des lignes identifiables. La colonne applications la viole avec « nginx, php-fpm ». Conséquence : « quels incidents ont touché php-fpm ? » oblige à fouiller du texte, « nginx, php-fpm » et « php-fpm, nginx » diffèrent pour le moteur, et rien ne garantit que chaque nom désigne une application réelle.

La correction donne une ligne par couple (incident, application), et la clé composée devient (num_incident, application). Pour que chaque nom désigne une application réelle, ces noms se rangent aussi dans une table des applications à laquelle le couple fait référence ; le tableau garde le nom en clair pour rester lisible :

num_incident	application	serveur	site	ville_site	technicien	equipe
101	nginx	web-01	DC-Lyon	Lyon	Awa Diallo	Systèmes
101	php-fpm	web-01	DC-Lyon	Lyon	Awa Diallo	Systèmes
102	nginx	web-01	DC-Lyon	Lyon	Awa Diallo	Systèmes
103	postgresql	db-02	DC-Paris	Paris	Karim Benali	Bases de données

Glissez pour voir

2NF : toute la clé

La deuxième forme normale demande que chaque colonne hors clé dépende de toute la clé ; elle ne se joue qu'avec une clé composée, formée de plusieurs colonnes. Ici serveur, site, technicien dépendent de num_incident seul, c'est-à-dire d'une partie de la clé : c'est une dépendance partielle. L'incident 101 répète web-01 sur deux lignes ; en corriger une seule donnerait un incident sur deux serveurs à la fois. Deux autres anomalies se cachent ici : on ne peut pas enregistrer un incident qui n'a touché aucune application, puisque l'application fait partie de la clé, et effacer la dernière application de l'incident 101 ferait perdre aussi son serveur et son technicien. L'anomalie d'insertion ne se contourne pas : aucune discipline applicative ne permet d'enregistrer un fait que la structure refuse.

On sépare ce qui dépend de l'incident (serveur, site, ville_site, technicien, equipe) de la liste des applications touchées, qui garde son couple de clés. Une table dont la clé tient en une seule colonne respecte la 2NF dès qu'elle respecte la 1NF. La table des incidents devient :

num_incident	serveur	site	ville_site	technicien	equipe
101	web-01	DC-Lyon	Lyon	Awa Diallo	Systèmes
102	web-01	DC-Lyon	Lyon	Awa Diallo	Systèmes
103	db-02	DC-Paris	Paris	Karim Benali	Bases de données

Glissez pour voir

3NF : rien que la clé

La troisième forme normale interdit qu'une colonne hors clé dépende d'une autre colonne hors clé, ce qu'on appelle une dépendance transitive. Ici serveur détermine site puis ville_site, et technicien détermine equipe : ces faits concernent le serveur et le technicien, pas l'incident. Les trois anomalies apparaissent :

• mise à jour : Awa Diallo passe à l'équipe Réseau, et il faut corriger chacune de ses lignes, sinon elles se contredisent ;
• insertion : impossible d'enregistrer le nouveau serveur db-03 de DC-Paris tant qu'aucun incident ne le concerne ;
• suppression : effacer l'incident 103 fait disparaître le fait que db-02 est à Paris.

Chaque fait n'est écrit qu'une fois en décomposant en incidents, serveurs, sites, techniciens, applications et impacts. Regardez le schéma : à gauche la table plate, à droite le résultat, et sur chaque flèche la ou les formes normales qui ont provoqué la séparation de la table d'arrivée.

Source · SVG

Échap pour fermer, + et − pour zoomer, défilement pour se déplacer. 100 % − Ajuster + Fermer

Ce sont les six tables du diagramme entité-association plus haut, qui n'y ajoute que la date d'ouverture de l'incident et la gravité de l'impact. Les colonnes changent de nom en route : num_incident devient l'identifiant de incidents, serveur le nom de serveurs, site et ville_site le nom et la ville de sites, et chaque clé étrangère (site_id, serveur_id, technicien_id) remplace la valeur recopiée.

Faut-il toujours viser la 3NF ? La dénormalisation comme décision

Non : la 3NF est le point de départ par défaut, parce qu'elle garantit qu'un fait n'existe qu'une fois, mais dénormaliser, c'est-à-dire réintroduire volontairement de la redondance, reste un choix légitime. Ce n'est ni une faute ni une astuce : c'est un échange qu'on décide en connaissant les deux plateaux.

Ce que la dénormalisation achète	Ce qu'elle coûte
Moins de jointures, une lecture plus directe	Chaque copie est à mettre à jour partout, sinon les valeurs divergent
Des valeurs précalculées (nombre d'incidents par serveur)	Des écritures plus lourdes, du code ou des déclencheurs (routines exécutées par le moteur à chaque écriture) pour synchroniser
Des requêtes de reporting plus simples	Le retour des anomalies de mise à jour, d'insertion et de suppression

Glissez pour voir

Un cas n'est pas une dénormalisation : l'équipe du technicien au moment de l'incident est un fait historique, distinct de son équipe actuelle. La stocker dans l'incident ne copie rien, elle conserve un autre fait.

Dénormalisez quand les lectures dominent et qu'une jointure mesurée est le goulot, jamais par principe. Essayez d'abord un index adapté, c'est-à-dire une structure annexe qui accélère la recherche sur une colonne (voir Index et EXPLAIN), ou une vue matérialisée, qui stocke le résultat d'une requête et que certains moteurs proposent. Si vous dénormalisez, désignez la source de vérité, la copie et le mécanisme de resynchronisation ; mettre à jour les deux dans la même transaction, validée en entier ou pas du tout, évite qu'une copie change sans l'autre (voir Transactions et concurrence). Les bases NoSQL poussent cette logique plus loin : peu ou pas de jointures, des données rangées pour la lecture visée.

Modèle logique ou modèle physique : qu'est-ce qui dépend du moteur ?

Un modèle se construit à trois niveaux, du plus abstrait au plus proche du moteur. Le modèle conceptuel dit quelles entités et relations existent, dans le vocabulaire du métier. Le modèle logique en fait des tables, colonnes et clés normalisées, sans choisir de moteur. Le modèle physique ajoute tout ce qui dépend du moteur choisi.

Niveau	Contient	Dépend du moteur ?
Conceptuel	entités, relations, cardinalités	non
Logique	tables, colonnes, clés, formes normales	non
Physique	types exacts, index, partitions, stockage	oui

Glissez pour voir

Le diagramme entité-association de cette leçon est un modèle logique : les types qu'il affiche (int, varchar) sont indicatifs. Passer au physique oblige à trancher des points propres au moteur : le type exact d'une date avec fuseau, la façon de générer l'identifiant, les types d'index disponibles, le partitionnement (découper une grosse table en morceaux) et le stockage. Pour choisir le moteur, voir Choisir un moteur relationnel.

Un index, vu plus haut, accélère la lecture mais chaque écriture doit aussi le mettre à jour : il a un coût en écriture. On indexe donc ce que les requêtes cherchent, colonnes de clé étrangère comprises (PostgreSQL 18, MySQL 8.4) : MySQL (InnoDB) en crée un seulement si aucun index utilisable n'existe déjà sur cette colonne, PostgreSQL n'en crée jamais sur la colonne qui référence (la clé primaire de la table référencée est, elle, toujours indexée). Le geste est détaillé dans Index et EXPLAIN.

À retenir

• Un modèle se conçoit avant les tables : une erreur de conception se paie dans chaque requête et chaque migration.
• Les noms du besoin donnent les entités, les verbes les relations ; deux questions de cardinalité, une par sens.
• Un plusieurs-à-plusieurs passe par une table de liaison, qui porte aussi les attributs du lien.
• Une clé technique apporte la stabilité ; la clé naturelle garde sa contrainte d'unicité.
• L'intégrité référentielle empêche les lignes orphelines, mais seulement si la clé étrangère est déclarée au moteur.
• Pour repérer une violation : 1NF une valeur par cellule, 2NF toute la clé, 3NF rien que la clé.
• Dénormaliser est un échange : lecture plus simple contre cohérence et écritures plus coûteuses, à décider sur mesure.
• Le modèle logique ne dépend pas du moteur ; types, index et partitions relèvent du physique.

Contrôle de connaissances

Dix questions sur le passage d'un attribut à une entité, le minimum d'une cardinalité, la table de liaison, le choix entre clé naturelle et clé technique, ce que garantit l'intégrité référentielle, les trois formes normales, le prix de la dénormalisation et la frontière entre modèle logique et modèle physique.

Contrôle de connaissances

Validez vos connaissances avec ce quiz interactif

10 questions

8 min.

70% requis

Informations

• Le chronomètre démarre au clic sur Démarrer
• Questions à choix multiples, vrai/faux et réponses courtes
• Vous pouvez naviguer entre les questions
• Les résultats détaillés sont affichés à la fin

Démarrer le quiz

Lance le quiz et démarre le chronomètre

×

📖 Voir la documentation →