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

[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 ?

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 :

Code :

1
2
3
4
5
6
7
8
9
 
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

[StringBuilder]

Bonjour,

Je viens ajouter un petit grain de sable à l'édifice.

Je travaille sur un ERP, qui s'appelle Generix.

Et cet outils m'a ouvert les yeux sur une nouvelle façon de modéliser les bases de données.

En effet, outre normaliser/dénormaliser, il y a une étape d'abstraction à prendre en compte.

En effet, cet ERP par du principe que, dans une entreprise, j'ai par exemple un nombre infini de tiers :
Client, Fournisseur, Prospects, Dépôts, Magasins, Centrales d'achat, Vendeurs, etc.

Idem pour les "évènements" : habituellement, on a les flux suivants en Gescom, mais il peut y en avoir d'autres :
devis, commande, livraison, facture ; avoir ; retour ; que ce soit en achat ou en vente.

La normalisation préconise de faire une table par type de tiers (donc une table client, une table magasin, une table vendeur, etc.)

Idem avec les évènements.

Et c'est vite le bordel :
1/ On a 500 tables avec des structures communes
2/ Le jour où on a besoin d'une nouvelle fonctionnalité (je introduire une notion de carriste dans ma base pour tracer qui a mis quoi où et quand dans mon dépôt), je dois recréer des tables pour gérer mon carriste, et des tables pour gérer la mise en rayon dans mon dépôt.

L'abstraction joue alors son rôle ici :
- Ben mon carriste, c'est un tiers comme un client ou un magasin
- Ben le fait de placer un produit en rayon, c'est exactement comme une livraison ou un avoir fournisseur

Et donc après avoir créé 500 entités dans le MCD, on mutualise les tables, en ajoutant des colonnes dans la clé primaire.

Dans Generix par exemple, dans la table tiers, la clé est composé de 3 colonnes :

CODSOC : Code société (ceci permet à plusieurs entités juridiques d'utiliser la même base de données, avec notions de hiérarchie dans les tables de référentiel)
TYPTIE : Type de tiers (CLI = Client, REP = Vendeur, DEP = Dépôt, etc.)
SIGTIE : Identifiant du tiers

Et pour les événements :

CODSOC
ACHVTE : Indicateur Achat ou Vente
TYPEVE : Type d'évènement (CDE = Commande, FAC = Facture, LIV = livraison, etc.)
NUMEVE : Numéro de la commande/livraison/...

L'ensemble des éléments venant s'ajouter à la clé composite tels que TYPTIE, ACHVTE, TYPEVE sont gérés dynamiquement dans des tables de correspondances, ce qui permet d'étendre le modèle des données sans créer de nouvelles tables.

Et même plus loin : un table permet d'étendre les colonnes d'une table pour une clé donnée, et ainsi d'avoir virtuellement autant d'attributs spécifiques dans chaque table

Et une table "fourre tout" permet de stocker dans une structure commune l'ensemble des tables de référence de la base (la liste des ACHVTE/TYPEVE/NUMEVE, mais aussi la description de ce qu'est ma zone 824 sur mon événement de type LIV, et même la liste de valeurs autorisés dans cette zone).

En gros, là où normalement, on aurait besoin de plus de 100 tables pour gérer un flux GESCOM standard, Generix n'a besoin en tout et pour tout que... d'une dizaine de tables à tout casser.

Ceci a plusieurs avantages :
1/ Simplicité du modèle : un nombre restrein de tables à utiliser
2/ Généricité des requêtes et des binaires : le binaire qui gère un client ou un dépôt par exemple est identique et utilise les mêmes requêtes : seul la valeur du TYPTIE diffère dans les requêtes
3/ Fluidité de la lecture des tables : malgré des alias à la mord-moi-le-noeud, un TYPTIE ou un TYPEVE collé dans une requête permet d'identifier dans la seconde sur quelle "table" on travaille

Niveau performances, je n'ai jamais trop comparé, mais je doute très fortement que cela impacte quoi que ce soit, et même au contraire.

Donc avant de dénormaliser comme un porc, je pense qu'il est intéressant de faire cette passe d'abstraction du modèle, afin de le simplifier.

[fsmrel]

Envoyé par StringBuilder

La normalisation préconise de faire une table par type de tiers (donc une table client, une table magasin, une table vendeur, etc.)

Vous confondez normalisation et modélisation. La normalisation n’a rien à voir dans votre histoire : elle recommande seulement d’appliquer le théorème de Heath quand une table viole la BCNF, le 1er théorème de Fagin en cas de viol de la 4NF, etc.

Votre problème relève de la modélisation : au niveau conceptuel, il est évident qu’en présence de 36 types de tiers, tout concepteur normalement constitué mettra en œuvre une entité-type (ou une classe) TIERS en relation avec une entité-type (ou une classe) TYPE_DE_TIERS. Au niveau tabulaire, ces deux entités-types feront chacune l’objet d’une table.

Envoyé par StringBuilder

Dans Generix par exemple, dans la table tiers, la clé est composé de 3 colonnes :
CODSOC : Code société (ceci permet à plusieurs entités juridiques d'utiliser la même base de données, avec notions de hiérarchie dans les tables de référentiel) :
TYPTIE : Type de tiers (CLI = Client, REP = Vendeur, DEP = Dépôt, etc.)
SIGTIE : Identifiant du tiers

Si d’aventure l’identifiant de deux tiers distincts ne peut pas prendre la même valeur, alors votre triplet de colonnes ne peut pas représenter une clé mais seulement une surclé.

Envoyé par StringBuilder

Je viens ajouter un petit grain de sable à l'édifice [...] Avant de dénormaliser comme un porc [...]

Evitez surtout à l’avenir de commettre des erreurs de casting. Vous pouvez donc tranquillement remettre votre grain de sable dans votre seau et réfléchir à ce que l'on entend par modéliser proprement.

[StringBuilder]

Non, dans le cas que je décrit, SIGTIE peut être identique selon les TYPTIE et les CODSOC.

En ce qui concerne l'erreur de casting, j'admet que je ne suis pas pointilleux sur les mots.

En revanche, je ne vais pas me laisser limite insulter de la sorte.

La plupart du temps, la dénormalisation est motivée avant tout par une tentative de "simplification" du modèle des données.

Les cas de dénormalisation à des fins d'optimisation sont rares, même si c'est cet argument qui ressort lorsqu'on demande pourquoi, car il est plus convainquant qu'un simple "j'en ai marre d'avoir 200 tables dans mes requêtes".

Il n'est pas rare de trouver, au moment de la conception, des tables avec une adresse dans la commande.

"Ben oui, mais c'est parceque mon client il a déjà une adresse, le fournisseur aussi, et du coup ça m'ennuie de faire une table de plus pour stocker les adresses des livraisons".

Ca, c'est un cas de dénormalisation typique, qui est évité sans problème par la phase d'abstraction que j'ai énnoncé, et que nombre de personnes ne font pas : c'est clairement pas à l'école qu'on apprend ça, et encore moins dans les SSII où les développeurs/chef de projets n'ont aucun suivit pédagogique, aucun moyen de capitaliser d'un projet à l'autre.

[fsmrel]

Envoyé par StringBuilder

Les cas de dénormalisation à des fins d'optimisation sont rares, même si c'est cet argument qui ressort lorsqu'on demande pourquoi, car il est plus convainquant qu'un simple "j'en ai marre d'avoir 200 tables dans mes requêtes".

Sur quelles observations vous basez-vous pour être aussi affirmatif quant à la motivation et la rareté de la dénormalisation volontaire ? Pour ma part, je suis beaucoup plus prudent, j’ai surveillé le comportement de mes collègues : je ne dis pas qu’ils ne sont pas prompts à dénormaliser, aussi dois-je les convaincre de n’en rien faire, preuve à l’appui, à titre d’exemple voyez ce que je rapporte ici (dénormalisation de la table des chèques).

Si on a l’intuition que la dénormalisation vaut le coup, on met en œuvre un prototype pour comparer les performances et mettre en balance les effets secondaires. Mes travaux de prototypage m’ont toujours montré que l’intuition est en l’espèce bien mauvaise conseillère.

Par ailleurs, je n’ai jamais vu quelqu’un programmer une requête faisant mention de 200 tables, et pourtant j’en ai vu des choses bizarres en plus de trente-cinq ans de pratique des bases de données relationnelles (et bien plus si l’on tient compte des bases de données non relationnelles), dans bien des secteurs (banque, assurance, industrie, distribution, retraite, j’en passe et des meilleures), tant dans le domaine de la conception, de l’audit, du conseil, de l’animation des équipes de développement, du rattrapage en catastrophe de projets partant à vau-l’eau. Maintenant, pour reprendre votre exemple des tiers, en supposant qu’il y ait 200 types possibles de tiers et que le concepteur en ait inféré 200 entités-types, donc autant de tables, il est évident qu’il s’agirait là d’une maladresse (euphémisme) due à son incompétence.

Envoyé par StringBuilder

Il n'est pas rare de trouver, au moment de la conception, des tables avec une adresse dans la commande.

Que la présence d’une colonne ADRESSE dans l’en-tête d’une table COMMANDE soit valide ou non doit être démontré. Tout dépend des règles de gestion des données, donc des dépendances fonctionnelles qui en découlent. Si la table COMMANDE comporte cette colonne ADRESSE et si la BCNF est respectée, il n’y a rien à redire. Si la BCNF est violée, il faut appliquer le théorème de Heath et décomposer la table.
Cela dit, si on n’aime pas développer des requêtes comportant des jointures (puisque c'est elles qui sont manifestement en cause), alors plutôt que dénormaliser, on met en oeuvre des vues qui « encapsulent » ces jointures.

Envoyé par StringBuilder

Ca, c'est un cas de dénormalisation typique, qui est évité sans problème par la phase d'abstraction que j'ai énoncée, et que nombre de personnes ne font pas

Depuis plus de trente ans, les concepteurs procèdent à l’abstraction et créent des diagrammes conceptuels (à la Chen ou à la Merise ou à la NIAM, etc.), ou encore des diagrammes de classes. Tous ces diagrammes font l’objet d’une traduction pour donner lieu aux tables attendues, et ce ne sont pas les outils qui manquent pour automatiser la tâche (WinDesign, Power AMC, etc.) S’il reste des tables non normalisées, on complète le travail au niveau tabulaire en appliquant les théorèmes déjà mentionnés (Heath, Fagin).

De votre côté, vous avez votre propre technique d’abstraction, orientée « fourre-tout » comme vous le dites dans votre message précédent (« une table "fourre tout" permet de stocker dans une structure commune »), mais heureusement il y a d’autres techniques, moins ad-hoc, qu’elles soient ascendantes, descendantes ou mixtes. Pour les étudier, je vous recommande la lecture des bons auteurs, tels qu’Yves Tabourier (De l’autre côté de Merise) ou Dominique Nanci (Ingénierie des systèmes d’information : Merise deuxième génération). Incidemment, je fais observer qu'on ne modélise pas dans le but de réduire le plus possible le nombre des entités-types (ou classes) au niveau conceptuel (donc le nombre de tables au niveau tabulaire), mais pour représenter un univers du discours ayant sémantiquement un sens, sans amalgamer des données de types distincts.

Envoyé par StringBuilder

dans le cas que je décris, SIGTIE peut être identique selon les TYPTIE et les CODSOC.

Je suppose que par identité vous voulez dire égalité. Quoi qu’il en soit, où faites-vous figurer les données qui servent pour les clés alternatives, telles que le numéro Siret des clients, les matricules des vendeurs, bref, toutes les données identifiantes « naturelles », c'est-à-dire connues des utilisateurs ?

Envoyé par StringBuilder

c'est clairement pas à l'école qu'on apprend ça, et encore moins dans les SSII où les développeurs/chef de projets n'ont aucun suivit pédagogique, aucun moyen de capitaliser d'un projet à l'autre.

Concernant les étudiants, si l’on se réfère chez DVP aux forums Merise, UML et Schéma, il y en a pas mal qui posent des questions ayant trait aux problèmes de modélisation que leur soumettent leurs professeurs.

Quant aux SSII, il y en a qui font malheureusement l’impasse, reconnaissons-le, mais il ne faut pas généraliser. En ce qui me concerne, j’ai fait partie pendant plus de trente ans de la même SSII ; j’ai évidemment passé le plus clair de mon temps sur le terrain, mais cela ne m’a pas empêché de monter une bonne dizaine de cours et de former quelques centaines de nos ingénieurs (déjà expérimentés), notamment en ce qui concerne la modélisation des bases de données, de former des formateurs et suivre les ingénieurs qui le souhaitaient. Accessoirement, outre nos clients, j’ai même formé la concurrence, comme quoi...

[StringBuilder]

Envoyé par fsmrel

Sur quelles observations vous basez-vous pour être aussi affirmatif quant à la motivation et la rareté de la dénormalisation volontaire ?

Sur les miennes
Ce que je veux dire, c'est que j'ai personnellement très rarement vu des cas de besoin de dénormalisation (je ne parle pas pour un DataWare, je parle pour une base applicative) pour des raisons purement de performances.
Par contre, des dénormalisation pour simplifier des développements, améliorer les performances en évitant de faire plusieurs requête pour récupérer un résultat (l'exemple des téléphones est bon : si j'ai 3 téléphone pour une personne, soit je fais une jointure et j'ai 3 lignes par personnes, soit je fais une requête par personne : si c'est pour éditer la liste de toutes les personnes avec un tableau de leurs numéros de téléphones, c'est assez moyen).
Voilà, moi c'est ce genre de cas que j'ai principalement vu, poussant des dev à dénormaliser.

Envoyé par fsmrel

Que la présence d’une colonne ADRESSE dans l’en-tête d’une table COMMANDE soit valide ou non doit être démontré.

Je ne parle pas d'une clé étrangère "adresse" dans la commande, mais bien de l'adresse complète dans la commande. Autant pour des raisons de suivit, s'il y a plusieurs adresses, je conçois qu'on fasse une FK vers la table des adresses du client et/ou même vers une table d'adresses éphémères remplie à chaque saisie de commande, autant il n'y a pas lieux de stocker une adresse entière dans la commande.

Envoyé par fsmrel

Je suppose que par identité vous voulez dire égalité. Quoi qu’il en soit, où faites-vous figurer les données qui servent pour les clés alternatives, telles que le numéro Siret des clients, les matricules des vendeurs, bref, toutes les données identifiantes « naturelles », c'est-à-dire connues des utilisateurs ?

SIGTIE est un matricule justement : vu qu'on a déjà une donnée identifiante "naturelle", c'est celle-ci qui est utilisée au lieu d'un auto-incrément dénué de sens, obligeant ensuite les utilisateurs à jongler avec plusieurs informations identifiantes. Ceci évite aussi la création d'un index supplémentaire inutile.
Les informations de SIRET, etc. sont bien évitement stockées soit dans la table des tiers (lorsqu'elles sont succeptibles d'être communes à suffisament de types de tiers -il vaut mieux parfois sacrifier une normalisation à l'extrême au profit de la simplicité lorsque la simplicité s'applique au plus grand nombre-) ou dans des tables dépendantes.

PS : C'est quoi ta SSII que je signe tout de suite ?

[fsmrel]

Envoyé par StringBuilder

j'ai personnellement très rarement vu des cas de besoin de dénormalisation (je ne parle pas pour un DataWare, je parle pour une base applicative) pour des raisons purement de performances.
Par contre, des dénormalisation pour simplifier des développements, améliorer les performances en évitant de faire plusieurs requête pour récupérer un résultat

Il y a là une contradiction : vous commencez par dire que vous avez très peu connu de cas dans lesquels il fallait dénormaliser au nom de la performance, et dans la foulée, vous affirmez qu’il faut dénormaliser pour améliorer les performances.

Je rappelle une fois de plus que lorsqu’on bâtit un prototype pour mesurer les bienfaits que procurerait a priori la dénormalisation quant à la performance, on se rend compte qu'en réalité cette dénormalisation n’apporte rien, sinon des effets secondaires fâcheux. Je me souviens des réactions de la direction de la Production d’une très grande banque, d’abord fort septique quant au principe même du prototypage des performances, puis, après en avoir tâté, l’imposer à l’ensemble des projets : comme dit l’autre, « better prevent than cure ». Toujours dans cette banque, pour le projet dont j’avais validé le modèle, des mois avant que les développements ne commencent on connaissait la performance de la base de données normalisée suite au prototypage. En conséquence aucune dénormalisation.

Lors des réponses d’appel d’offre et propositions diverses faites aux DSI, à chaque fois que j’ai eu à engager mon entreprise quant à la performance, jamais je n’ai par exemple fourni des durées de traitements sortant d’un chapeau, je ne suis pas un commercial ; invariablement je me suis seulement engagé à d’abord bâtir un prototype et seulement après le verdict de celui-ci, enfin quantifier pour de vrai pour que le client ait une vue objective. Et n’oublions pas qu’il y a les clauses de pénalité qui sont appliquées en cas de non satisfaction de l’engagement.


En outre, s’il s’agit de simplifier les développements, je rappelle ce que j’ai écrit :

Envoyé par fsmrel

Si on n’aime pas développer des requêtes comportant des jointures (puisque c'est elles qui sont manifestement en cause), alors plutôt que dénormaliser, on met en oeuvre des vues qui « encapsulent » ces jointures.

=> Votre argument « dénormalisation pour simplifier des développements » n’est pas recevable.

Envoyé par StringBuilder

Voilà, moi c'est ce genre de cas que j'ai principalement vu, poussant des dev à dénormaliser

Les développeurs ne sont pas concernés, c’est aux DBA de se prononcer, suite aux résultats du prototypage au besoin.

Envoyé par StringBuilder

Je ne parle pas d'une clé étrangère "adresse" dans la commande, mais bien de l'adresse complète dans la commande. [...]il n'y a pas lieux de stocker une adresse entière dans la commande.

Qui a parlé de clé étrangère ? Pour ma part j’ai uniquement parlé de la présence d’une colonne ADRESSE dans l’en-tête de la table COMMANDE. Bien évidemment, l’adresse peut faire l’objet de plus d’une colonne (nom de la voie, numéro dans la voie, code postal, etc.), et ça n’est pas l’instinct mais le principe du respect de la BCNF (à défaut de la 6NF) qui doit conduire à la conservation ou non de l’adresse dans la table COMMANDE. Combien de fois devrai-je le répéter ?

Envoyé par StringBuilder

SIGTIE est un matricule justement : vu qu'on a déjà une donnée identifiante "naturelle", c'est celle-ci qui est utilisée au lieu d'un auto-incrément dénué de sens, obligeant ensuite les utilisateurs à jongler avec plusieurs informations identifiantes. Ceci évite aussi la création d'un index supplémentaire inutile.

Pour commencer, vous mélangez les niveaux : nous sommes au niveau conceptuel (ou logique, comme vous voudrez) et déjà vous parlez d’index, c'est-à-dire que vous tricotez sémantique et plomberie. Chaque chose en son temps.

En ce qui concerne la colonne faisant l’objet de l’auto-incrémentation, autrement dit la clé primaire de la table, cette colonne ne sert que pour effectuer les jointures et autres opérations relationnelles, elle doit rester cachée à l’utilisateur, lequel n’est concerné que par les propriétés naturelles, telles que le numéro Siret des tiers. Incidemment, je note que la colonne SIGTIE dont vous faites mention n’est pas identifiante, puisqu’il faut l’associer aux colonnes CODSOC et TYPTIE pour garantir la propriété d’unicité.

Envoyé par StringBuilder

Les informations de SIRET, etc. sont bien évitement stockées soit dans la table des tiers (lorsqu'elles sont succeptibles d'être communes à suffisament de types de tiers -il vaut mieux parfois sacrifier une normalisation à l'extrême au profit de la simplicité lorsque la simplicité s'applique au plus grand nombre-) ou dans des tables dépendantes.

Que signifie cette partie de phrase : « lorsqu'elles sont susceptibles d'être communes à suffisamment de types de tiers » ? Le numéro Siret des entreprises fait-il bien l’objet d’une colonne qui lui est propre ?

Que signifie cette 2e partie de phrase : « il vaut mieux parfois sacrifier une normalisation à l'extrême au profit de la simplicité lorsque la simplicité s'applique au plus grand nombre » ? Pour ma part, je fais observer que la normalisation des tables et la simplicité font particulièrement bon ménage.

Je crains que nous ne donnions pas le même sens au terme « normalisation ». La normalisation des bases de données est une branche des mathématiques appliquées, avec laquelle on s’appuie sur une axiomatique (les axiomes et règles d’Armstrong) et des théorèmes (essentiellement Heath et Fagin), des algorithmes et divers outils théoriques (voyez par exemple The Theory of Relational Databases de David Maier) pour prouver que les tables composant une base de données sont en 2NF, 3NF, BCNF, 4NF, 5NF, 6NF. Si vous l’entendez ainsi, alors vous conviendrez que la 2e partie de votre phrase n’est pas pertinente.

[iberserk]

Sur les miennes
Ce que je veux dire, c'est que j'ai personnellement très rarement vu des cas de besoin de dénormalisation (je ne parle pas pour un DataWare, je parle pour une base applicative) pour des raisons purement de performances.

Peut être ne parle t'on pas du même niveau d'expérience?

J'en suis même convaincu.

[StringBuilder]

Envoyé par fsmrel

Il y a là une contradiction : vous commencez par dire que vous avez très peu connu de cas dans lesquels il fallait dénormaliser au nom de la performance, et dans la foulée, vous affirmez qu’il faut dénormaliser pour améliorer les performances.

Il y a performances et performances.

Dans ce topic, il est question de démontrer que dénormaliser pour améliorer les performances des requêtes est souvent illusoire.
Sur ce point, je suis parfaitement d'accord, il est très rare que la suppression d'une jointure permette de changer significativement le temps d'exécution d'une requête, alors que l'alourdissements des tables peut rapidement s'avérer catastrophique.

EN REVANCHE, pour reprendre l'exemple des téléphones, si j'ai 3 colonnes téléphone dans ma table, alors en une seule requête, je récupère une ligne par tiers, avec ces trois téléphones.
Dans le code de mon application, les SELECT et UPDATE seront alors extrêment rapide et simples : une seule requête, point barre.
Maintenant, si j'ai une table de téléphones, j'aurai soit :
- Une seule requête qui ramène X lignes par tiers (avec X = nombre de numéro de téléphone), ce qui va effondrer mes performances réseau entre mon serveur SGBD et mon serveur d'application.
- Deux requêtes, une pour les tiers, puis autant de requêtes qu'il y a de tiers pour récupérer les numéros de téléphone tiers par tiers : cette fois, on a une très forte dégradation des performances sur la base de données (des centaines de requêtes supplémentaires, même si elles sont simples, sur un index et avec peut de résultat, ça se fait forcément ressentir), et sur mon réseau pour faire transiter tous les résultats des requêtes.
- Deux requêtes, une pour récupérer tous les tiers, puis une pour récupérer tous les numéros de téléphones, avec cette fois un parcours des deux curseurs en // dans la même boucle : à nouveau, on a deux requêtes au lieu d'une, donc aucun intérêt au niveau des performances sur le SGBD et le réseau.
- Une seule requête avec un nombre figé d'auto-jointures sur de type OUTER JOIN sur mes numéros, afin de reproduire la structure de la table dénormalisée. On perds en performances (même s'il s'agit de jointures sur des index, c'est forcément plus lent que de la lecture séquentielle dans une seule table).
=> Dans ces quatre cas, en termes de coûts de développements pour faire les sélections et modifications dans la table, seront bien plus importants : il faut gérer l'existence des téléphones avant de les modifier, gérer le cas de la suppression, ne pas se gourrer dans l'ordre, etc.
=> Dans ces trois cas, on alourdis considérablement les traitements de l'application cliente, qui risque de rapidement avoir des performances dégradées inutilement.

C'est de ce genre d'optimisation qu'il est question lorsque je parle de dénormaliser pour des problèmes de performances. Ca, c'est des cas concrets, qui arrivent tous les jours à chacun de nous.

Envoyé par fsmrel

En outre, s’il s’agit de simplifier les développements, je rappelle ce que j’ai écrit :
=> Votre argument « dénormalisation pour simplifier des développements » n’est pas recevable.

Faudrait pas non plus me prendre pour un Mickey BEP assistant comptable en stage dans un service informatique non plus.
Je ne parle pas du tout de simplifier les requêtes SQL, on s'en cogne des requêtes SQL. Moi je parle de simplifier les algorythmes des applications consommatrices. Et ça, c'est une véritable réalité. Y'a qu'un chercheur de l'EDNAT pour croire qu'un modèle hyper normalisé est super optimisé pour une application de production : le SGBD, il ne faut pas oublier que c'est qu'un moyen de stocker les données. Si la représentation des données n'est pas compatible avec la représentation qu'on en a dans l'outils qui les traites, là il y a un vrai problème, bien plus grave qu'un souci de dénormalisation.

Envoyé par fsmrel

Les développeurs ne sont pas concernés, c’est aux DBA de se prononcer, suite aux résultats du prototypage au besoin.

Détrompe-toi. Le développeur ne se contente pas de faire des INNER JOIN dans son code : il met en place aussi toute l'intelligence du code autour des requêtes. Si pour faire une action triviale, il doit faire X requêtes, des traitements ittératifs à n'en plus finir en mémoire sur le serveur d'application, alors SI, il a son mot à dire.

Envoyé par fsmrel

Incidemment, je note que la colonne SIGTIE dont vous faites mention n’est pas identifiante, puisqu’il faut l’associer aux colonnes CODSOC et TYPTIE pour garantir la propriété d’unicité.

CODSOC et TYPTIE sont des données contextuelles, dont l'utilisateur n'a que faire. Donc si, SIGTIE est un identifiant unique pour un contexte donné (les clients du magasin, les fournisseur de la centrale d'achat, etc.) C'est donc une clé toute trouvée, dans le plus strict respect de la conceptualisation.

[CinePhil]

Maintenant, si j'ai une table de téléphones, j'aurai soit :
- Une seule requête qui ramène X lignes par tiers (avec X = nombre de numéro de téléphone), ce qui va effondrer mes performances réseau entre mon serveur SGBD et mon serveur d'application.

Qu'est-ce qui vous permet d'affirmer que cet "effondrement" va avoir lieu ?
C'est pourtant ainsi que fonctionnent toutes les applications correctement développées et interrogeant une BDD normalisée. Il ne me semble pas qu'on entende parler tous les matins de serveurs ou de réseaux qui plantent à cause de cette architecture !

- Deux requêtes, une pour les tiers, puis autant de requêtes qu'il y a de tiers pour récupérer les numéros de téléphone tiers par tiers : cette fois, on a une très forte dégradation des performances sur la base de données (des centaines de requêtes supplémentaires, même si elles sont simples, sur un index et avec peut de résultat, ça se fait forcément ressentir), et sur mon réseau pour faire transiter tous les résultats des requêtes.

N'importe quoi ! Le développeur qui programme une telle ineptie est pas loin de la porte !
Vous avez d'ailleurs pointé les conséquences de cette horreur !

- Deux requêtes, une pour récupérer tous les tiers, puis une pour récupérer tous les numéros de téléphones, avec cette fois un parcours des deux curseurs en // dans la même boucle : à nouveau, on a deux requêtes au lieu d'une, donc aucun intérêt au niveau des performances sur le SGBD et le réseau.

La fréquentation des forums de Developpez.com m'a montré qu'il y a effectivement des développeurs ne connaissant que très peu les bases de données qui procèdent ainsi. Encore une mauvaise solution évidemment !

- Une seule requête avec un nombre figé d'auto-jointures sur de type OUTER JOIN sur mes numéros, afin de reproduire la structure de la table dénormalisée. On perds en performances (même s'il s'agit de jointures sur des index, c'est forcément plus lent que de la lecture séquentielle dans une seule table).

Et si des tiers ont davantage de numéros de téléphone qu'il n'y a de jointures, on multiplie de nouveau les lignes ou on perd de l'information.
No good !

=> Dans ces quatre cas, en termes de coûts de développements pour faire les sélections et modifications dans la table, seront bien plus importants : il faut gérer l'existence des téléphones avant de les modifier, gérer le cas de la suppression, ne pas se gourrer dans l'ordre, etc.

C'est pourtant assez basique et ça fonctionne ainsi dans des milliers d'applications sans problème. Si on va au bout de votre raisonnement, autant ne faire qu'une seule table dans la BDD ! Si je me souviens bien, Supercalc était un tableur mono-feuille avec lequel on pouvait déjà faire pas mal de choses, c'était dans les années 80...

=> Dans ces trois cas, on alourdis considérablement les traitements de l'application cliente, qui risque de rapidement avoir des performances dégradées inutilement.

C'est curieux quand même que tant d'applications soient aussi mauvaises !

[StringBuilder]

Envoyé par CinePhil

Qu'est-ce qui vous permet d'affirmer que cet "effondrement" va avoir lieu ?

Multiplier par 3 la charge du réseau, c'est une affirmation en l'air ?
Pourquoi alors dans les fermes de serveurs, on a des lignes 10base1000 dédiés entre chaque serveurs depuis 10 ans alors que sur les réseaux d'entreprise, on est encore la plupart du temps en 10base100 ?
Pourtant les utilisateurs qui utilisent les applications consomment pas mal de ressource réseau... Mais rien comparé à ce que consommera un serveur de traintement. C'est justement pour cette raison qu'il est intéressant de limiter au maximum les échanges réseau entre le serveur de traitement et le serveur de base de données (donc essayer de faire le plus de choses possibles en une seule requête... et éviter de devoir faire des produits cartésiens inutiles).

Envoyé par CinePhil

N'importe quoi ! Le développeur qui programme une telle ineptie est pas loin de la porte !
Vous avez d'ailleurs pointé les conséquences de cette horreur !

C'est pourtant vers ce genre de choses qu'on s'oriente avec les framework SQL Less.
L'ERP sur lequel je travaille utilise ce système pour construire dynamiquement des écrans : dans un fichier XML on indique qu'on veut parcourir un objet métier (qui correspond généralement à une table) et pour chaque ligne, on va parcourir d'autres objets métiers : on a donc rapidement des centaines (voir des milliers) de requête qui sont exécutées pour un seul écran.
=> Et si dans un nombre limité de cas, c'est une ineptie, la plupart du temps il serait "impossible" de faire tout en une seule requête.

Envoyé par CinePhil

C'est pourtant assez basique et ça fonctionne ainsi dans des milliers d'applications sans problème. Si on va au bout de votre raisonnement, autant ne faire qu'une seule table dans la BDD ! Si je me souviens bien, Supercalc était un tableur mono-feuille avec lequel on pouvait déjà faire pas mal de choses, c'était dans les années 80...

Non. Si on suit mon raisonnement, on se pose la question de l'intérêt de faire une table supplémentaire mono-propriété alors que l'ajout de 3 colonnes dans une autre table éviterait des jointures inutiles. D'autant que (et je maintiens, je parle de ce cas précis) il n'aura de toute façon pas de redondance, et qu'au plus on aura des valeurs NULL dans ces colonnes supplémentaires.

[iberserk]

A StringBuilder:
C'est un peu du tous contre vous là...
Vous êtes donc le seul à détenir la vérité...

Vous venez sur ce forum avec une pseudo (et unique?) expérience de la base de données (d'un ERP qui plus est...) en avançant perpétuellement des inepties (problèmes réseaux, effondrement de performance etc.) qui semblent sortir d'on ne sait où (enfin si de votre tête...) et surtout qui semblent être là pour vous auto-convaincre que le modèle que vous utilisez quotidiennement (qui n'est pas le votre qui plus est!) est le Saint-Graal de la modélisation.

Les gens qui vous répondent ont pour la plupart une voir des dizaines d'années d'expériences derrière eux (je ne m'inclus pas dedans...), et autant d'années de participation et de publication sur ce forum...

Je pense qu'ils ont une autre crédibilité que vous...

[fsmrel]

Bonsoir,


iberserk a bien résumé la situation. La communication avec StringBuilder est pratiquement impossible, car pour lui la modélisation des données doit être compatible avec la représentation qu’en fait l’outil (par référence à ce qu’il écrit, je ne pense pas me tromper : « Si la représentation des données n'est pas compatible avec la représentation qu'on en a dans l'outils qui les traites, là il y a un vrai problème »), ce qui revient à dire que la modélisation doit être faite en fonction de l’outil. Il y a là un viol manifeste de la règle d’indépendance de la modélisation des données : l’art de la modélisation exige que soit seulement pris en compte le « Quoi », l’ensemble des règles de gestion des données recensées dans le dossier de conception, indépendamment des outils et des traitements. Le « Comment » ne relève pas de la modélisation des données au niveau conceptuel. C’est au cours d’une étape ultérieure que l’on traite de l’adéquation du modèle et de la représentation qu’en a l’outil, par exemple en mettant en jeu le mécanisme des vues.

Une précision concernant la [dé]normalisation : ce que nous appelons normalisation des bases de données (de la 2NF à la 6NF) est basé fondamentalement sur l’étude des dépendances fonctionnelles (et de leurs grandes soeurs) et joue un rôle crucial quant à la qualité et la robustesse du modèle, mais manifestement cela ne semble pas concerner StringBuilder qui ne jure que par un ERP où l’on « modélise » par exemple des entités-types « fourre-tout » (sic !)

A son sens, dénormaliser (« comme un porc » bien entendu...) une table qui est en xNF ne consiste pas à se limiter à respecter seulement la (x-1)NF ou la (x-2)NF, etc., sinon il nous l’aurait fait savoir, mais vraisemblablement à « optimiser », ce qui n’a rien à voir (comme le rappelle Chris Date dans Logic and Databases, « Denormalization Considered Harmful », la confusion entre les termes « dénormalisation et « optimisation » est du reste très fréquente). Qui plus est, pour StringBuilder dénormaliser est du ressort des développeurs !

CinePhil aura beau faire des observations frappées au coin du bon sens, il s’agit désormais d’un dialogue de sourds. Mais comme cette discussion fait partie de la série « Débat » et peut servir à d’autres, de mon côté je m’arme de patience et vais prendre le relais...

[fsmrel]

Envoyé par StringBuilder

[...] l'alourdissements des tables peut rapidement s'avérer catastrophique.

« L’alourdissement » est une conséquence, soit de la normalisation en 1NF d’une table qui est en 0NF, soit de la dénormalisation d’une table en xNF (x > 1). Considérez ce qui est écrit à propos de la conséquence de la normalisation en 1NF de la table Membre qui n'est initialement qu'en 0NF (figure 2.3) : on assiste à une inflation « katastrophique » des redondances. Mais c’est la normalisation en 2NF et au delà (merci Ted Codd !) qui à son tour provoquera un amaigrissement aussi spectaculaire que salvateur et décisif.

Autrement dit, violer la 2NF est radical pour rendre des tables obèses. Pour reprendre l’exemple auquel je fais référence, le poids de la table Membre non normalisée « pèse » en tout 36,85 Mo, alors qu’après normalisation le poids (tables Membre + Message) n’est plus que de 26,40 Mo.

Envoyé par StringBuilder

[...] si j'ai 3 colonnes téléphone dans ma table [...]

Supposons que dans le cas général des applications on ait en moyenne un téléphone par tiers. Puisque que les colonnes que vous affectez aux téléphones font partie de l’en-tête de la table TIERS des tiers, pour N tiers et Q octets par numéro de téléphone, il y a en gros une perte de l’ordre de 2/3 * N * Q octets. Si la base de données est celle des dix millions de clients d’une grande banque, et si toutes les tables sont structurées ainsi, on tend vers la gabegie. Aujourd’hui, un gigaoctets n’est rien, mais quand j’avais votre âge, les disques permettaient de stocker au mieux 30 Mo chacun (exemple IBM 2314) et ils coûtaient fort chers, on faisait donc attention (un de mes collègues avait réussi à n’avoir besoin que d’une vingtaine de Mo pour loger les données de 56 millions de bons anonymes...) Quoi qu’il en soit, comme disait Guillaume d’Ockham « Pluralitas non est ponenda sine necessitate », autrement dit, par souci d’économie il faut se défaire de ce qui n’est pas utile, ce qu’en l’occurrence on réalise en mettant en œuvre une table TEL des téléphones, quitte à prototyper les performances pour comparer.

Envoyé par StringBuilder

[...] si j'ai 3 colonnes téléphone dans ma table [...]

Que faire quand il faudra prendre en compte un 4e téléphone ? Puis un 5e ? Puis un nième ? Ajouter des colonnes à une table est lourd de conséquence, notamment sur la maintenance des développements. Que faire quand il faudra distinguer les types de téléphone (bureau, personnel, fax, ...) sans violer désormais la 3NF ?

Votre exemple des téléphones illustre une approche horizontale (vectorielle) avec une cardinalité (degré, dimension) fixée, gravée dans le marbre. N’oubliez pas que les opérateurs relationnels sont orientés verticalité, avec cardinalité variable, notamment les opérateurs d’agrégation (SUM, COUNT, AVG, ...) Cela vous le savez puisque vous connaissez SQL.

Envoyé par StringBuilder

[...] si j'ai une table de téléphones, j'aurai soit :
- Une seule requête qui ramène X lignes par tiers (avec X = nombre de numéro de téléphone), ce qui va effondrer mes performances réseau entre mon serveur SGBD et mon serveur d'application. [...]

Étant donné que l’unité de lecture/écriture pour un SGBD (au moins pour ceux que je connais) est la page, le bloc physique dont la taille s’exprime en kilo-octets (par exemple 4K), si pour éviter de funestes problèmes d’I/O bound, vous avez pris soin d’utiliser l’identification relative (entité-type TEL identifiée relativement à l’entité-type TIERS, ce qui revient à dire d’un point de vue sémantique que le téléphone est une propriété multivaluée du tiers), alors le SGBD récupérera vos X téléphones en un seul accès au disque. Dans ces conditions, le surcoût de la requête de récupération (par jointure) des numéros de téléphone dans la table TEL est négligeable. Voyez à ce propos la note concernant l'identification relative et le paragraphe concernant l’I/O bound.

Que le SGBD produise non pas X mais une seule ligne par tiers, c’est là un problème de mise en forme à prendre en compte au niveau de la requête, le but de la manœuvre étant de n’avoir qu’un seul échange entre le SGBD et l’application (conséquence d’une approche ensembliste) et non pas un échange pour chaque téléphone (conséquence d’un approche séquentielle). Si vous adaptez l’exemple proposé à drbs, vous aurez une vue, appelons-la TELV (MaVue dans l’exemple), permettant de représenter en une seule ligne les données contenues dans X lignes, et par jointure naturelle de la table TIERS avec la vue TELV, vous produirez le résultat attendu, avec pour conséquence que le nombre d’échanges entre le SGBD et l’application sera effectivement limité à un par tiers, donc sans que les performances du réseau soient pénalisées (au passage, voyez aussi les articles de SQLpro ayant trait par exemple aux CTE, aux procédures stockées etc.)

Envoyé par StringBuilder

Multiplier par 3 la charge du réseau, c'est une affirmation en l'air ?

Contrairement à vous le laissez entendre, dans le cas des trois téléphones, on ne multiplie pas par trois la charge du réseau, car en vertu de ce qui précède celle-ci n’est pas fonction linéaire du nombre de lignes à lire de la table des téléphones.

Envoyé par StringBuilder

C'est de ce genre d'optimisation qu'il est question lorsque je parle de dénormaliser pour des problèmes de performances.

Vous pouvez vous cantonner à l’utilisation du terme « optimisation ». Le fait que l’en-tête de la table TIERS contienne X attributs pour les téléphones est en réalité indépendant de la normalisation car, de la 2NF à la BCNF, celle-ci implique seulement les clés candidates d’une part et d’autre part les dépendances fonctionnelles, puis les dépendances multivaluées (pour la 4NF) et les dépendances de jointure (pour les 5NF et 6NF).

En tout état de cause, comme je l’ai montré (notamment avec l’identification relative), nul besoin de chercher à optimiser en rapatriant les téléphones dans la table des tiers.

Envoyé par StringBuilder

Ca, c'est des cas concrets, qui arrivent tous les jours à chacun de nous.

C'est malheureusement possible, dès lors qu’on ne modélise pas dans les règles de l’art et que l’on ne s’astreint pas à utiliser pleinement et correctement l’algèbre relationnelle. J’ai constaté cela au cours d’audits que j’ai effectués, suite auxquels il a fallu corriger la structure des tables constituant le cœur des bases de données, opération onéreuse mais indispensable pour parvenir à des performances correctes. Je pense, entre autres, à cet exemple caractéristique, ce batch quotidien qui à sa mise en production (sans prototypage de performances préalable) a duré 240 heures... Comme a fini par me l’avouer d’une voix mourante celui qui avait créé le modèle conceptuel, après que je l’ai eu coincé dans ses retranchements (une véritable anguille) : « J’aurais dû faire appel à un technicien »... Il ne m’avait pas fallu longtemps pour me rendre compte qu’il n’avait jamais modélisé de sa vie, mais qu’il avait voulu se faire plaisir, c’était un excellent commercial, mais à chacun son métier.

[fsmrel]

Envoyé par StringBuilder

Je ne parle pas du tout de simplifier les requêtes SQL, on s'en cogne des requêtes SQL.

Qu’en termes peu châtiés ces choses sont dites... Bref, passons, mais c’est l’homme à la moto, Don Chamberlin qui va être bien déçu de savoir que d’aucuns ne portent guère d’intérêt à SQL (du moins c’est ce qu’ils proclament), langage qu’il a inventé il y a trente-cinq ans avec Raymond Boyce (RIP)... D’accord, SQL n’est pas le prince des langages (il s’en faut de beaucoup !), mais il est dommage de vouloir se passer de la puissance de l’algèbre relationnelle et donc de raisonner (quelle alternative ?) séquentiellement. En outre, au-delà des requêtes, on doit savoir sous-traiter un maximum de choses à son SGBD, histoire de se simplifier la vie et assurer une meilleure pérennité des règles de gestion. Prenons l’exemple très simple d’une date de début qui ne doit pas être postérieure à la date de fin associée : la commande CREATE TABLE comportera une clause CHECK à cet effet. Autre exemple, quand on a une table T1 des entreprises et une table T2 des établissements et que l’on veuille s’assurer qu’un établissement n’est pas simultanément une entreprise (contrainte d’exclusion), avec SQL on mettra en oeuvre une assertion (cf. CREATE ASSERTION), ou un trigger (CREATE TRIGGER) si le SGBD ne nous permet pas de coder des assertions. En tout cas, dès que l’on peut les sous-traiter au SGBD, ces contraintes n’ont plus rien à faire dans les programmes d’application. Vous me direz que votre ERP s’occupe de tout, mais ça reste du code applicatif et par ailleurs comment développerez-vous quand les aléas de la vie vous éloigneront de lui ?

=> Sous-traiter le maximum de contrôles au SGBD (lequel est de plus en plus performant au fil des ans) et ne conserver dans les programmes que la partie productive.

Envoyé par StringBuilder

Moi je parle de simplifier les algorythmes des applications consommatrices

Intention louable, mais simplifier un algorithme dans un programme n’entraîne pas a priori la réduction de la consommation de ressources, mais a un coût en termes de maintenance. En revanche, un SGBD comme DB2 utilise sous le capot des algorithmes de plus en plus astucieux et sophistiqués, dont l’objet est bien de réduire la consommation des ressources, sans que l’on ait en contrepartie à modifier une ligne de code dans les programmes.

Envoyé par StringBuilder

Y'a qu'un chercheur de l'EDNAT pour croire qu'un modèle hyper normalisé est super optimisé pour une application de production

Non. Il y a aussi ceux dont je fais partie et qui ont passé leur vie dans la soute et à la dunette, à modéliser, prototyper et garantir les performances de la production informatique dans les entreprises. Comme je l’ai déjà mentionné, j’ai passé le plus clair de mon temps sur le terrain, notamment suite à des appels au secours, pour rattraper en catastrophe les bourdes des autres mettant à mal la production. Cela fait un bon moment que j’ai conclu que, même si elle n’est pas la panacée, la normalisation des bases de données est déterminante et singulièrement bénéfique. Par ailleurs, je rappelle que normaliser et optimiser sont deux choses différentes mais qui sont complémentaires.

Envoyé par StringBuilder

Détrompe-toi. Le développeur ne se contente pas de faire des INNER JOIN dans son code

Que le développeur n’ait pas à coder que des INNER JOIN, personne n’en disconvient ! Cela dit, je vous rappelle ce que j’ai écrit dans un précédent message : « des mois avant que les développements ne commencent on connaissait la performance de la base de données normalisée suite au prototypage » et l’équipe de la production de la banque a du coup obtenu que soit imposé le prototypage des performances à l’ensemble des projets. Par ailleurs, j’ai quand même commencé à développer il y a quarante cinq ans et j’étais aussi DBA quand vous êtes né, j’ai donc le sentiment de pouvoir affirmer que je sais de quoi je parle.

Envoyé par StringBuilder

CODSOC et TYPTIE sont des données contextuelles, dont l'utilisateur n'a que faire. Donc si, SIGTIE est un identifiant unique pour un contexte donné (les clients du magasin, les fournisseur de la centrale d'achat, etc.) C'est donc une clé toute trouvée, dans le plus strict respect de la conceptualisation.

Plaît-il ? Vous aviez écrit :

Envoyé par StringBuilder

dans le cas que je décrit, SIGTIE peut être identique selon les TYPTIE et les CODSOC.

=> Pour deux tiers différents, la colonne SIGTIE peut avoir la même valeur, donc le singleton {SIGTIE} ne peut pas être clé candidate.

Envoyé par StringBuilder

Les informations de SIRET, etc. sont bien évitement stockées soit dans la table des tiers (lorsqu'elles sont succeptibles d'être communes à suffisament de types de tiers -il vaut mieux parfois sacrifier une normalisation à l'extrême au profit de la simplicité lorsque la simplicité s'applique au plus grand nombre-) ou dans des tables dépendantes.

Dans mon message précédent, je vous avais demandé de nous éclairer, car votre explication n’est pas claire (euphémisme...) Je repose donc ma question :

Envoyé par fsmrel

Le numéro Siret des entreprises fait-il bien l’objet d’une colonne qui lui est propre ?

De la même façon, le matricule des vendeurs fait-il bien l’objet d’une colonne qui lui est propre (que ce soit dans la table TIERS ou une autre) ? Plus généralement, conformément aux règles de la modélisation, chaque donnée identifiante « naturelle » fait-elle bien l’objet d’une colonne qui lui est propre ?

Envoyé par StringBuilder

C'est pourtant vers ce genre de choses qu'on s'oriente avec les framework SQL Less.
L'ERP sur lequel je travaille utilise ce système pour construire dynamiquement des écrans : dans un fichier XML on indique qu'on veut parcourir un objet métier (qui correspond généralement à une table) et pour chaque ligne, on va parcourir d'autres objets métiers : on a donc rapidement des centaines (voir des milliers) de requête qui sont exécutées pour un seul écran.

Pardon ? Des centaines (des milliers) de requêtes pour un seul écran ?! Il y a de l’optimisation à prévoir en ce qui concerne la manipulation des données, les requêtes SQL il ne faut pas « s’en cogner », même si celui qui les génère est un ERP...

Envoyé par StringBuilder

[...]l'ajout de 3 colonnes dans une autre table éviterait des jointures inutiles.

J’inverse le propos : la jointure (l’opérateur relationnel par excellence) permet de ne pas avoir à ajouter inutilement des colonnes dans l’en-tête d’une table. Ajouter une colonne est une opération lourde s’il en est quand il faut s’y résoudre, alors qu’ajouter une ligne dans une table est transparent, et comme on l’a vu, sans que la performance soit impactée.

Envoyé par StringBuilder

au plus on aura des valeurs NULL dans ces colonnes supplémentaires

Le défi de la modélisation est — entre autres — de ne jamais avoir d’attribut qui puisse être marqué NULL.

Envoyé par StringBuilder

[...]le SGBD, il ne faut pas oublier que c'est qu'un moyen de stocker les données.

Êtes-vous conscient de l’énormité que vous proférez ? Grâce au langage qu’il propose, le SGBD vous permet de structurer les données (le typage sert par exemple à éviter de comparer des choux et des carottes, les vues vous permettent de présenter les données de différentes façons, etc.), les manipuler en tant qu’ensembles (grâce aux opérateurs de l’algèbre relationnelle), en garantir l’intégrité (intégrité d’entité, intégrité référentielle, contraintes de base de données (instruction CONSTRAINT de Tutorial D ou assertions et triggers SQL)). Et je ne parle même pas des propriétés ACID que le SGBD doit assurer outre d’autres fonctions. Si vous vous limitez au stockage, autant en passer par des fichiers.

Envoyé par StringBuilder

Si la représentation des données n'est pas compatible avec la représentation qu'on en a dans l'outils qui les traites, là il y a un vrai problème, bien plus grave qu'un souci de dénormalisation.

Effectivement, il faut changer d’outil, ou envoyer celui qui en est l’auteur apprendre à modéliser correctement (5NF, voire 6NF pour les données temporelles), apprendre l’algèbre relationnelle, apprendre à faire des vues. La modélisation des bases de données ne se fait pas en plaquant dans des tables ce qu’on voit à l’écran, la vue qu’on a de l’écran est indépendante (orthogonale comme dirait Chris Date) de la structure de la base de données.

[StringBuilder]

Merci pour ces longues réponses très intéressantes.

Elles le seraient plus si je n'étais pas déjà convaincu de ce que vous dites.

Le seul problème, et effectivement, pour cette raison, j'estime le débat clos, c'est qu'on ne parle absolument pas de la même chose, et au lieu de chercher un terrain d'entente pour comprendre ce que l'autre a à dire, on ne fait qu'énnoncer des expériences diverses, illustrées par des cas plus théoriques.

Tout ce que je remarque ici, c'est qu'il est question d'un topic sur "quand" dénormaliser.
Si la question se pose, c'est qu'il doit vraisemblablement y avoir des cas où effectivement, cela peut devenir judicieux, voir nécessaire. Ou alors ce topic est inutile, ou tout du moins, mal nommé.

Depuis le début, les différents intervenants s'égosillent à dire qu'il fait absolument modéliser dans les règles de l'art, et qu'il ne faut surtout pas dénormaliser.

Il se trouve que je travaille sur un outils (peut-être visionnaire, peut-être complètement à côté de la plaque) qui est parti d'un raisonnement simple :
- Un SGBD, c'est un outils informatique, qui va être utilisé par un autre outils informatique
- En informatique, il existe des notions d'héritage, de surcharge
- Pourquoi ne pas appliquer ces principes au modèle des données

Ceci a pour effet, avec cet outils (mais aussi d'autres, avec lesquels j'ai déjà eu l'occasion de travailler) de mutualiser un certain nombre d'entités, afin de stocker, avec la même structure, les mêmes algorithmes, des données de nature différentes.

L'intérêt ? Il est pourtant simple : alors que vous me repprochez de devoir ajouter une colonne lors d'une montée en version, vous êtes absolument pas étonnés de devoir rajouter des tables à tout bout de champs, dès que le besoin s'élargit... alors que la solution que je donne comme exemple, permet de conserver le même modèle des données.

Il y a tout de même une certaine contradiction vous ne trouvez pas ?

Le fait de stocker en effet dans une unique table "tie" des informations telles que des clients, fournisseurs, dépôts, magasins, etc. demande malheureusement de faire quelques concessions en ce qui concerne la dénormalisation : colonnes vides notamment. Cependant, vu qu'il s'agit d'un outil d'éditeur (ERP ou non, j'insiste sur le fait qu'il s'agit d'un outils d'éditeur) il existera toujours des éléments dans le modèle de données qui ne seront pas exploités, qu'il soit hyper normalisé ou non.
En revanche, le même outil peut servir à Générale de Santé à gérer la pose de prothèses sur des patients ainsi que leurs prescriptions médicales, ou à La Dauphinoise l'ensemmancement des champs de ses clients, ou à CDiscount la gestion de ses ventes en ligne.

Le tout avec exactement la même base de données, alors que ni les procédures métier, ni les informations nécessaires au fonctionnement de ces sociétés n'ont quoi que ce soit en commun.

Ce modèle n'est pas parfait, certains choix de dénormalisation peuvent être reprochés, mais je n'admettrai jamais qu'on puisse remettre en question l'aspect praticité, fiabilité et performance des choix qui ont été faits.

J'insiste donc sur ce point.

Il ne faut pas confondre la modélisation "pointue", où chaque élément du dictionnaire de données est maîtrisé, chaque lien entre information est scellé dans le marbre, avec la modélisation "générique", qui permet au plus grand nombre de profiter du même outils : ça n'a rien à voir.
Dans le premier cas, oui, normaliser le plus possible est tout à fait normal : c'est la garantie de pouvoir maîtriser le modèle des données et son évolution.
Dans le second cas, en l'abscence d'un dictionnaire des données, en l'absence d'une large majorité des règles de gestion, il est évident que la modélisation ne sera pas du tout pensée de la même façon.

Un exemple simple :
- Lorsque CDiscount vend des produits hifi, certaines propriétés sont parfaitement dédiées au fait que le produit soit hifi. Ce nombre de propriétés est illimité, et diffère d'un produit à l'autre (y'a pas d'auteur sur un lecteur de DVD de salon, et y'a pas de classe d'énergie sur un CD Audio).
=> Le modèle des données doit déjà pouvoir s'adapter à ça. C'est pas simple quand on est en mode "normalisation pure". Personnellement, à froid, je n'ai pas de solution toute prête pour ça (démultiplication des tables de produit en fonction des types de produits ? démultiplication des tables de propriétés ? quelle solution n'est pas usine à gaz lors de l'allimentation/interrogation ?)
- Mais maintenant, quand Générale de Santé arrive, et commence à parler de prothèses des hanches, de pathologies clients... ça commence à faire beaucoup de différences !
=> Avec un choix de modélisation "classique", à part se retrouver avec des dizaines, centaines de table pour gérer bêtement des propriétés de produits, je ne vois pas trop comment faire.

Le principe de cet outils dont je parle, c'est justement de conserver le même modèle des données pour tout le monde. Alors toutes ces propriétés, c'est géré en 3 tables, avec beaucoup moins de null que vous n'imaginez : il ne s'agit pas de créer des tables "fourre-tout" avec 500 colonnes "au cas où un jour ma prothèse mamaire n'est un tuner FM", mais d'une table "fourre-tout" avec un faible nombre de colonnes, mais une ligne par propriété, et une description des contraintes dans une troisième table.

C'est une façon complètement différente de penser.
D'un point de vue performances, j'accepte sans condition que ce type de modélisation peut avoir quelques lacunes par rapport à de la modélisation normalisée. Cependant, bien moins inportante que vous ne voulez bien le dire, sinon je me demande comment nos clients feraient pour fonctionner vu leur volumétrie et la tronche de leurs serveurs (chez Animalis ? Ah, ben c'est un vieux Pentium II qui fait tourner la base... c'est pas hyper rapide, mais ça tourne bien... j'ai jamais vu de souris blanche dans un aquarium dans leurs rayons).

L'optimisation, elle ne se trouve pas seulement au niveau "matériel" du serveur de base de données, ni même d'application.

L'optimisation, elle se trouve aussi au niveau de la maintenance, et de la mise en oeuvre des projets, des montées en version de l'outil (qu'est-ce que le prix d'un serveur plus gros en contrepartie de milliers d'heures de mise en oeuvre ?)

Voilà. J'accepte sans aucun problème qu'une bonne modélisation est une modélisation sans (ou presque) dénormalisation. Mais acceptez quand même qu'il existe des cas où OUI, la dénormalisation est nécessaire. C'est tout ce que je vous demande.

[iberserk]

D'un point de vue performances, j'accepte sans condition que ce type de modélisation peut avoir quelques lacunes par rapport à de la modélisation normalisée

Mais cela n'a rien à voir avec de la dénormalisation!

Le modèle pour les articles est le modèle dit par méta données (une table des type de caractéristique d'article, une table article, une table d'association article/caractéristique -> valeur).

SQLPRO a d'ailleurs fait un excellent article à ce sujet:
http://sqlpro.developpez.com/cours/m...n/metadonnees/

Et j'utilise d'ailleurs cette modélisation dans le cadre de mon travail (site ebusiness clé en main connecté à différents type d'ERP).

Par ailleurs CDISCOUNT est un mauvais exemple, leur "modélisation" une catastrophe et leurs problèmes à ce sujet important...
(les caractéristiques sont stockées en XML dans une colonne de l'article)