Discussion :

[Bruno75]

J'ai une petite tendance à normaliser à mort mes tables. Quels sont les critères qui indiquent que l'on doit dénormaliser son modèle ? Il ne s'agit pas ici de la base de reporting mais de la base "transactionnelle".

[laffreuxthomas]

Tout dépend du problème.

Dans le cadre d'un Data Warehouse, on dénormalise. Ca reste gérable puisque normallement on ajoute des données, puis on les consulte. on ne les modifie jamais. C'est pas ton cas manifestement.

Tu poses ta question à propos de quoi ? Si tes collègues râlent à cause des jointures à rallonge alors n'hésite pas à continuer à les faire râler.

Si tu as des problèmes d'optimisation tu peux tenter d'ajouter des index. Des index sont au fond des redondances techniques gérés par le SGBD et dont l'intégrité est garantie par le sgbd.

Donc pourquoi cette question ?

[Bruno75]

Le reporting est un cas standard ou la dénormalisation est de mise. Dans le cas du reporting, c'est pour des raisons de performances. Existe-t-il d'autres cas typiques où l'on dénormalise ? (pour diverses raisons).

[Cotentin]

salut,

tu peux denormalises aussi lorsque tu as besoin de consulter un grand de lignes avant de trouver ton bonheur.
cela permet aussi d'eviter des calculs longs et fastidieux

[grafikm_fr]

Perso, je considere que si la normalisation en 3FN aboutit à un nombre trop elevé de tables, il convient d'en denormaliser quelques une sinon bonjour le temps de réponse...

[SQLpro]

Le principe fondamental pour décider d'un dénormalisatio est le suivant :

1) normaliser au maximum lors de la création de la base
2) mettre en exploitation et mesurer régulièrement les temps de réponse
3) laisser la base vivre quelques semaine, mois ou années suivant la montée en charge de données
4) prendre les 20% des requêtes ayant les temps de réponse les plus longs et refaire en parallèle une base dénormalisée
5) comparer les temps de réponse entre la base "normale" lorsqu'il a peu d'utilisateurs et les temps de réponse de la base dénormalisée.

Si les temps de réponse dénormalisé sont de plus de 30% inférieur passer à la dénormalisation.

Revenir sur le sujet tous les ...

L'expérience m'a montré que lorsque la bse avait été bien normalisé et à condition de n'utiliser que des clefs auto incrémentées avec des index clusterisés, aucune dénormalisation n'avait besoin d'être introduite.

En revanche, pour simplifier le modèle, créer des vues ou des SP qui renvoie des tables, notamment pour les éditions.

Le probléme de conversion en décisionnel est tout autre. Dans ce cas : modèle en étoile ou flocon + réplication

A +

[Cotentin]

salut,

dis donc sql pro, as tu vraiment tester ce que tu dis, ou alors c'est seulement de la théorie ?
Parce que dans le cas de production, où la base est sollicité
je ne vois bien leur dire, que l'on doit les arreter pour changer les bases et recompiler les programmes
je travaille dans une usine de decoupe de viande, et ce sont plutot des as du couteaux n'ayant pas du tout de patience etant donné qu'il travaille à la piece.

La dénormalisation doit etre prevue lors de l'analyse des données.
Où la charge de travail doit etre calculer et prevue à l'avance

une fois, la base créé ont a pas à revenir dessus.

[al1_24]

Il faut aussi penser à l'évolution de la base de données.

Si la dénormalisation consiste à remplacer une table détail par une série de colonnes supplémentaires (exemple : les enfants d'un foyer), on risque de se trouver soit limité (pas assez de colonnes), soit encombré(des colonnes souvent vides).

Si la dénormalisation consiste à répéter les identifiants d'une nomenclature dans la table détail (pour simplifier les regroupements ?), il faut être certain que la nomenclature en question ne changera pas, ou prévoir des mises à jours longues et risquées.

Le probléme de conversion en décisionnel est tout autre. Dans ce cas : modèle en étoile ou flocon + réplication

Dans le cadre d'un Data Warehouse, on dénormalise. ...

On peut très bien développer un DWH en 3FN, même avec des milliards de lignes et plusieurs To de données. L'important c'est d'avoir un bon moteur...

Comme l'a si bien dit laffreuxthomas

Si tes collègues râlent à cause des jointures à rallonge alors n'hésite pas à continuer à les faire râler.

[Pomalaix]

Le principe fondamental pour décider d'un dénormalisatio est le suivant :

1) normaliser au maximum lors de la création de la base
2) mettre en exploitation et mesurer régulièrement les temps de réponse
3) laisser la base vivre quelques semaine, mois ou années suivant la montée en charge de données
4) prendre les 20% des requêtes ayant les temps de réponse les plus longs et refaire en parallèle une base dénormalisée
5) comparer les temps de réponse entre la base "normale" lorsqu'il a peu d'utilisateurs et les temps de réponse de la base dénormalisée.

Si les temps de réponse dénormalisé sont de plus de 30% inférieur passer à la dénormalisation.

Revenir sur le sujet tous les ...

Je me pince là !
Vous auriez pu me dire qu'on était le 1er avril !

[Admin]

Personnelement, vu le peu d'experience que j'ai sur Oracle, j'ai toujours considéré qu'une base normalisée bien faite fonctionne mieux qu'une base dénormalisée.

[siocnarf]

--> Pomalaix
Quand tu auras fini de te pincer et si tu as le temps, pourrais-tu nous dire ce que tu préconise sur la normalisation/dénormalisation ?

Merci d'avance

[grafikm_fr]

On peut très bien développer un DWH en 3FN, même avec des milliards de lignes et plusieurs To de données. L'important c'est d'avoir un bon moteur...

Deja qu'avec un bon moteur et un truc denormalisé un DW c'est la galere, alors en 3FN...

D'ailleurs dès que tu retient un modele en étoile (ce qui est le cas pour la plupart des DW), il n'est par definition pas en 3FN puisque la table contient une hierarchie complete...

Le modele flocon peut etre en 3FN mais il sera lent et difficilement gérable... Peut-etre dans l'avenir...

[SQLpro]

Mais pour du DataWaehouse il existe des SGBD (R ?) spécialisé comme celui de Sybase...

Donc pas besoin de faire du DW dans du SGBDR. Choisir le bon outil pour donner la bonne solution au problème posé !

A +

[grafikm_fr]

Je suis d'accord avec toi SQLPro, mais tout DWH basé sur une techno ROLAP (BO etc...) s'appuie sur une SGBDR (un peu modifiée certes, mais une SGBDR quand meme...) et les regles de normalisation (et surtout de dénormalisation) sont valables aussi. Non?

Bien sur, tu peux mettre une techno MOLAP qui n'a plus rien à voir avec une SGBDR mais là c'est une autre histoire... Et ROLAP c'est à 95% du relationnel, avec les memes contraintes (sauf que les regles de passage sont très legerement differentes)

[Pomalaix]

--> Pomalaix
Quand tu auras fini de te pincer et si tu as le temps, pourrais-tu nous dire ce que tu préconise sur la normalisation/dénormalisation ?

Bonjour

De manière générale, j'essaye d'être pragmatique.
La dénormalisation se justifie si ses avantages sont supérieurs à ses inconvénients, tout simplement !

Pourquoi normaliser, au fait ? Principalement :
1) par souci d'économie d'espace, pour éviter de dupliquer une même donnée N fois, ou de laisser des zones vides
2) par souci de cohérence, pour éviter que des données qui devraient être identiques ne le soient pas (en raison d'erreurs de saisie par exemple)

En bon normatolâtre, on devrait par exemple créer une table TITRE (contenant M., Mme et Mle) dès lors qu'on gère des clients ou des personnes.
Mais qu'est-ce qui s'oppose à ce qu'on stocke le titre directement dans la table client ?
Concernant l'espace disque, il n'est pas plus avantageux de stocker une clé étrangère qu'une valeur de 3 caractères.
Concernant la cohérence, une contrainte de validation permet de s'assurer simplement que les seules valeurs acceptées sont bien M. Mme ou Mle et rien d'autre.
En revanche, si l'on veut connaître les différents titres de civilité des clients, on est obligés de faire un DISTINCT sur la colonne TITRE de la table CLIENT (plusieurs milliers de lignes), alors qu'il suffisait dans la situation de précédente de consulter une table minuscule.
Si ce dernier point pose problème, parce qu'on a très fréquemment besoin de la liste des différents titres, alors la normalisation est sans doute préférable. Dans le cas contraire, fonçons ! On voit que la réponse n'est pas uniquement technique, elle demande une bonne connaissance des besoins fonctionnels.

Une question courante de dénormalisation concerne les valeurs agrégées.
Par exemple, on peut juger plus efficace d'ajouter une colonne PLACES_LIBRES dans une table VOL_AERIEN, plutôt que d'aller interroger à chaque fois la table des réservations pour le vol concerné, en exécutant une somme ou un décompte. Ce report peut se faire de manière simple et transparente par un déclencheur, à chaque réservation.

La complexité de la structure et le nombre de jointures ne sont pas sans importance non plus.
En effet, même si des jointures impliquant 10 ou 12 tables correctements indexées restent généralement efficaces, il est certain qu'il est plus économique en entrées/sorties d'aller lire dans une seule table que dans 2 pour faire une jointure.
(A ce sujet, les clusters de tables supportés par certains SGBD permettent de stocker physiquement côte à côte des données de tables différentes mais fréquemment en jointure, ce qui limite donc les entrées sorties)

Par ailleurs, même si les vues permettent de masquer la complexité sous-jacente des tables, il faut penser aux cas où les utilisateurs font des requêtes libres avec un outil de restitution quelconque, type BO ou autre. Dans ce cas, sauf utilisateur très averti, un informaticien reste souvent indispensable pour aider l'utilisateur à s'y retrouver dans une structure ultra-normalisée, avec des jointures externes, etc.
Dénormaliser quelque peu (en toute sécurité grâce aux mécanismes d'intégrité fournis par les contraintes ou les déclencheurs) peut alors être judicieux si on reste conscient des éventuels inconvénients générés en contrepartie.

[Fabien Celaia]

D'accord avec SQLPro, sauf sur ce cas-la:

L'expérience m'a montré que lorsque la bse avait été bien normalisé et à condition de n'utiliser que des clefs auto incrémentées avec des index clusterisés, aucune dénormalisation n'avait besoin d'être introduite.

L'indexation cluster determine generalement l'ordre physique dans lequel sont stockes les enregistrements (hormis dans le cas exceptionnel des tables DOL sous ASE). Dans ce cas, creer un index cluster sur un champ autoincremente genere un hot-sport enorme sur la derniere page de ladite table (grosse contention) qui est un frein aux performances.

[siocnarf]

--> Pomalaix

Merci

[SQLpro]

Excuse moi Pomalaix, mais je peut pas laisser dire cela...

Les bases de données relationnelles, ont été créées pour gérer des relations et non pour recommencer un sempiternel modèle de données basé sur des fichiers.
A ce titre, ce que savent le mieux faire les SGBDR c'est la mise en relation des données.

Maintenant quand tu dit dans "pourquoi normaliser [...] ?"
1) par souci d'économie...
2) par souci de cohérence
...

tu oublie le principe même de la normalisation qui est la dépendance fonctionnelle.
Si cette donnée dépend d'autres données alors normalisation.
Maintenant cette dépendance n'est pas binaire au sens stricte. Je dirais que la dépendance fonctionnelle est plutôt en logique flou : un pourcentage compris entre "dépendance fonctionnelle = Vrai" et "dépendance fonctionnelle = Faux".
Comment évaluer ce pourcentage ? Simple : compter la dispersion des données d'une colonne. Plus cette dispersion est élevée moins la dépendance est claire.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Un petit exemple
****************

Une table des personnes (PRS_ID, PSR_TITRE, PRS_NOM)

Premier cas, notre table contient 3 lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
PRS_ID PRS_TITRE PRS_NOM 
------ --------- ------- 
1      M.        DUPONT  
2      Mme.      DURAND  
3      Null      DUBOIS

Les questions a se poser sont les suivantes :

1) Y a t-il dépendance fonctionnelle de PRS_TITRE ? Pas évident !
2) quelle est la dispersion de PRS_TITRE ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT COUNT(DISTINCT PRS_TITRE) / (SELECT COUNT(*) FROM MaTable)
FROM MaTable

Résultat : 1 => la dispersion est maximale donc dépendance fonctionnelle quasi inexistante.

Second cas, notre table contient 30 000 lignes...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT COUNT(DISTINCT PRS_MASCULIN) / (SELECT COUNT(*) FROM MaTable)
FROM MaTable

Résultat : 0,2545758 => la dispersion est suffisament forte compte tenu du nombre de ligne pour procéder à la normalisation.

Quel sera la gain ?
PRS_TITRE nécessite au moins 5 octets ("Mlle.")
Une table externe peut utiliser un entier pour coder ce titre (ou un smallint le gain sera quasiement le même).
La table peut être construite de la sorte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
TIT_ID TIT_TITRE
------ ---------
1      M.
2      Mme.
3      Mlle.
4      Me.

etc...

La table de référence va donc couter : 9 octets * 4 lignes soit : 36 octets.
elle sera lue en une seule passe (car les SGBDR lisent des pages de l'ordre de 4 à 8 Ko !)

La table non normalisée coutera n octets de volume avec un PRS_TITRE de 5 octets.
Si elle devient normalisée elle ne coutera qu'un seul octet de moins puisque la différecne entre CHAR(5) et entier est de 1 octet.
Mais elle comporte 30 000 lignes... soit 8 pages lues.
Donc ta table non normalisée demande 8 pages de lecture supplémentaire... Bien entendu si l'on code la clef de la table de référence en smallint on passe à 24 pages lues en plus et si entier < 256 => 32 pages lues supplémentaires...
La normalisation a au moins fait gagner 7 pages en lecture...

La conclusion est simple :
La nécessité de normaliser ou pas, doit s'apprécier en fonction de la dispersion des données et de la taille des pages lues par le SGBDR.
J'ai coutûme de dire que :
pour les tables faiblement populées la normalisation doit s'effectuer si la dispersion est inférieure à 90%
pour les tables moyennement populées la normalisation doit s'effectuer si la dispersion est inférieure à 99%
pour les tables fortement populées la normalisation doit s'effectuer si la dispersion est inférieure à 99,9%

Autres avantages :
Si la données est stockée directement, rien n'empêche de saisir 'M.', 'Mr', 'Mr.' correspondant à Monsieur. Or cela va créer à tyerme de nombreux problèmes. Il faut donc mettre en place un contrôle de validité.
Le choix est simple : une contrainte CHECK VALUE IN ('M.', 'Mme.', 'Mlle.'). C'est léger, simple et... emmerdant !
En effet si notre quidam qui a modélisé la base n'a pas pensé à mettre toutes les valeurs, alors il faut modifier la structure de la table en passant un ordre ALTER !
Super !!! Par exemple notre quidam à oublié 'Me.' pour Maître (notaire, avocat...) et 'Dr.' pour Docteur !
L'avantage d'avoir une table de référence est donc bien de pemettre l'ajout ou la suppression de données sans altérer le modèle de données.
La containte d'intégrité référentielles fera la même chose que le CHECK VALUE....
Donc une grande souplesse en plus !

Contrairement donc à ce que tu dis, dans toues les cas ou le volume des données est en jeu, cette normalisation ne présente que des avantages !

En ce qui concerne les valeurs agrégées c'est une autre problématique...
D'abord l'exemple que tu donne n'est vraiement pas le bon, car toutes les compagnies, comme toutes les gestion de stocks possèdent des colonnes de comptage indépendante de la valeur réllément compté et cela pour deux problématiques différentes : la cie d'avion pour faire du surbooking, la gestion des stocke à cause de la démarque inconnue et donc de la nécessité de procéder régulièrement à un inventaire...
Si l'on veut conserver une valeur agrégée qui possède un réel intérêt il faut donc mettre en place un trigger qui lors des insert, update et delete, va recalculer cet agrégat.
Dès lors analysons différents cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Peu de lecture, peu de modif           => pas de nécessité de dénormaliser
Peu de lecture, beaucoup de modif      => surtout pas de normalisation
beaucoup de lecture, beaucoup de modif => la dénormalisation va dégrader sensiblement les perfomances du serveur
beaucoup de lecture, peu de modif      => la dénormalisation sera gagnante

N'oublions pas que les triggers sont les procédures les plus couteuses en ressources systèmes, bien plus que le procédures stockées !

La conclusion est donc simple : la dénormalisation ne peut s'engager qu'après avoir normalisé sa base,l'avoir mis en production et l'avoir auditée...
Une normalisation "préventive" s'avère généralement un remède pire que le mal !

[SQLpro]

Facade :

L'indexation cluster determine generalement l'ordre physique dans lequel sont stockes les enregistrements (hormis dans le cas exceptionnel des tables DOL sous ASE). Dans ce cas, creer un index cluster sur un champ autoincremente genere un hot-sport enorme sur la derniere page de ladite table (grosse contention) qui est un frein aux performances.

Oui, mais là c'est le travail ddu DBA et ce n'est plus du ressort du développeur ou CDP... Plan de maintenance.

A +

[SQLpro]

De plus des problèmes ne peuvent survenir que dans le cas d'insertion massive dans une table avec auto incrément et la plupart du temps c'est contournable en débranchant le mécanisme d'auto incrément

A +