Comment faire une Base de Données proprement...

Si tu es jeune, tu te poses des questions existentielles qui ramènent Spinoza aux conversations de pochtrons qui se terminent à l'alcool de bois à 5 h du mat' au Macumba.

Si tu es vieux (comme moi), tu as vu tellement d'horreurs qui feraient passer l'Enfer de Dante pour le dernier film de Bob l'Eponge.

Alors, comme faire ?

Rassure-toi, point n'est besoin de se farcir l'Algèbre Relationnelle d'Edgar Codd (1).

Pas plus que des diagrammes UML (2), mais ça viendra après.

On a dû vous parler des Formes Normales, surtout les 3 premières, mais, connaissez-vous celle de Boyce-Codd ? la 4ème ? la 5ème ? Les définitions formelles sont assez obscures.

Alors, je vais vous parler plutôt des Dépendances Fonctionnelles (3) (4), et surtout de graphes de dépendances fonctionnelles, car c'est une approche simple et sûre pour modéliser une base de données.

Car arriver à une base de données normalisée à partir d'un schéma UML me semble franchement difficile.
Il est bien sûr très intéressant de raisonner de manière abstraite dans un premier temps, et un schéma d'objet est tout à fait approprié.
Quand on arrive à la phase plus concrète d'implémentation de schéma base de données, on se retrouve vite avec des redondances vicieuses bien cachées qui sont de véritables bombes à retardement.

Il vaut mieux partir d'une base de données normalisées et introduire des redondances après les avoir mûrement réfléchies que se retrouver des années plus tard avec une BdD complètement pourrie et bricoler du code pour corriger les données...

Les Dépendances Fonctionnelles

Et avant tout, qu'est-ce qu'une dépendance fonctionnelle ?

Avec des mots simples que peuvent comprendre les gens normaux que nous sommes qui se promènent avec des Santiag ou des Doc Marten's et qui écoutent de la musique avec des vrais instruments, c'est ceci :

"Si une caractéristique A permet de déterminer une autre caractéristique B, alors il y a une dépendance fonctionnelle entre A et B que l'on notera A --> B."

Prenons un exemple tout simple avec une plaque d'immatriculation et un véhicule.

Qui de la poule et l'œuf était avant ?... le dinosaure !

Euh... est-ce que c'est le véhicule qui permet de connaître la plaque ?
Si je vous dis "Fiat 500 Abarth rouge", arriverez-vous à trouver la plaque d'immatriculation, sachant qu'il y a une foule de Fiat 500 de ce modèle et cette couleur ?

Ne serait-ce pas plutôt la plaque d'immatriculation qui me permet d'identifier, de retrouver "LA Fiat 500 Abarth rouge" bien particulière que je recherche... ?

Plaque Immatriculation --> Véhicule

Autre exemple : N° INSEE (ou n° de sécurité sociale) et Personne.

Est-ce le fait de connaître Pierre Martin me permet de déterminer facilement son n° INSEE ?
J'en doute...

Par contre, si j'ai le n° INSEE "1 65 05 13 155 666", je retrouve bien LE Pierre Martin que je recherche.

N° INSEE --> Personne

Précisons maintenant la limite des exemples précédents, il s'agissait de vulgariser le concept, et dans les faits, on va réfléchir un peu plus finement, car dans la théorie des Bases de Données Relationnelles que je vous dispense de dévorer pour justement vous simplifier la vie la définition des "caractéristiques" est très précise.

Je vais donc vous proposer en apéro (ouais !!!) sans alcool (...ooooh...) un petit exercice de modélisation qui permettra d'arriver à un graphe de dépendances fonctionnelles, et de là, la définition d'une entité de base de donnée coulera de source.

Voici l'exercice : "Comment modéliser une personne ?", soit plus simplement :

Comment représenter abstraitement une personne ?
Quelles sont les informations qui caractérisent une personne ?
Et en particulier, quelles sont les informations qui m'intéressent pour définir une personne ?
Car je ne vais pas avoir besoin des mêmes infos si je gère une salle de sport, ou un club de musique. C'est un point important à garder dans le coin de la tête, mais...

Restons très simple.

Une personne a un prénom, un nom, est née quelque part à une date bien précise.

Contentons nous de ceci:

PRENOM,
NOM,
DATE DE NAISSANCE,
LIEU DE NAISSANCE,

Est-ce que ceci est suffisant pour définir précisément une personne ? D'un point de vue abstrait bien, n'entrons pas dans des considérations philosophiques, complexes !

Voici les 5 premiers noms de famille les plus répandus en France :

Martin : 250 013
Bernard : 131 330
Thomas : 118 331
Petit : 115 217
Robert : 112 998

Et voici les prénoms :

Marie 2 231 347
Jeanne 564 247
Françoise 401 427
Monique 399 616
Catherine 394 699

Jean 1 911 457
Pierre 892 384
Michel 820 560
André 712 248
Philippe 538 712

Il risque d'y avoir de fortes probabilités pour trouver plus d'une "Marie Bernard" et plus d'un "Pierre Martin"...

C'est bien pour ça qu'a été inventé le n° INSEE qui est unique et attribué à une seule et unique personne. Il faut rajouter cette information à notre liste:

N° INSEE,
PRENOM,
NOM,
DATE DE NAISSANCE,
LIEU DE NAISSANCE,

En reprenant la notation des dépendances fonctionnelles "A --> B", on a ceci :

N° INSEE --> PRENOM,
N° INSEE --> NOM,
N° INSEE --> DATE DE NAISSANCE,
N° INSEE --> LIEU DE NAISSANCE,

Regardez donc votre compte Ameli et celui de quelqu'un d'autre.

Votre n° INSEE détermine de manière UNIQUE votre nom, votre prénom, votre date de naissance, votre lieu de naissance, pas les informations de l'autre personne.

Si l'on poursuit la réflexion sur ce petit exemple, qu'est-ce qu'un lieu de naissance ?

C'est une commune.

Oui, mais il y a :

13 Saint-Sauveur
12 Sainte-Colombe
11 Beaulieu

Donc la commune ne suffit pas, il faut préciser le département pour faire la différence entre Mars dans la Loire (42) et Mars dans l'Ardèche (07).

Oui, en France, on peut aller sur Mars. Deux fois... Elon Musk en rêve...

Voici notre liste mise à jour :

N° INSEE --> PRENOM,
N° INSEE --> NOM,
N° INSEE --> DATE DE NAISSANCE,
N° INSEE --> COMMUNE DE NAISSANCE,
N° INSEE --> DEPARTEMENT DE NAISSANCE,
N° INSEE --> PAYS DE NAISSANCE

Et on peut ajouter le pays de naissance.

Représentation graphique avec le Graphe des D.F.

Le graphe des dépendances fonctionnelles se dessine comme suit :

Et... de ce graphe à la définition d'une entité "Personne", il n'y a qu'un pas !

PERSONNE( N° INSEE , PRENOM, NOM, DATE DE NAISSANCE, COMMUNE DE NAISSANCE, DEPARTEMENT DE NAISSANCE, PAYS DE NAISSANCE)

Voici l'entité "PERSONNE", une conceptualisation, une définition abstraite d'une personne dans une système d'information, et plus particulièrement, dans une base de données.

L'identifiant est, vous vous en doutez, le N° INSEE.

Si vous ne voyez pas encore l'intérêt de passer par les Dépendances Fonctionnelles et le Graphe qui le représente, ça va sera peut-être plus clair avec l'exemple suivant.

On continue ? On continue ! 😺

On va manipuler maintenant des clients, des commandes, des articles, des fournisseurs.

Définition du client dans le contexte:

Un nom de société, un nom de contact, un numéro de téléphone principal, un numéro de téléphone mobile, une adresse mail, une adresse principale, plusieurs adresses de livraison

Définition des commandes dans le contexte:

Un client passe une commande à une date bien précise d'un ou plusieurs articles.

La commande sera livrée à une adresse de livraison.

Définition des articles dans le contexte:

Un article a un nom court mais clair, une description plus détaillée, un prix à une certaine date, il est fabriqué par un fournisseur.

Définition des fournisseurs dans le contexte:

Un nom de société, un nom de contact, un numéro de téléphone principal, un numéro de téléphone mobile, une adresse mail, une adresse principale, plusieurs adresses d'approvisionnement

Ca va ? Pas trop les chocottes ?

Bien !

Alors...

On attaque !

La modélisation du client.

un nom de société,
un nom de contact,
un numéro de téléphone principal,
un numéro de téléphone mobile,
une adresse mail,
une adresse principale,
plusieurs adresses de livraison

La question de l'identification du client se pose comme celle de la personne dans le petit exercice précédent.

Donc, sans plus attendre, on va parler de "code client".

Ca, c'est fait.

On a les D.F. suivantes :

CODE_CLIENT --> NOM_SOCIETE
CODE_CLIENT --> NOM_CONTACT

On peut se poser des questions sur les numéros de téléphones.

Est-ce qu'il y aura seulement un numéro principal et un numéro de mobile ?
Est-ce qu'il n'y aurait pas plus de numéros que ça ?

Nous allons choisir l'hypothèse simple de 2 numéros et rien de plus. Voici les D.F. qui suivent :

CODE_CLIENT --> TELEPHONE_PRINCIPAL
CODE_CLIENT --> TELEPHONE_MOBILE
CODE_CLIENT --> MAIL

Que faire avec l'adresse principale et les adresses de livraison ?

On pourrait penser à une dépendance fonctionnelle entre le CODE_CLIENT et les éléments qui constituent l'adresse principale.
Il y a plusieurs adresses de livraison. Plusieurs, cela sous-entend un ensemble possible de 1 à "n" adresses de livraison, "n" pouvant être 2, 3, 4, 5, ...

On comprend vite qu'il ne peut y avoir de D.F. directes entre le CODE_CLIENT et les adresses de livraison.

Et comme modéliser, c'est rationaliser et prévoir, on se doute de l'émergence d'une entité "Adresses du client".

Voici le graphe des D.F. du client :

La modélisation des adresses

Comment déterminer une adresse client ?

Avec le CODE_CLIENT, bien sûr. Et comme il y en a plusieurs, il y a aussi un numéro d'adresse.

Donc CODE_CLIENT et NUMERO_ADRESSE déterminent une adresse d'un client.

Qu'est-ce qui définit une adresse en plus de ces notions ?

le TYPE_ADRESSE (principale, livraison), et selon l'AFNOR :
DENOMINATION
DESTINATAIRE
BATIMENT
VOIE
MENTION_SPECIALE
CODE_POSTAL
COMMUNE
CEDEX
LIBELLE_CEDEX

On a donc les D.F. suivantes :

CODE_CLIENT, NUMERO_ADRESSE --> TYPE_ADRESSE
CODE_CLIENT, NUMERO_ADRESSE --> DENOMINATION
CODE_CLIENT, NUMERO_ADRESSE --> DESTINATAIRE
CODE_CLIENT, NUMERO_ADRESSE --> BATIMENT
CODE_CLIENT, NUMERO_ADRESSE --> VOIE
CODE_CLIENT, NUMERO_ADRESSE --> MENTION_SPECIALE
CODE_CLIENT, NUMERO_ADRESSE --> CODE_POSTAL
CODE_CLIENT, NUMERO_ADRESSE --> COMMUNE
CODE_CLIENT, NUMERO_ADRESSE --> CEDEX
CODE_CLIENT, NUMERO_ADRESSE --> LIBELLE_CEDEX

Jusqu'à présent, je vous ai épargné les aigreurs d'estomac avec les formes normales, ce n'est pas pour autant que je vous accorde un permis de tuer les Bases de Données !

Un peu de bon sens, même sans parler de la forme de Boyce-Codd, nous permet de trouver des petits grains de sable.

La présence de "COMMUNE" dans l'entité ADRESSE_CLIENT n'est-elle pas une magnifique redondance ?

Comment reconnaît-on une commune ? Grâce à son code postal.

On trouve donc la D.F. : CODE_POSTAL --> COMMUNE,

Ce qui donne finalement :

CODE_CLIENT, NUMERO_ADRESSE --> TYPE_ADRESSE
CODE_CLIENT, NUMERO_ADRESSE --> DENOMINATION
CODE_CLIENT, NUMERO_ADRESSE --> DESTINATAIRE
CODE_CLIENT, NUMERO_ADRESSE --> BATIMENT
CODE_CLIENT, NUMERO_ADRESSE --> VOIE
CODE_CLIENT, NUMERO_ADRESSE --> MENTION_SPECIALE
CODE_CLIENT, NUMERO_ADRESSE --> CODE_POSTAL
CODE_CLIENT, NUMERO_ADRESSE --> CEDEX
CODE_CLIENT, NUMERO_ADRESSE --> LIBELLE_CEDEX

CODE_POSTAL --> COMMUNE

Graphe des D.F. des Clients, complété des Adresses et des Communes

La modélisation des commandes

Un client passe une commande à une date donnée, et elle contient un à plusieurs articles en précisant la quantité, par exemple 3 jeux de cordes D'addario 9-42 pure nickel.

Le prix figure sur la fiche de l'Article, certes, mais dans la commande, le prix peut être négocié, donc différent de celui de l'article.

Ce qui sous-entend qu'il y aura plus tard la modélisation des articles ???

Si, si, c'est vrai, alors, autant s'y atteler de suite.

L'interlude des Articles

Un article, c'est un nom court, un code pas trop ésotérique, compréhensif juste ce qu'il faut.

Et ensuite une description un peu plus étoffée.

Un prix à une date donnée. Hé oui, les prix changent au cours du temps. Il faut pouvoir connaitre le prix valable à la date d'une commande.

Et n'a-t-on pas parlé au début de cette étude de cas de la notion de "fournisseur" ?

Si, si, retourner voir un peu en haut...

Le méta-interlude des Fournisseurs...

Ami lecteur, tu commences à avoir l'habitude, ou bien ?

Un fournisseur, c'est un nom court, on l'espère assez compréhensible, puis un texte plus développé.

Et pour ne pas aller trop loin, une description libre encore plus grande pour noter l'adresse ou des infos diverses saisies librement. C'est une étude de cas, pas un projet de la Vraie Vie de la Réalité Réelle.

Allez, on décrit les D.F. des Articles et des Fournisseurs :

CODE_ARTICLE --> DESCRIPTION_ARTICLE

CODE_ARTICLE --> CODE_FOURNISSEUR

CODE_ARTICLE, DATE_TARIF --> TARIF_ARTICLE

CODE_FOURNISSEUR --> DESCRIPTION_FOURNISSEUR

Voici le Graphe de ces derniers éléments :

Revenons à nos moutons ...

Euh... aux commandes !

Donc, un client peut commander à une date donnée un ou plusieurs articles, on précise dans la commande la quantité de chaque article, et le prix négocié.

Le N° de commande est unique dans le système.

NUMERO_COMMANDE --> CODE_CLIENT

NUMERO_COMMANDE --> DATE_COMMANDE

Comment faire pour l'ensemble des articles d'une commande ?

Ce sont des lignes de commande...

NUMERO_COMMANDE, NUMERO_LIGNE --> CODE_ARTICLE

NUMERO_COMMANDE, NUMERO_LIGNE --> QUANTITE_ARTICLE

NUMERO_COMMANDE, NUMERO_LIGNE --> TARIF_NEGOCIE

Voici le graphe des commandes :

Et sous vos applaudissements, voici le graphe complet des D.F.

Et tout ça pour quoi ?

De ce graphe des D.F. se déduit immédiatement les entités.

Vous objecterez en me faisant remarquer que la définition formelle des D.F. suffisait et que point n'était besoin de faire des petits schémas de ce genre...

En êtes-vous si sûrs ?

Voici un cas un peu taquin. On va modéliser du vin avec les informations suivantes :

Le CRU, soit la zone de production du vin.
La REGION, une zone un peu plus large.
Le PAYS, zone encore plus large.
La QUALITE, appréciation selon les œnologues

Le Chablis est un CRU dans la REGION de Bourgogne, en France, de QUALITE qui peut être Excellente.

Le Brouilly est un CRU dans la REGION du Beaujolais, en France, de Bonne QUALITE.

Sauf que... On trouve du Chablis et du Brouilly californien. Et aussi du Cabernet Sauvignon.

CRU	REGION	PAYS	QUALITE
Brouilly	Beaujolais	France	Bon
Brouilly	Californie	USA	Excellente
Cabernet Sauvignon	Bordeaux	France	Excellente
Cabernet Sauvignon	Californie	USA	Excellente
Chablis	Bourgogne	France	Excellente
Chablis	Californie	USA	Bon

Quelles sont les D.F. ?

CRU, PAYS --> REGION

CRU, PAYS --> QUALITE

REGION --> PAYS

Ce qui nous fait un graphe... étonnant

L'exemple est simple, avec seulement 3 D.F., on pourrait repérer l'étrangeté.

Mais dans une liste plus longue, cela passerait inaperçu.

Vous voyez un schéma qui ne respecte pas la forme de Boyce-Codd.

On a une clé (REGION) qui a pour attribut PAYS qui est une partie de la clé (CRU, PAYS) qui détermine REGION.

On en revient à "qui de la poule ou l'œuf était avant".

En fait, le CRU ne détermine pas la REGION puisqu'on a du Cabernet aussi bien en Californie qu'à Bordeaux.

Mais la REGION détermine bien le PAYS.

Voici le vrai graphe :

Ne nous arrêtons pas en si bon chemin !

La table suivante respecte la 3ème forme normale.

La clé de cette table est (ELEVE, COURS, INSTRUMENT).

ELEVE	COURS	INSTRUMENT
Jimmy Page	Solfège	Guitare
Jimmy Page	Théorie	Guitare
John Bonham	Solfège	Batterie
John Bonham	Théorie	Batterie

F.N. = les champs sont atomiques, càd, il n'existe pas d'information complexe rassemblant plusieurs notions, comme par exemple ADRESSE qui serait composé de NUMERO, VOIE, CODE POSTAL, VILLE, ...
F.N. = tout attribut n'appartenant pas à une clé ne dépend d'une partie de la clé
F.N. = pas de transitivité, soit : tout attribut n'appartenant pas à une clé ne dépend pas d'un autre attribut non-clé.

On y est en plein. Et pourtant, on voit bien des redondances.

L'analyste a défini la BdD en ne réfléchissant que sur les définitions des 3 premières Formes Normales, et quand l'application est finie, on a une table ... moyenne...

Et c'est devenu pire quand 2 petits génies sont arrivés dans cette école :

ELEVE	COURS	INSTRUMENT
Jimmy Page	Solfège	Guitare
Jimmy Page	Théorie	Guitare
John Bonham	Solfège	Batterie
John Bonham	Théorie	Batterie
Prince	Solfège	Batterie
Prince	Solfège	Guitare
Prince	Solfège	Piano
Prince	Théorie	Batterie
Prince	Théorie	Guitare
Prince	Théorie	Piano
Stevie Wonder	Solfège	Piano
Stevie Wonder	Solfège	Batterie
Stevie Wonder	Solfège	Harmonica
Stevie Wonder	Théorie	Piano
Stevie Wonder	Théorie	Batterie
Stevie Wonder	Théorie	Harmonica

Ce que l'on vient de voir est le cas des dépendances multivaluées. Soit le non-respect de :

La 4ème Forme Normale

Pour respecter la 4FN, on aurait dû avoir 2 tables :

COURS_SUIVIS(ELEVE, COURS)

ELEVE	COURS
Jimmy Page	Solfège
Jimmy Page	Théorie
John Bonham	Solfège
John Bonham	Théorie
Prince	Solfège
Prince	Théorie
Stevie Wonder	Solfège
Stevie Wonder	Théorie

INSTRUMENTS_APPRIS(ELEVE, INSTRUMENT)

ELEVE	INSTRUMENT
Jimmy Page	Guitare
John Bonham	Batterie
Prince	Batterie
Prince	Guitare
Prince	Piano
Stevie Wonder	Piano
Stevie Wonder	Batterie
Stevie Wonder	Harmonica

Les clés sont en GRAS et SOULIGNEES.

NON, ce n'est pas fini !

Poursuivons avec nos élèves :

MUSICIEN	INSTRUMENT	MARQUE
Eric Clapton	ES-335	Gibson
Eric Clapton	Firebird	Gibson
Eric Clapton	LesPaul	Gibson
Eric Clapton	SG	Gibson
Eric Clapton	Stratocaster	Fender
Eric Clapton	Telecaster	Fender
Jeff Beck	LesPaul	Gibson
Jeff Beck	Stratocaster	Fender
Jeff Beck	Telecaster	Fender
Jimmy Page	59DC	Danelectro
Jimmy Page	LesPaul	Gibson
Jimmy Page	Stratocaster	Fender
Jimmy Page	Telecaster	Fender

Les redondances sautent aux yeux, non ?

On est dans un cas de non-respect de :

La 5ème Forme Normale

La définition pique un peu les yeux :

"Pour toute relation de dimension n (avec n supérieur à 2) en quatrième forme normale, il ne doit pas être possible de retrouver l’ensemble de ses occurrences par jointure sur les occurrences des relations partielles prises deux à deux. Cette normalisation conduit parfois à décomposer une relation complexe en plusieurs relations plus simples."

Georges Gardarin l'appelle "Forme normale de projection jointure". Je vous fais grâce des détails qui viennent juste après, mais pour les plus matheux d'entre vous maîtrisant la Théorie des Ensembles, l'Algèbre Relationnel sera une promenade de santé, et vous pouvez aller jeter un œil sur le site du professeur Gardarin.

Avec un peu de bon sens, on peut déduire assez facilement comment éliminer les redondances de la table.

Il suffit de la décomposer en 3.

MUSICIEN	INSTRUMENT
Eric Clapton	ES-335
Eric Clapton	Firebird
Eric Clapton	LesPaul
Eric Clapton	SG
Eric Clapton	Stratocaster
Eric Clapton	Telecaster
Jeff Beck	LesPaul
Jeff Beck	Stratocaster
Jeff Beck	Telecaster
Jimmy Page	59DC
Jimmy Page	LesPaul
Jimmy Page	Stratocaster
Jimmy Page	Telecaster

INSTRUMENT	MARQUE
ES-335	Gibson
Firebird	Gibson
LesPaul	Gibson
SG	Gibson
Stratocaster	Fender
Telecaster	Fender
59DC	Danelectro

MUSICIEN	MARQUE
Eric Clapton	Gibson
Eric Clapton	Fender
Jeff Beck	Gibson
Jeff Beck	Fender
Jimmy Page	Danelectro
Jimmy Page	Gibson
Jimmy Page	Fender

Finalement...

Je vous ai amené au plus profond de l'Enfer de Dante.

Mais, progressivement.

L'intérêt d'aller au bout de la normalisation d'une Base de Données est de pouvoir maîtriser la dénormalisation plutôt que de la subir par la suite.

En fin de modélisation, on a un schéma idéal.

S'il est performant, on a tout gagné.

S'il ne l'est pas, on dénormalise progressivement pour atteindre le niveau de performances souhaité et cela se fait en connaissances de causes. Il ne faut pas être dogmatique, mais pragmatique.

Notes

(1) Edgar Codd : A relational model of data for large shared data banks

(2) Microsoft : Guide to UML diagramming and database modeling

(3) Définition formelle des DF

(4) Edgar Codd : FURTHER NORMALIZATION OF THE DATA BASE RELATIONAL MODE

mercredi 9 avril 2025