Dénormalisation de table. Quand ?

[fsmrel]

Bonsoir Daranc,

Vous écrivez

je croyais apercevoir la lumière mais ce n'était qu'un dysfonctionnement oculaire

On sent comme une pointe d’amertume...

On va essayer de remédier à cela et de faire en sorte que le mirage se dissipe. Je vous ai demandé de vous rabattre sur le lien "hippique" ("Unicité d'une clef composée") parce que, à l’aide d’un exemple simple mais fort intéressant, on y a traité de la normalisation (par exemple mon message du 20/02/2007, 12h36) : définition des dépendances fonctionnelles, contraintes d’unicité et d’irréductibilité, clés candidates, surclés, forme normale de Boyce-Codd (BCNF), etc. On a montré pourquoi la table T (Jockey, Cheval, Course, Dossard) était en BCNF. Dans mon message du 21/02/2007, 01h09, j’ai rappelé la définition de la Première forme normale (1NF). J’ai mis en garde contre les définitions fantaisistes (21/02/2007, 12h49). J’ai aussi traité de la 2NF (24/02/2007, 00h18).

Recommencer tout cela au sein de la discussion " Dénormalisation de table. Quand ?" n’est pas très approprié, car si les concepts sont parfois évoqués (dépendance fonctionnelle), ils sont généralement omis et de toutes façons non formellement définis, tout devra être repris à zéro. D’autre part, le débat y est plutôt : faut-il dénormaliser ? Mais si vous y tenez, soit, restons dans cette discussion et reprenons le thème de la normalisation en remettant les compteurs à zéro (vous pouvez aussi ouvrir une nouvelle discussion).

Concernant la normalisation proprement dite, je souhaiterais qu’à un moment donné vous arriviez à repérer les erreurs du genre de celles qu’on trouve dans :

http://developpeur.journaldunet.com/...lisation.shtml

En effet, les définitions des formes normales y sont à revoir. Je ne vous donne pas les réponses tout de suite, je vous laisse réfléchir. Je peux quand même vous dire ceci :

Les définitions des NF suivantes sont fausses : 1NF, 2NF, BCNF, 4NF, 6NF.

La définition de la 3NF est vague et incomplète.

La définition de la 5NF est presque bonne (remplacer "des" par "l’ensemble des").

La définition de la 6NF n’est pas fournie et en plus ce qui en est dit est faux.

La définition de la DKNF n’est pas satisfaisante (par ailleurs elle est très particulière car ne faisant pas intervenir les dépendances fonctionnelles, multivaluées ou de jointure : laissons-la tomber).

Quitte à compter les formes normales, il y en a plus de 8, l’auteur oublie la forme normale par "Restriction-union", la "(3,3)NF". Le champ d’investigation reste ouvert. Bon je ne vous embête pas plus avec cet aspect des choses qui n’offre pas trop d’intérêt sinon théorique.

je fait une très grave allergie à l'anglais

C’est bien dommage, car la littérature anglo-saxonne est très riche pour le sujet qui nous concerne (Codd, Fagin, Rissanen, Armstrong, Date, etc.)

Je vous rappelle qu’il existe une traduction en français de l’ouvrage de référence :

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

je ne vois pas trop comment un arrangement mathématique peut influer sur un stockage de données

Tout d’abord, ceux qui ont contribué à la théorie de la normalisation sont des mathématiciens. Même si tout y est en anglais, je vous conseille de visiter le site de Ronald Fagin (sans trop vous attarder quand même...), c’est édifiant.

http://www.almaden.ibm.com/cs/people/fagin/sigmod79.pdf
http://www.almaden.ibm.com/cs/people/fagin/
http://www.almaden.ibm.com/cs/people/fagin/papers.html

Quant aux conséquences sur le stockage de données, je commence par rappeler, comme je l’ai fait précédemment dans cette discussion, qu’il faut faire une distinction très nette entre le niveau logique et le niveau physique : l’indépendance du premier est totale par rapport au second.

A ce jour, on peut voir les choses ainsi, à l’aide d’un exemple très simple. Prenons celui des courses hippiques et considérons la table suivante :

Edition (Course_Id, Course_Date_Id, Course_Date, Temps_Vainqueur, Course_Nom, Course_Lieu)

La 2NF est violée (cf. la discussion sur ces courses, mon message du 24/02/2007, 00h18).

A l’instar du ver de terre, la table doit subir une scissiparité et donner lieu donc à deux tables, celles que l’on a définies dans ce même message et qui sont en BCNF, sachant que par jointure naturelle de ces deux tables, on retrouve la table délinquante, ni plus ni moins (par application du théorème de Heath, voir à la fin de ce message).

Course (Course_Id, Course_Nom, Course_Lieu)
Edition (Course_Id, Course_Date_Id, Course_Date, Temps_Vainqueur)

Si l’on fait référence aux Create Table (toujours mon message du 24/02/2007, 00h18), chaque tuple occupe ici respectivement 16 octets et 68 octets (je ne tiens pas compte des octets dont le système a besoin).

En supposant que les volumétries soient les suivantes :

Table Course, V1 : 10 000 lignes,
Table Edition, V2 : 1 000 000 lignes,
L’encombrement global logique est le suivant :
V1 * 68 + V2 * 16 = 16 680 000 octets

Si l’on considère la table Edition qui viole la 2NF, son encombrement logique est de l’ordre de :
(16 + 64) * V2 = 80 000 000 octets.

Dans la mesure où la correspondance entre un tuple (logique) et un enregistrement (physique) est de un pour un dans les systèmes actuels :

=> Un arrangement mathématique peut manifestement avoir des conséquences sur le stockage des données.

Les volumétries ne sont peut-être pas très pertinentes concernant des courses hippiques (quoique à l’échelon planétaire...), mais si l’on remplace ces tables par celles que l’on rencontre dans le monde de la banque, de l’assurance, de la retraite, de la sécu, de la DGI, de la téléphonie, j’en passe et des meilleures, on change d’échelle et vous pouvez multiplier par un facteur 100.

Il y a quand même un élément dont il faut tenir compte : les mathématiques ne se situent ni dans le temps ni dans l’espace. Au niveau physique, on ne peut pas en dire autant !

En l’occurrence, ce qui était vrai il y a dix, vingt, trente ans ne l’est plus aujourd’hui et ce qui est vrai aujourd’hui fera sourire dans dix, vingt ou trente ans.

Ainsi, il est naturel aujourd’hui de compresser les données et les 80 000 000 d’octets n’en font peut-être plus que le tiers : contrairement à hier ou avant-hier, l’encombrement physique n’est plus égal à l’encombrement logique (au moins sur disque, pour ce qui se passe dans les buffers et espaces apparentés, c’est une autre histoire).

Et demain ? Si l’on utilise le modèle TransRelational (TM) de Steve Tarin, on dira adieu aux index, les colonnes Course_Nom et Course_Lieu de la table Edition non 2NF comporteront physiquement le même nombre de lignes que la table Course en BCNF. Les DBA de demain auront une pensée émue pour leurs anciens qui projetaient au niveau physique l’image qu’ils avaient des tuples logiques. Attention, le modèle TransRelational (TM) ne remplace pas le Modèle relationnel ! Son rôle est de réaliser la correspondance avec le niveau physique ("Trans" est l’abréviation de "Transform"). Mais quelle révolution sous le capot !

Autrement dit, le gaspillage physique pour cause de non normalisation 2NF, 3NF ou BCNF ne sera plus un argument d’optimisation. Resteront les arguments traditionnels :

— Éliminer la redondance inutile.
— 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), donc faire des économies du côté de la maintenance des applications.
— Aboutir à une base de données structurellement correcte.
— Éviter de favoriser certaines requêtes au détriment des autres.
—...

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 ?

Je ne sais pas ce que vous entendez par table principale et tables satellites.
En tout état de cause, j’utiliserai les tables que j’avais définies (toujours mon message du 24/02/2007, 00h18). En effet, vous mentionnez "Deauville, 7e", quand de mon côté je mentionne "Prix d’Amérique, édition [de l’année] 1956" (voyez les Insert) :

Course (Course_Id, Course_Nom, Course_Lieu)
Edition (Course_Id, Course_Date_Id, Course_Date, Temps_Vainqueur)
Resultat (Course_Id, Course_Date_Id, Dossard_Id, Jockey_Id, Cheval_Id, Place)
Jockey (Jockey_Id, Jockey_Nom)
Cheval (Cheval_Id, Cheval_Nom, Cheval_Date_Naissance, Propriétaire)

A partir de ces tables, on peut définir une table des résultats, qui rende transparentes les jointures :

V_COURS (Course_Nom, Course_Lieu, Cheval_Nom, Jockey_Nom, Dossard_Id, Course Date)

Table virtuelle certes, mais table quand même, résultant de la jointure des autres tables :

 
CREATE VIEW V_COURS AS
SELECT   S.Course_Nom, S.Course_Lieu, V.Cheval_Nom, J.Jockey_Nom, R.Dossard_Id, E.Course_Date
FROM     Course AS S, Edition AS E, Resultat AS R, Jockey AS J, Cheval AS V 
WHERE    S.Course_Id = E.Course_Id 
  AND    E.Course_Id = R.Course_Id  AND  E.Course_Date_Id = R.Course_Date_Id
  AND    R.Jockey_Id = J.Jockey_Id
  AND    R.Cheval_Id = V.Cheval_Id
;
 

Quant aux "Allers-retours", je suppose qu’il s’agit des entrées-sorties. En l’occurrence, la performance dépend de pas mal de paramètres, au niveau physique.

Si par exemple, vous disposez d’une machine bases de données Teradata, les E/S sont effectuées en parallèle (à charge bien sûr du DBA de s’en assurer). Avec DB2 for z/OS, même principe, vous disposez des tuyaux nécessaires pour paralléliser. Avec ce genre de systèmes, les DBA savent agir pour que l’on ne ressente pas de différence de performances. Je suppose qu’un expert Oracle tiendra un discours de cette nature. Avec mon SQL Server Express sur mon modeste PC avec un seul disque dur, je constaterai peut-être la différence, mais je n’ai pas testé.

Quoi qu’il en soit, comme je l’ai déjà écrit dans cette discussion, quand c’est pour de vrai et que j’engage ma société sur la performance de la base de données dont j’ai réalisé l’architecture, je prototype encore et encore, jusqu’à ce que la performance définie par le cahier des charges soit effective. Et croyez-moi, j’ai de sacrés souvenirs à ce sujet.

Je peux vous dire que j’ai effectué des jointures impliquant plus de 10 tables (en BCNF bien entendu), chacune comportant des dizaines de millions de lignes : si je n’ai pas eu de problèmes de performance, c’est parce que j’ai toujours commencé par une validation vigilante et sévère des modèles conceptuels de données Entités/Relations, et poursuivi au niveau physique par un choix et une organisation soignés des index et autres ressources, avec tests de performance (et de contention...) très complets, en collaboration avec l’équipe des DBA.

Une fois encore, ne mélangeons pas les niveaux.

Vous savez peut-être que les SGBD relationnels devancent les autres d’un bon paquet de longueurs, en termes de performance. Mais, quand en 1975, Ted Codd présenta le Modèle relationnel (qui n’existait bien sûr que sur le papier) à des pontes d’IBM, les questions qu’on lui posa furent du genre :

— Pourriez-vous fournir la justification économique de solutions d’entreprises.
— Une estimation des applications potentielles chez les utilisateurs, par secteur d’activité et par type d’application si possible.
— Les méthodes de programmation, leur compatibilité et un comparatif avec notre standard actuel de bases de données, DL/1.

J’en passe et des meilleures. Pauvre mathématicien égaré chez les managers...
Ses collègues réussirent à obtenir qu’un prototype fut réalisé : System R. La révolution était commencée.

Si vous vous sentez toujours victime d’un dysfonctionnement oculaire, précisez ce qui vous gêne plus particulièrement...
__________________________________

NB. Théorème de Heath (1971) :
Soit la variable relationnelle R (A, B, C) dans laquelle A, B et C sont des ensembles d’attributs de R.
Si R satisfait à la dépendance fonctionnelle A -> B, alors R est égale à la jointure de ses projections sur {A,B} et {A,C}.
(Le terme "variable relationnelle" peut être traduit informellement par le terme "table").

[Daranc]

Bonjour
Je vous remercie de prendre le temps ,de vous penchez sur des problèmes qui avouons le n'ont pas d'existence fondamentale . comme je vous l'avez précisé seule la curiosité m'a amené sur ce débat. Mais avant de vous dire ou je bloque je vais déjà lire (et relire) ce que vous m'avez cité. je tenais à vous rassurer sur ce que vous nommez "certaine amertume" , c'est garder le moral à tous les coups (un peu comme lorsqu'un lumbago vous oblige de descendre à quatre pattes d'un canapé , vous pensez au moins je repère mes pantoufles).Et pour ce qui est des mathématiques les ensembles n'ont pas ce que j'ai préféré (surtout attaqué ça en cinquième et abandonné en quatrième le suivi de l'EN) mais je vais rouvrir mes bouquins . de toute façon je ne reviendrais qu'après avoir essaye de (ou réussi à) comprendre
Cordialement
Daranc

[Daranc]

Bonjour
je crois que pour la normalisation j'ai compris le but (sinon la construction)
c'est l'exemple des courses qui me bloquait un peu :(un jockey peut monter le même cheval dans la même course à un an d'intervalle et ça, ça me chagrinais) j'ai en suivant les liens donnés (et en recherchant les définitions des termes employés) trouvé un exemple de bibliothèque.
un fournisseur change d'adresse et ce sont tous les livres y référant qu'il faut modifier alors que si il est relié aux livres par une table fournisseurs seul sa fiche fournisseurs est à mettre à jour ;Ce qui prends trois minutes ( compris le temps de parler du film du dimanche, un navet, soir avec la collègue ) autrement une semaine à plein temps pour un petit fournisseur ,sans certitude de ne rien oublier; de même on peut rajouter un fournisseur qui n'a pas encore fournit quoi que ce soit .
Bon j'ai pas fini de décortiquer , j'y retourne
Cordialement
Daranc

[Thes32]

Salut
y a rien de plus intelligents que de normailiser une table. une table denormaliser manque de cohérence.
normalise jusqu'au BCFN ça ira mieux