Dénormalisation de table. Quand ? [Débat]

[laffreuxthomas]

Envoyé par fsmrel

J’ai extrait ce qui suit de l'article de Chamberlin (qui s’inspire de ce qu’il avait déjà écrit en 1994 avec trois de ses collègues chez IBM, « Extending relational database technology for new applications ») :

Vos contributions sont précieuses. Grand merci. Vous m'avez instruit.

Après avoir à nouveau retourné le problème dans ma tête aujourd'hui, je suis repassé à trois tables (la solution avec héritage). Je garde la redondance de la méthode du chemin mais j'hésite à la virer et mettre la récursion (qui devrait être rare) dans l'application.

Envoyé par CinePhil

laffreuxthomas, tu devrais proposer ton MCD dans le forum Schéma mais en tout cas, ça, c'est vraiment affreux, Thomas !

J'avoue avoir un peu peur de me faire taper pour ne pas être capable de vous soumettre un beau dessin. Je n'ai aucun logiciel pour faire ça bien que j'aie noté celui proposé par CinéPhil. Et d'ailleurs mon schéma principal ne contient que trois tables. Alors je raisonne direct en DDL…

Malgré tout il serait intéressant de discuter des différentes manières de représenter des arbres. Avez-vous parcouru l'exposé indiqué plus haut ? Ce que j'aime bien dans la méthode du chemin, c'est que son volume en base est proportionnel au nombre des éléments. Contrairement à la méthode de la table externe contenant toutes les combinaisons "ascendant-descendant" qui, elle, est proportion du carré du nombre des éléments (j'édite pour barrer, c'est une bêtise).

[CinePhil]

Postez votre cas particulier dans une discussion séparée et dans le forum approprié.

En l'occurrence, puisqu'il s'agit plus de discuter du modèle de données que de langage SQL, je vous recommande vivement de créer votre nouvelle discussion dans le forum Schéma dont j'ai donné le lien plus haut.

[laffreuxthomas]

Envoyé par CinePhil

En l'occurrence, puisqu'il s'agit plus de discuter du modèle de données que de langage SQL, je vous recommande vivement de créer votre nouvelle discussion dans le forum Schéma dont j'ai donné le lien plus haut.

C'est fait.

[redoran]

salut ; pourquoi dénormaliser une table ?
généralement la dénormalisation est faite pour extraire les données rapidement ( gain de temps) .
quand peut-on dénormaliser ?
pour me répondre à cette question , je crois que certains le font a défaut de conception ( modélisation incorrecte) là du coté du concepteur.
d'autres le font dans les cas suivants :

1. pour éviter le jointures sur plusieurs tables,
2. pour faciliter les calculs sur des colonnes de table différentes, je ne sais d'autres motifs d'utilisation... !

devant ces arguments , le choix de dénormaliser est-il bien pensé ?
normalement on doit cherché des solutions coté normalisation , indexation et utilisation de requête avancées.
coté fonctionnement SGBDR:
je ne suis pas expert dans la matière , mais je crois que le SGBDR est doté d'un optimiseur basé sur des algorithmes avancés pour prendre en charge les requêtes dans un délai optimal.
Est ce que les limites des SGBD nous poussent à dénormaliser ?

[cladsous]

Pour répondre à Redoran : la dénormalisation est souvent pratiquée quand le SGBDR est chargé à la fois en données et en connexions. Les MCDs exposés sur ce forum, sont souvent très contractés et facile de compréhension, mais dans "la vraie vie" ils sont souvent très complexes et difficiles à modéliser en restant fidèle aux normes. La dénormalisation prend en compte de nombreux paramètres : la manière de coder des développeurs, la solicitation de certaines procédures stockées et surtout mais vraiment surtout les temps de réponses du SGBDR de la forme normalisée rapport à une forme qui ne le serait pas. L'avantage de la forme normalisée est d'être rapidement compréhensible et facilite le développement mais aussi il est compréhensible à un public élargi. Mais comme toute chose une norme a des limites et parfois il faut prendre l'initiative de ne pas la respecter. Il existe de nombreuses manières de normaliser une BDD et il peut arriver qu'aucune ne réponde efficacement aux contraintes métiers existantes mais doivent aussi permettre d'être adaptable aux évolutions.
Enfin, ce que je précise ici n'engage que moi. Mais de part mon expérience, je constate que les personnes qui s'offusquent de ces "dérives" sont
en général les puristes alors que les donneurs d'ordres sont totalement satisfaits par les différents tests au moment de la réception, les maintenances effectuées ne sont jamais liées à un problème ne normalisation, mais plus à une évolution.

[CinePhil]

Envoyé par cladsous

Pour répondre à Redoran : la dénormalisation est souvent pratiquée quand le SGBDR est chargé à la fois en données et en connexions.

Elle est, d'après ce que je lis sur les forums DVP, pratiquée surtout à tort et à travers !
Même un SGBDR chargé en données et en connexions doit être capable de répondre rapidement à toute requête. Dans la grande majorité des cas, un modèle de données bien normalisé, une bonne indexation des tables et des requêtes bien écrites ne demanderont pas de dénormalisation pour obtenir de bonnes performances.

Les MCDs exposés sur ce forum, sont souvent très contractés et facile de compréhension, mais dans "la vraie vie" ils sont souvent très complexes et difficiles à modéliser en restant fidèle aux normes.

Mon exéprience se limite principalement à ce que je rencontre sur les forums de DVP et certains MCD exposés dans le forum Schéma sont parfois quand même un poil complexes.
Par contre, j'ai aussi constaté que le modèle de données de l'ERP que j'utilise comprend de nombreuses redondances qui semblent quand même surtout dues à la mise en commun de modèles de données au départ indépendants. Et c'est vrai que reconcevoir l'ensemble représenterait un boulot gigantesque que personne n'a envie de prendre le temps de faire.

La dénormalisation prend en compte de nombreux paramètres : la manière de coder des développeurs

Surtout pas !
Le modèle de données obéit à des règles de gestion des données qui ne doivent pas tenir compte de "la manière de coder des développeurs" !

la solicitation de certaines procédures stockées

Là encore, une procédure stockée étant un programme, elle ne doit pas contraindre le modèle de données mais au contraire s'y plier. Elle peut même être une aide au respect des contraintes sur les données issues des règles de gestion de celles-ci.

et surtout mais vraiment surtout les temps de réponses du SGBDR de la forme normalisée rapport à une forme qui ne le serait pas.

Voilà la seule raison qui devrait être prise en considération pour dénormaliser une BDD, après prototypage et tests approfondis et surtout après avoir vérifié les autres paramètres (modèle de données normalisé, indexation, écriture des requêtes, recours aux vues, aux vues matérialisées...).
La dénormalisation doit être le dernier recours car elle engendre un risque d'incohérence des données et une lourdeur pour justement maîtriser ce risque.
Je ne parle bien entendu là que de dénormaliser la BDD de production, pas d'en extraire les données d'une manière pratique pour en tirer des statistiques, donc de constituer un data wharehouse.

L'avantage de la forme normalisée est d'être rapidement compréhensible et facilite le développement mais aussi il est compréhensible à un public élargi.

Ce n'est pas son but, c'est une conséquence. Et même, cette affirmation est à pondérer car un "public élargi", s'il est un peu profane en modélisation des données, comprendra difficilement pourquoi il faut des tables associatives, de l'héritage de données, modéliser un calendrier, faire une arborescence intervallaire... Il aura plutôt tendance à se raccrocher à quelque chose de plus familier, tel qu'un tableur, et vouloir tout mettre dans une table !
Le but du modèle de données normalisé est de placer les données là où il faut pour rendre les opérations effectuées par la BDD les plus fluides et rapides possibles.

Mais comme toute chose une norme a des limites et parfois il faut prendre l'initiative de ne pas la respecter.

En dernier recours, quand c'est réellement nécessaire, et ça l'est rarement ! Jamais a priori !

Il existe de nombreuses manières de normaliser une BDD

Euh... à partir de règles de gestion identiques, deux personnes modélisant avec méthode devraient arriver au même modèle de données, au nom des propriétés, des entités types, des associations et, par voie de conséquence, à la description des tables près.
Le bémol que je mettrais est l'emploi ou non de l'identification relative dans certains cas car ça constitue déjà une optimisation (mais pas une dénormalisation) du modèle de données pour faciliter le requêtage.

et il peut arriver qu'aucune ne réponde efficacement aux contraintes métiers existantes

Les contraintes métier relèvent de l'applicatif. Les contraintes sur les données sont relativement indépendantes des contraintes métier.

mais doivent aussi permettre d'être adaptable aux évolutions.

Un modèle de données normalisé est toujours évolutif.
Un modèle dénormalisé est plus difficilement modifiable.

Mais de part mon expérience, je constate que les personnes qui s'offusquent de ces "dérives" sont
en général les puristes alors que les donneurs d'ordres sont totalement satisfaits par les différents tests au moment de la réception, les maintenances effectuées ne sont jamais liées à un problème ne normalisation, mais plus à une évolution.

Sur ce point, je ne peux te contredire, ne connaissant pas ton expérience et n'en ayant moi-même qu'une assez faible.

[cladsous]

D'après SYBASE :

Dénormalisation de tables et de colonnes
La normalisation consiste à éliminer la redondance et les dépendances incohérentes entre les tables. Bien que la normalisation soit le plus souvent considérée comme le but de la modélisation de base de données, la dénormalisation, c'est-à-dire la duplication délibérée de certaines données afin d'accélérer l'extraction des données, peut s'avérer utile dans certains cas :
- Lorsque les requêtes les plus importantes portent sur des données réparties sur plusieurs tables.
- Lorsque des calculs doivent être effectués sur une ou plusieurs colonnes avant que la requête ne renvoie une réponse.
- Si les tables doivent être consultées de différentes façon par différents utilisateurs lors d'une même période.
- Si certaines tables sont très fréquemment utilisées.
Lorsque vous devez évaluer la pertinence d'une dénormalisation, vous devez analyser vos besoins dans le domaine de l'accès aux données par les applications dans votre environnement ainsi que leurs performances. Le plus souvent, d'éventuels problèmes de performances peuvent être résolus par une politique d'indexation judicieuse et l'emploi d'autres solutions que la dénormalisation.
La dénormalisation peut être effectuée de différentes façons :
- Partitionnement horizontal : utilisé pour diviser une table en plusieurs tables contenant les mêmes colonnes, mais moins de lignes.
Le partitionnement horizontal consiste à segmenter une table en plusieurs tables contenant chacune un sous-ensemble des lignes et les mêmes colonnes que la table partitionnée afin d'optimiser l'interrogation des données. Vous pouvez utiliser n'importe quelle colonne, y compris une colonne de clé primaire, comme critère de partitionnement.

- Partitionnement vertical : utilisé pour diviser une table en plusieurs tables contenant le même nombre de lignes, mais moins de colonnes.
Le partitionnement horizontal consiste à segmenter une table en plusieurs tables contenant chacune un sous-ensemble des lignes et les mêmes colonnes que la table partitionnée afin d'optimiser l'interrogation des données. Vous pouvez utiliser n'importe quelle colonne, y compris une colonne de clé primaire, comme critère de partitionnement.

- Fusion de tables : permet de fusionner des tables afin d'éliminer la jointure entre elles.
La fusion de tables consiste à combiner plusieurs tables en une seule afin d'éliminer des jointures et d'améliorer les performances des requêtes.
Dénormalisation de colonnes
Vous pouvez dénormaliser des colonnes pour éliminer des jointures fréquentes en utilisant la dénormalisation de colonnes.

- Dénormalisation de colonne : permet de répéter une colonne dans plusieurs tables afin d'éviter d'avoir à créer des jointures entre les tables.
Vous pouvez dénormaliser des colonnes pour éliminer des jointures fréquentes en utilisant la dénormalisation de colonnes.

[StringBuilder]

En même temps, si je ne m'abuse, SQL Server était basé sur Sybase jusqu'à sa version 6.5. Et c'était une merde qui était à peine capable de rivaliser avec Access (en exagérant un peu évidement)
Et il a commencé à devenir à peut près potable avec la version 7.0, et réellement correct avec la version 2000, quand Microsoft à décidé de réécrire complètement le moteur.

Donc je prendrais les conseils de Sybase avec des pincettes (après, le produit à peut-être lui aussi évolué dans le bon sens, on sait jamais).

En effet, Sybase était, à l'époque tout du moins, de ce genre de SGBD dont les performances s'écroulaient dès qu'on devait faire une jointure.

En pratique, la dénormalisation était utile d'un point de vue performances jusque vers la fin des années 90. Depuis, force est de constater qu'il est plus que rare qu'on puisse tirer le moindre profit d'une dénormalisation.
Et quand cette dernière est réellement nécessaire, on peut s'en passer la plupart du temps en utilisant des fonctionnalités bien plus avancées, telles que les colonnes calculées ou les vues matérialisées.

[fsmrel]

Envoyé par cladsous

D'après SYBASE

Si j’en crois Wikipedia, SYBASE fut fondée en 1984, un paquet d’années après que Ted Codd (inventeur de la théorie relationnelle) ait défini la normalisation.

Histoire que tout le monde parle de la même chose, je rappelle en quelques lignes ce qu'est la normalisation dans le contexte des bases de données (pour en savoir plus, voyez par exemple ici).

En 1970, dans son article fondateur A Relational Model of Data for Large Shared Data Banks, Codd définit la normalisation en première forme normale, ce qu’il appelait simplement « normalisation ».
La même année Codd définit les deuxième et troisième formes normales (The Second and Third Normal Forms for the Relational Model, IBM technical memo, October 6th, 1970). Voici une photo de la page 27 d’un article daté de 1972, Further Normalization of the Data Base Relational Model, dans lequel Codd résume les étapes de la normalisation :



En 1974, conjointement avec son collègue d’IBM, Raymond Boyce, Codd définit la forme normale de Boyce-Codd.

En 1977, Ronald Fagin (un autre de ses collègues d’IBM lui aussi) définit la quatrième forme normale (in Multivalued Dependencies and a New Normal Form for Relational Databases) puis la cinquième forme normale en 1979 (Normal forms and relational database operators).

Enfin en 2003 C.J. Date, H. Darwen et N. Lorentzos définirent la sixième forme normale (in Temporal Data and the Relational Model).

Voilà pour ce qu’on appelle la normalisation au sens habituel du terme.

De la 2NF à la 6NF, la technique utilisée pour normaliser une variable relationnelle (relvar, informellement table en SQL) consiste par une opération de projection à produire deux relvars « mieux » normalisées R1 et R2 à partir d’une relvar R violant la xième forme normale, sachant que par jointure (naturelle) de R1 et R2 on doit retrouver exactement R (principe de la décomposition sans perte).

Incidemment, on notera que la 5NF est née 5 ans avant Sybase.

Envoyé par cladsous

La normalisation consiste à éliminer la redondance

Le verbe "consister" est ici impropre, car normaliser consiste à décomposer (par projection) une table en deux tables (en rappliquant par exemple le théorème de Heath). En revanche, la normalisation a notamment pour effet la diminution (sinon l'élimination) des redondances.

Envoyé par cladsous

et les dépendances incohérentes entre les tables

Plaît-il ? De quoi Sybase veut-il parler ? De l’intégrité référentielle ?

Envoyé par cladsous

Bien que la normalisation soit le plus souvent considérée comme le but de la modélisation de base de données

Sybase confond la fin et les moyens. Appliquer la théorie de la normalisation permet de mieux contrôler le processus de la modélisation, mais en l’occurrence cette théorie n’est pas la panacée.

Envoyé par cladsous

la dénormalisation, c'est-à-dire la duplication délibérée de certaines données afin d'accélérer l'extraction des données

Pour violer quelle forme normale ? Sybase a-t-il pensé aux risques d’incohérence inhérents lors des mises à jour ?

Sybase a-t-il démontré par un prototypage de performances ad-hoc qu’il faut dénormaliser ? Il est possible qu’avec son SGBD la conclusion aille en ce sens, mais par exemple, sur la base du verdict du prototypage, j’ai mis en œuvre des bases de données de plus de mille tables sans avoir à dénormaliser. Il faut dire qu’ave un SGBD comme DB2 for z/OS, l’optimiseur est particulièrement performant quant aux jointures.

Accessoirement, quand on dénormalise en joignant deux tables T1 et T2 pour produire une table T3 redondante, les lignes de celle-ci occupent plus de place que ce qui est requis par une jointure figurant dans une requête dans laquelle on ne code pas SELECT T1,*, T2.*, mais où l’on nomme les seules colonnes nécessaires. En voulant bien faire, on peut avoir des surprises.

Envoyé par cladsous

La dénormalisation peut être effectuée de différentes façons :
- Partitionnement horizontal

Aïe ! Le gars de Sybase s’est planté... Ça c’est de l’« optimisation », ça n’a strictement rien à voir avec la [dé]normalisation.

Envoyé par cladsous

Partitionnement vertical

Ça a des relents de [dé]normalisation (au fait, pour passer en quelle forme normale ?) Cela dit, il y a une erreur de copier/coller : vous avez recopié la description du partitionnement horizontal.

Envoyé par cladsous

Fusion de tables : permet de fusionner des tables afin d'éliminer la jointure entre elles.
La fusion de tables consiste à combiner plusieurs tables en une seule afin d'éliminer des jointures et d'améliorer les performances des requêtes.

Ouch ! En attente du verdict du prototype ad-hoc, je remplacerai volontiers « améliorer la performance » par « dégrader la performance »...

Envoyé par cladsous

Dénormalisation de colonne

C’est seulement de l’optimisation (sous réserve que les la copie de colonne n’entraîne pas viol de la forme normale respectée jusqu’ici par les tables touchées). En tout cas, bonjour les mises à jour, Sybase aurait pu s’abstenir.


Bref, la [dé]normalisation selon Sybase ça n’est pas très sérieux et c’est surtout redoutable. En 25 ans de DB2 je n’ai pas une seule fois procédé comme le fait Sybase qui a une vision bien curieuse de la normalisation et ferait bien de lire les auteurs sérieux tels que Codd, Date et Fagin, sans oublier de monter des prototypes de performance.

La défiance de Sybase à l'endroit de la jointure, donc sa volonté de dénormaliser me rappelle les a priori auxquels j'ai dû parfois faire face et évacuer, voyez par exemple ici l'histoire des lots de chèques.