Normalisation

Normalisation

Normalisation est le processus de restructuration de nos entités et leurs attributs. Il y a plusieurs objectifs :

• la minimisation de redondance dans notre schéma

• la détection et résolution des problèmes de consistance

• ...

Nous faisons évoluer notre schéma de façon itérative, en passant par plusieurs phases de normalisation :

• 1NF – First normal form

• 2NF – Second normal form

• 3NF – Third normal form

• BCNF – Boyce-Cobbs normal form

Normalisation

1NF

Une entité est en 1NF s’il n’y a pas d’attribut composé ou multivaleur

Par exemple :

étudiant(id, nom, prénom, adresse)

L'entité étudiant n'est pas en 1NF parce que l'adresse peut être considérée comme « composé » de numéro, rue, city, département, région, pays, code postal.

Nous faisons évoluer le schéma de l'étudiant :

étudiant(id, nom, prénom, addr_numéro, addr_rue, addr_city, addr_dept, addr_region, addr_pays, addr_code)

1NF nous aide à se sécuriser pour le futur

Imaginons le cas suivant : on est en train de créer une appli pour grand public. Nos utilisateurs indiquent leur adresse, qui pour le moment est juste "indicatif". Donc au départ, on se dit, "pas la peine de se prendre la tête avec plusieurs colonnes pour chaque composant de l'adresse. Cela ne change rien pour l'utilisateur".

Sauf, après quelques mois de fonctionnement, on ajoute un service de livraison (qui n'était pas prévu au départ de projet). Il faut optimiser les livraisons par département. Mince ! Il serait difficile d'extraire l'info nécessaire, car le numéro de département et confondu dans l'adresse !

Un autre exemple. On a une entité qui représente nos étudiants et leurs cours.

étudiant(numéro, nom, cours)

numéro	nom	cours
1	Bonnetain	[ SGBDR, Backend ]
2	Laurendeau	[ Angular ]
3	Perrousset	[ Backend, Angular ]

On décide de lister les cours de chaque utilisateur dans un seul champ cours. Le type de l'attribut cours est un array des noms des cours. Donc, ici on a un problème de domaine.

Certes, on utilise un format structuré (pseudo-json). Cette table n'est pas en 1NF. La colonne cours est multi-valeur. Dans ce cas, il serait difficile d'éxtraire tous les étudiants du cours Backend, par exemple.

Il faut modifier le type (et donc restreindre le domaine) de l'attribut cours : on l'imite à un seul cours. Nos données se transforment donc ainsi :

numéro	nom	cours
1	Bonnetain	SGBDR
1	Bonnetain	Backend
2	Laurendeau	Angular
3	Perrousset	Backend
3	Perrousset	Angular

Cette table est maintenant en 1NF.

Il est toujours utile de tester nos entités avec des données d'échantillon ! On se rend compte des failles plus facilement !

2NF

Une entité est en 2NF si :

• elle est déjà en 1NF

• elle n’a pas de dépendance partielle, c'est-à-dire il n’y a pas d’attribut non-prime qui dépend d’un sous-set d’une clé candidate. En langage plus simple : il ne faut pas qu'il y a des attributs qui dépendent entièrement sur un sous-set de la clé primaire.

Exemple, on aimerait afficher le tarif des cours des étudiants :

étudiant(numéro, nom, cours, prix)

En échantillon des données possibles est :

numéro	nom	cours	prix
1	Bonnetain	SGBDR	1000
1	Bonnetain	Backend	1500
2	Laurendeau	Angular	2000
3	Perrousset	Backend	1500
3	Perrousset	Angular	2000

Ici, le prix dépend du cours, donc il y a une dépendance fonctionnelle cours ➔ prix.

• La seule clé candidate est { numéro, cours }

• prix est donc un attribut non-prime (il ne fait pas partie de la clé candidate)

• prix dépend d’un sous-set de la clé candidate (cours), et donc il y a une dépendance partielle

• alors cette table n’est pas en 2NF

En réalité, il est plutôt intuitif que cette représentation n'est pas idéal. Il suffit de se poser la question : "et si demain le prix d'un cours change ?". La réponse, je serais obligé de modifier beaucoup de lignes, alors que la modification devrait impacter une seule ligne.

Quelle est la solution ? Il faut couper notre entité en deux.

étudiant(numéro, nom, cours)
cours(cours, prix)

Exemple de la table étudiant :

numéro	nom	cours
1	Bonnetain	SGBDR
1	Bonnetain	Backend
2	Laurendeau	Angular
3	Perrousset	Backend
3	Perrousset	Angular

Exemple de la table cours :

cours	prix
SGBDR	1000
Backend	1500
Angular	2000

Est-ce que nos entités sont tous en 2NF ? Non ! Il existe encore une dépendance partielle !

• l'attribut nom (non-prime) dépend de numéro, et numéro fait partie de notre clé-candidate { numéro, cours }

Il faut encore couper en deux les entités :

étudiant(numéro, nom)
étudiant_cours(numéro, cours)
cours(cours, prix)

Exemple de la table étudiant :

numéro	nom
1	Bonnetain
2	Laurendeau
3	Perrousset

Exemple de la table étudiant_cours :

numéro	cours
1	SGBDR
1	Backend
2	Angular
3	Backend
3	Angular

Exemple de la table cours :

cours	prix
SGBDR	1000
Backend	1500
Angular	2000

3NF

Une entité est en 3NF si :

• elle est en 2NF

• Il n’y a pas de dépendance transitive pour les attributs non-primes

• Un attribut non-prime ne peut pas être dépendant d’un autre attribut non-prime

Qu'est-ce que c'est une dépendance transitive ?

Dans un set de relations fonctionnelles :

{ 
  A ➔ B
  B ➔ C 
} 

Une dépendance transitive existe : A ➔ C

Exemple d'une entité étudiant :

étudiant(id, nom, prénom, téléphone, département, région, pays, âge)

• elle est en 1NF, car il n'y a pas d'attribut composé ou multivaleur

• elle est en 2NF parce qu'il n'y a pas de dépendance partielle (la clé candidate est id uniquement)

• elle n'est pas en 3NF parce qu'il existe une dépendance transitive !

Pourquoi ?

Parmi les dépendances fonctionnelles, on trouve :

id ➔ département
département ➔ région
région ➔ pays

Il y a donc une dépendance transitive entre id et pays !

La solution, encore une fois, est de couper l'entité en deux :

étudiant(id, nom, prénom, téléphone, département, région, âge)
région_pays(région, pays)

Est-ce qu'on a atteint la 3NF ? Pas encore ! Il y a toujours des dépendances transitives à résoudre :

étudiant(id, nom, prénom, téléphone, département, âge)
département_région(département, région)
région_pays(région, pays)

Au-delà de 3NF

D’autres formes normalisées qui ajoutent de contraintes sur les données :

• BCNF : boyce-codd normal form (une forme plus restreinte de 3NF)

• 4NF

• 5NF

• etc..

Je vous laisse étudier ces formes plus strictes, par exemple ici

Dénormalisation

Plus on normalise, plus on serait obligé de faire les jointures de table pour récupérer les données :

• Requêtes SQL complexes

• Exécution plus lente

En plus, le passage en BCNF pourrait aussi casser des relations fonctionnelles.

Le processus de dé-normalisation introduit quelques données redondantes dans l’intérêt de performance.

Exemple :

user(user_id, email, nom, prénom)
post(post_id, user_id, contenu, date)
post_commentaire(commentaire_id, post_id, contenu, date)

Si on voulait récupérer tous les commentaires de tous les post d’un utilisateur, nous serions obligés de faire un join entre au moins post et post_commentaire

Si c’est une requête qu’on fait souvent, on pourrait optimiser en dénormalisant, on ajoute user_id à la table post_commentaire

post_commentaire(commentaire_id, post_id, user_id, contenu, date)

Maintenant, on peut simplement faire un select simple en filtrant par le user_id.

Comme d'habitude, on essaie de trouver le meilleur compromis entre plusieurs facteurs :

• la consistance des données, manque de redondance

• performance et optimisation

• facilité de compréhension du code

• flexibilité et adaptabilité longue terme

Observations

Un schéma n'est pas statique, il pourrait changer au fur et à mesure :

• le schéma évolue avec notre compréhension du problème ou du domaine.

  • Parfois, c'est après la mise en service de notre plateforme qu'on se rend compte !

• avec l’évolution de notre logique business.

  • Les entreprises (surtout les startups) peuvent pivoter, changer de fonction. Il faut être prêt à s'adapter en fonction !

L’importance est de pouvoir :

• communiquer l’information utile entre développeurs

• afin de pouvoir faire des requêtes précises

• et efficaces

• et manipuler les données sans créer des incohérences

Exercice

(basé sur une vraie startup auquel j'ai eu le plaisir de participer)

Vous lancez une appli qui va permettre aux utilisateurs de garder un journal des repas. À chaque repas, l’utilisateur ouvre l’appli et précise des informations suivantes :

• Quel repas (petit-dej, dej, etc)

• Les aliments du repas

• Photos

• Les sensations pendant ou après le repas (satisfaction, toujours faim, etc)

• Un texte libre permettant de décrire d’autres infos concernant le repas

Le but de cette appli est de pouvoir aider aux gens, et leurs nutritionnistes, de comprendre plus précisément leur consommation :

• Le profile des macro-aliments (glucides, gras, protéines) par repas et par jour

• La totale calorifique par repas et par jour

• Les sensations qui permettraient aux nutritionnistes et/ou psychologues d’adapter les régimes alimentaires qui améliorent la vie quotidienne (et donc l’adhérence) de l’utilisateur

Utilisez les techniques de modélisation (entités, normalisation, etc) afin de modéliser une base de données pour cette application.

Le livrable est un schéma E-R (vous pouvez utiliser un des outils gratuits pour le produire)

Attention à réduire autant possible la redondance ou risque d’erreurs.