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

[Pomalaix]

Je vais essayer d'être bref et d'aller à l'essentiel.

Ce avec quoi je ne suis absolument pas d'accord dans votre démarche, c'est quand vous dites qu'il faut attendre la mise en production de la base pour voir si la normalisation absolue ne pose pas de problème.

Des bases en 5NF, je n'en vois pas tous les jours. Mais heureusement, beaucoup d'entre elles donnent toute satisfaction. D'ailleurs, le jour où un théoricien va découvrir la 6NF, devra-t-on en déduire que toutes les bases existantes sont à jeter ??

Normaliser par défaut, oui, puisque la méthode est éprouvée. (Je ne préconise absolument pas une dénormalisation préventive).
Attendre la production pour vérifier que le modèle est efficace, c'est délirant.

Dans un projet digne de ce nom, on a une phase de test de charge permettant de vérifier que le traitement des gros volumes reste efficace, et que de nombreuses connexions concurrentes sont bien supportées.

Bien entendu, on n'est pas à l'abri de laisser passer un problème durant cette phase de test, mais "attendre et voir", c'est de la folie.
Comme l'a relevé un intervenant, une fois qu'un système est en production, devoir l'arrêter pour révision du modèle est une véritable catastrophe.

Vos exemples chiffrés ont le mérite de rendre les choses plus concrètes, même s'ils sont numériquement inexacts et extrapolés à mauvais escient.
* Sur l'exemple des titres de civilité, "Mlle" ne requiert que 4 caractères, et non 5. (Sur http://sqlpro.developpez.com/Normes/SQL_normes.html, j'ai souvenance d'une rubrique relative aux titres de civilité qui précise "Attention à la présence ou l'absence du point terminal de l'abréviation.")
* Les pages de SGBD ne font pas nécessairement 4 ou 8 Ko. D'ailleurs, en dernier ressort, c'est au niveau de l'OS et des disques physiques que ça se joue, avec une granularité qui peut être différente de celles des pages du SGBD du fait des caches de lecture anticipée.
* 8 pages de surcharge ( ou 24 ou 32) en cas de dénormalisation du titre. Même en admettant l'exactitude du calcul, ce point n'est pas fondamental. En effet, en transactionnel, on accède principalement aux données de manière ciblée, grâce aux index. Il est donc peu probable qu'on lise la table complète.

Par ailleurs, je l'avais dit d'emblée, puis en conclusion : la dénormalisation s'accompagne d'inconvénients. Il faut juger si les avantages qu'on obtient de la dénormalisation sont supérieurs.
Votre argument sur le manque de souplesse de la dénormalisation des titres (si subitement on veut en ajouter un) se situe à ce niveau-là : suis-je prêt à accepter cette incommodité ?

* Le critère numérique de dispersion est bien discutable.
En effet, sur une grande table de clients, on en conclurait qu'il faut créer une table des prénoms, puisqu'on n'a que quelques centaines de prénoms différents, alors qu'on a des milliers de noms de famille
Si vous avez un complément là-dessus, je suis preneur.

[SQLpro]

Je n'ais pas dit qu'il fallait aller jusqu'à la 5eme forme normale.

En fait 3 NF me suffit en général et couvre généralement la plupart des cas de figure.

En revanche, les tests de monté en charge sont rarement faits, et l'impasse sciement opérée sur ce point est monaie courante. De plus généralement les tests de montée en charge prennent rarement en compte la charge qui sera exactement celle d'ici 3 mois, 6 mois, un an... parce qu'il est très difficile de s'accorder sur le nombre de ligne de chaque table à terme.
De plus les schéma évoluent au gré des besoins et remettent en question l'étude "a priori". Connais tu des bases de données qui, depuis leur mise en exploit n'ont jamais vu leur schéma modifié ?

Arrêter un SGBDR pour en modifier son schéma n'est pas monnaie courante. Lorsque cela s'avère c'est généralement preuve d'un problème de conception à la base donc défaut d'analyse et/ou de modélisation.

Arrêter une appli pour le même motif relève que les développeurs ne réflêchissent pas au "style" de développement qui devrait être induit de la possible évolution du schéma.

Par exemple je préconise en général le style de dev suivant : lecture vai des requêtes SELECT sur tables ou mieux sur VUES et encapsulation du code SQL dans des bibliothèques ou des objets afin de pouvoir remédier rapidement aux modif. Mises à jour (INSERT/UPDATE, DELETE) dans des procédures stockées.
Lorsque ce style est mis en oeuvre, et il n'est pas plus couteux qu'un développement de "bidouilleur" la modification des schémas et des applicatifs est un jeu d'enfant puisque l'IHM et l'accès aux données ne subit quasiement aucun changement...

Lorsqu'on ne met pas en place des tables de ref il advient que si les colonnes visées sont fortement sollcictées il faut ajouter des index couteux car créé sur des types littéraux. Le probléme devient alors celui de la mise à jour des données et des index...

Quand à la table des prénoms ou des noms, elle existe sur de très grosses bases de données. Cela présente l'avantage d'être sûr que tous les Alain sont saisie de l'unique littéral mentionné et nom "ALAIN" ou "alain" ou encore " Alain" avec un espace devant !
De plus pour des éléments spécifiques comme les noms existe des mécanismes d'indexation phonétiques comme les soundex et autres (tables de Cutter par exemple) qui peuvent s'avérer payant.
Pensons aussi à l'indexation textuelle !

A +

[fadace]

Envoyé par 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

Non, pas seulement: n'importe champ numerique/date qui est la cle d'un index cluster et qui s'incremente aura ce genre de goulet d'etranglement.

[fadace]

Envoyé par SQLpro

Facade :

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

Alors dans ce cas, il faudra laisser au DBA le choix de definir le type d'index adequat... ce n'est heureusement pas toujours le cas (si "mes" 50 developpeurs ne s'en sortaient pas tout seul, je serais "mort"... ainsi que mon moteur SQL )

Par contre, entierement d'accord qu'on ne peut passer a une denormalisation sans avoir au prealable normalise. Je pense cependant que l'experience permet d'eviter de faire jouer les cobayes a la production et que la denormalisation ne doit pas passer forcement par un niveau "try & see".

[SQLpro]

En guise de conclusion et pour répondre à notre questionneur :

Mieux vaut commencer par une normalisation que par une dénormalisation !

A +

[Pomalaix]

Envoyé par SQLpro

En guise de conclusion et pour répondre à notre questionneur :
Mieux vaut commencer par une normalisation que par une dénormalisation !

Arf, tout ça pour ça !
Comme quoi le consensus tient en peu de mots...

[SQLpro]

J'étais sûr que cela te ferait rigoler !!!!

On est pas payé mais au moins on rigole !!!!

A +

[Bruno75]

Merci pour toutes vos réponses.

Une petite remarque. SQL pro j'ai l'impression que tu travailles avec des moyens très important parce que

Le probléme de conversion en décisionnel est tout autre. Dans ce cas : modèle en étoile ou flocon + réplication
...
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é !

+ les stratégies d'audit de bases.

Chanceux va.

[fadace]

C'est toujours moins cher d'acheter un bon moteur que de devoir rafistoler un mauvais a coup de CPU et de RAM
De plus, l'economie de disque (3x moins) peut se reveler payant pour un DW digne de ce nom

[Alexandre T]

Le logiciel sur lequel je travaille possède une base de données totalement dénormalisée pour simplifier les éditions que les clients peuvent souhaiter faire avec leurs outils de reporting.

Résultat redondance mal traitée et temps de réponse trop lent.

J'ai tout normalisé, j'ai "concaténé" la base de sept de nos plus gros client, j'ai démontré qu'infomaker faisait graphiquement et automatiquement les jointures sans la moindre erreur.

Les temps de réponses sont dans mon prototype (qui contient bien plus de données que les bases de prod) plus rapide de 45% aux temps de réponses sur la base des clients. (J'ai bien fait mes mesures aux heures creuses évidemment). Pourtant ma solution a été refusée car la direction ne comprends pas la normalisation.

Résultat : temps de développement : 5x supérieur à la normale. Temps d'accès 50% plus lents que la normale, mais tout va bien Madame la comtesse.

[SQLpro]

Bon exemple Alexandre T... C'est ce que je traite à longueur de journée dans mes audits...

Enfin en ce qui concerne ce que dit Pomalaix en général :

Attendre la production pour vérifier que le modèle est efficace, c'est délirant.
Dans un projet digne de ce nom, on a une phase de test de charge permettant de vérifier que le traitement des gros volumes reste efficace, et que de nombreuses connexions concurrentes sont bien supportées.

Malheureusement il est TRES rare de voir de voir des tests de charge et les rares tests de charge que j'ai vu étaient sur des volumes insignifiants ou des machines inapropriée. Quelques exemple : test de charge 2 go... Sur une base devant recevoir en 3 années d'exploit 600 Go...
Autre exemple : test de charge sur un serveu P2 à 1 Ghz mono processeur avec disque IDE... Pour un site web devant tourner avec une web farm et des serveurs quadri pro SCSI répliqués sur 4 serveurs !!!
Et ce sont des gros projets avec de gros moyens !!!! ;-)

Pomalaix
Pourquoi normaliser, au fait ? Principalement :
[...]

tu as oublié deux éléments les plus essentielles à mes yeux :
1) un SGBDR est optimisé pour faire des relations entre les tables. Donc, ne pas s'en priver !
2) la normalisation économise le cache des données et comme les caches ne sont pas extensibles à l'infini, plus le cache est dense en info, plus on peut traiter de tuples, plus vite l'information sera traiter. A contrario de grande tables (grande par leur lignes) encombre le cache car un SGBDR n'est pas capable de ne mettre en cache que certaines colonnes d'une table.

Pomalaix
Une question courante de dénormalisation concerne les valeurs agrégées.

***

Pour cela il existe des techniques tout à fait particulière qui combinent les deux mondes :
vues matérialisée dans Oracle, vues indexées dans MS SQL Server.
Là encore le modèle n'est pas dénormalisé, mais une redondance est sciement introduite, calculée automatiquement de manière interne en minimisant les ressources du système.

A noter, je prépare une série d'article sur l'optimisation des bases de données SQL Server pour SQL Server Magazine. Mais ces articles sont des articles TRES génériques dont les principes peuvent être repris pour tous les SGBDR.

A +

[SQLpro]

Pomalaix disait :

le jour où un théoricien va découvrir la 6NF, devra-t-on en déduire que toutes les bases existantes sont à jeter ??

Non seulement la 6NF existe bien (et depuis 2005) mais elle est très intéressante pour les problématiques de données temporelles.

A noter aussi la DKNF qui se situe après la 5NF.

Quelques liens sur le sujet :
les formes normales :
http://en.wikipedia.org/wiki/Database_normalization
http://experts.about.com/e/d/da/data...malization.htm

Discussion sur ces formes normales particulières
par Chris Date :
http://www.dbdebunk.com/page/page/621935.htm


A +

[SQLpro]

Extrait de : http://www.developpez.net/forums/sho...74&postcount=4

Ne mélangez pas le niveau logique et le niveau physique : très nombreux sont ceux qui ont oublié que l’indépendance entre le niveau physique et le niveau logique est un point capital et se sont fourvoyés. Commencez par vous focaliser sur le niveau logique. Quant à la performance (niveau physique), on trouve toujours un moyen de la garantir sans dénaturer ce qui a été fait au niveau logique.

[...]

Pour en arriver au niveau physique. Quant à la performance en relation avec le fait de normaliser ou dénormaliser, il n'y a aucune règle absolue. Vous devez construire un prototype, mesurer cette performance et améliorer le paramétrage physique (à commencer par les index...) jusqu’à obtenir les performances attendues. Sachez qu'en vingt ans de ("very large") bases de données relationnelles dans tous les secteurs d'activité, banque, assurance, industrie, services, j'en passe et des meilleures, j'ai toujours normalisé à fond et n'ai jamais eu à dénormaliser. En contrepartie, j'ai prototypé des nuits et des nuits (pour ne pas perturber les autres en pleine journée) et j'ai retenu que rien n'est jamais acquis : ce qui marche bien chez l'un est à reprendre complètement chez l'autre, et c'est ce qui fait du reste un des charmes du métier...

[fsmrel]

Merci SQLpro pour les citations.

J'ajouterai que j’ai souvent eu à engager mon entreprise (une SSII) en ce qui concerne la performance des applications chez nos clients. Il est évident qu’un client n’attendra pas que la base de données soit en production pour qu’on lui prédise la durée des traitements !

Et pourtant, histoire vécue... Tel grand cabinet modélise et conçoit une très grosse base de données pour le compte d’une très grande entreprise. Concernant LE batch quotidien, engagement pris sur une durée de traitement de 8 heures. Arrive l’heure de vérité, la mise en production. Durée effective du traitement : 240 heures (je dis bien, 10 jours pleins)... Pour un batch quotidien, dur, dur... Les pénalités vont tomber ? Vous n’y pensez pas, car en cours de route le client à décidé de changer de matériel et de SGBD : le grand cabinet était donc d’office désengagé... Les causes d’un tel désastre ? Le concepteur (du grand cabinet) ne connaissait rien à la modélisation et n’avait jamais touché à une base de données. En conséquence, des tables complètement dénormalisées et inutilement obèses, des requêtes patinant dans d’immenses champs de neige (je veux dire de valeurs nulles). Aucun prototypage de performance effectué, laxisme du client, etc., la totale. Quand j’ai eu à expertiser tout cela, imaginez mon étonnement puis ma colère devant tant d’incurie...

Une année plus tard, je dois à mon tour concevoir l’architecture physique d’une autre grosse base de données chez ce même client, encore sous le choc des 240 heures. Je dois engager mon entreprise quant à la performance. Finirai-je dans le goudron et les plumes ? Ne sachant pas lire dans le marc de café, je demande à pouvoir au préalable monter un prototype. Le client est d’accord pour me prêter une machine, la nuit. Je valide le modèle conceptuel, en m’assurant de la 3e forme normale puis le dérive en base de données et bourre celle-ci ras la gueule, crée les index pertinents, effectue les campagnes d’explains, bref la routine. Au bout de 3 semaines, j’estime le temps de traitement batch à 5 heures : cela convient au client. Autant vous dire que j’ai sensibilisé l’équipe de développement de mon entreprise à cet aspect des choses et nous sommes restés constamment en phase et très vigilants (vous noterez que les développements n’ont été effectués qu’après engagement). Le jour de la mise en production : temps de traitement batch égal à 4h40. Toutes les tables étaient en 3e forme normale, etc.

Je ne cherche pas à dire autre chose que ceci : mieux vaut prévenir que guérir. Et comme dit le poète :

« L’avait l’ don, c’est vrai, j’en conviens,
L’avait l’ génie,
Mais sans technique, un don n’est rien
Qu’un’ sal’ manie... »

[Daranc]

Bonjour
j'ai lu ce post (ou fil ,j'ignore le mot employé sur les forum Develloppez). pratiquement tois ans de débat. Mais pourrais je avoir une explication ou un lien qui parle de ce qu'est la normalisation?
Merci
Daranc

[fsmrel]

Bonsoir Daranc,

Très rapidement après qu’il eut inventé le Modèle relationnel en 1970, Ted Codd a traité de problèmes rencontrés avec la manipulation des relations (informellement tables), ce qui l’a conduit à ce qu’il a appelé la "Normalisation des relations" (jeu de mots peut-être lié au contexte de l’époque, dans le cadre des relations des blocs Est-Ouest...)

Objectif à atteindre :

— Minimiser la redondance.
— Minimiser les difficultés et les anomalies lors des opérations de mise à jour (INSERT, UPDATE, DELETE).
— Réduire la nécessité de modifier la structure des relations à l’occasion de la prise en compte de nouveaux types de données (donc d’attributs).
— Aboutir à une base de données structurellement correcte.
— ...

Un exemple tout bête :

Vous êtes en face d’une table T de commandes. On a commandé le produit P1 au fournisseur F1, en quantité 300. Ce fournisseur habite Lyon. T peut avoir l’allure suivante (clé primaire soulignée) :

Code :

1
2
3
 
    T (IdFour   IdProd    Quantité    Ville)
         F1       P1           300    Lyon 

On aimerait bien faire figurer le fait que le fournisseur F2 habite Paris, mais tant que l’on ne lui a rien commandé, impossible de faire figurer cette donnée dans la table.

La normalisation a fait l’objet d’études poussées, entre les années 1971-1980, elle est devenue une théorie que l’on considère comme une branche des mathématiques appliquées.

Références :

E. F. Codd. “Further Normalization of the Data Base Relational Model”. IBM Research Report RJ909 (August 31, 1971).
(C’est un des papiers fondateurs mais dont je ne retrouve plus le contenu sur la toile, désolé...)

Un article de William Kent, qui fut à l’origine de la BCNF, mais curieusement qu’il n’évoque pas ici :
http://www.bkent.net/Doc/simple5.htm
Et où j’observe que sa définition de la 1re forme normale est caduque (aujourd’hui, un attribut peut être du type Relation).

L’ouvrage le plus complet et le plus pertinent sur le sujet reste l’ouvrage de Chris Date (environ 750000 exemplaires vendus à ce jour) :
C.J. Date. An Introduction to Database Systems, 8th edition. (Pearson - Addison-Wesley)
http://www.amazon.com/dp/0321197844?...KC5YNS9WG724E&

La version française existe :
C.J. Date. Introduction aux bases de données, 8e édition (Vuibert)
http://www.amazon.fr/Introduction-ba.../dp/2711748383

N’hésitez pas à fouiller dans les sites suivants :

http://www.dbdebunk.com/index.html
http://www.thethirdmanifesto.com/

Vous pouvez aussi consulter sur le forum Général SGBD, la discussion dans laquelle je suis amené à exposer la BCNF :
http://www.developpez.net/forums/sho...d.php?t=281221

Bonne chance !

[Daranc]

Merci des explications et des liens je vais pouvoir me promener sur le sujet
je n'ai pas répondu plus tôt pour deux raisons 1 - je n'ai pas de retour par mail (j'ignore si le site le fait) 2 le plus grave ,je ne retrouvais pas le fil
encore merci
Daranc

[Daranc]

j'ai suivi le "thread" des courses Hippiques (Hard pour quelqu'un qui n'est confronté au SQL que depuis peu ) je ne fait que des requêtes d'interrogations . Mais comme je suis curieux j'aime savoir dans quoi je mets les pattes(au moins dans les grandes lignes) donc en résumant
j'ai soit une table unique
T_COURS avec les champs NOM_COURS;NUM_COURS ; NOM_CHEVA ;NOM_JOCKE ; NUM_DOSS ; DAT_COURS
remplie
Deauville ;7eme ; 'jolie fleur' ;'Chevailler Maurice' ;15;02/05/2006
sur quelques milliers de lignes
soit je multiplie les tables
T_JOCKEY avec les champs CODE_JOC ;NOM_JOCKE
T_CHEVAL avec les champs CODE_CHE;NOM_CHEVA
T_HIPPODROME avec les champs [I]CODE_NOC;NOM_COURS[/I]
T_NUMC avec les champs CODE_NUC;NUM_COURS
T_COURS avec les champs NOM_COURS;NUM_COURS ; NOM_CHEVA ;NOM_JOCKE ; NUM_DOSS ; DAT_COURS
et je remplie la table T_COURSE dans ce style
5;3;8;5;15;02/05/2006
ce qui allège la quantité de caractères stockés sur le disque . Les requêtes devant faire la liaison entre les tables
pour ce qui est des sur-clefs je vais devoir y retourner. (Je te dois déjà une nuit blanche à ruminer tout ça et il y a pas mal de truc qui m'échappe ; bien que j'ai lu le "thread" entièrement ,et plusieurs fois en plus )
Merci
Daranc

[fsmrel]

Bonjour Daranc,

Envoyé par Daranc

Mais comme je suis curieux j'aime savoir dans quoi je mets les pattes

De fait, la curiosité n'est pas toujours un vilain défaut.

Cela dit, pour des raisons de cohésion dans les sujets traités, pourriez-vous reporter vos observations dans la discussion :

http://www.developpez.net/forums/sho...d.php?t=281221

Merci à vous

PS. Il se trouve que si l'on gagne de la place sur le disque, c'est tant mieux, mais cela concerne le niveau physique. La théorie de la normalisation se situe à niveau complètement différent, disons mathématique, et il est fondamental de bien faire la différence.

[Daranc]

Bonjour

Envoyé par fsmrel

La théorie de la normalisation se situe à niveau complètement différent.

je croyais apercevoir la lumière mais ce n'était qu'un dysfonctionnement oculaire
je suis revenu sur ce thread parce que je restais sur la normalisation, le renvoi aux chevaux, était un des liens que j'ai put suivre (je fait une très grave allergie à l'anglais, disons même à toutes langues étrangères, sans avoir une once de xénophobie ;à signaler tout de même ;donc hélas ) la plupart des liens ne m'ont pas indiqué grand chose ; mais je ne vois pas trop comment un arrangement mathématique peut influer sur un stockage de données? Le seul avantage est d'avoir (dans cet exemple) quatre petites tables facilement modifiables et consultables ; de plus l'écriture dans la table principale doit bien faire des aller et retour avec les tables satellites pour récupérer les équivalences des code des champs! Ceci ne ralentis pas le traitement?
Daranc