Dans le cadre d’un héritage de données ayant de multiples enfants aux caractéristiques très diverses, est-il préférable de concevoir un modèle de données générique ou un modèle de données spécifique ? De nombreux jeunes développeurs pensent naïvement qu’un modèle générique est plus facile… Mais c’est souvent une erreur qu’il faudra payer au prix fort une fois l’application fonctionnelle. Démonstration…
Je suis partit initialement de ce post :
Avis sur l’architecture de ma base de données (choix de l’option)
La demande étant la suivante :
J’ai 30 familles de critères. Chaque famille comporte un nombre variable de critères pour un total d’environ 600 critères.
Chaque critère appartient à une seule famille de sorte que les 600 sont à répartir dans les 30 familles.
Ma question:
Рdevrais-je cr̩er une table par famille et ins̩rer dans chaque table les crit̬res qui lui appartiennent; (solution 1)
ou alors:
– créer une table contenant les 30 familles et une autre contenant les 600 critères. Ensuite, créer les relations qui lient chaque famille à ses critères et/ou inversement. (solution 2)
et ma réponse :
Pour qu’une base réponde bien en terme de performances, on doit tout faire pour minimiser les IO.
– Dans votre solution 1, les performances seront bonne si la plupart des requêtes portent sur plusieurs critères d’une même famille
РDans votre solution 2, les performances seront bonne si la plupart des requ̻tes portent sur un seul crit̬re.
Cependant un jeune internaute peu avisé, affirmait avec force que :
pour moi la solution 2 est la + simple à mettre en place.
et le nombre d’enregistrements ne justifie pas la solution 1..
tout dépend de votre SGBD (pas précisé..), mais dans tous les cas, il suffit de mettre des ID numérique pour que les requêtes soient optimisées. Créez vos tables avec des id uniques (sous Oracle on peux utiliser les séquences) et vous aurez des traitements optimisées au mieux… mais bon, je vous rassure, sous Oracle, même avec des clefs en Varchar, pour 600 enregistrements, y a aucun pb..
si je vois bien, vous aurez donc 3 tables avec les clef suivante :
– FAMILLE (Pk : ID_FAMILLE)
– FAMILLE_CRITERE (Pk : ID_FAMILLE, ID_CRITERE)
– CRITERE (Pk : ID_CRITERE)
Relations :
¤ Famille –> FAMILLE_CRITERES <— CRITERE
famille_critere ne contenant que le couple ID_FAMILLE, ID_CRITERE (on parle de table intermédiaire)
Ce à quoi je répondais :
La solution 2 est correcte si vous ne requêtez JAMAIS plus d’un critère à la fois. Elle est déjà moins performante à l’insertion/la mise à jour des données…
La solution 2 est imbécile, complexe et peu performantes si vous avez plusieurs critères a rechercher…
En sus, elle ne permet pas facilement de typer les informations, donc, vous aurez beaucoup de problématique de contrôle de qualité… et des performances catastrophiques
DÉMONSTRATION
Partons d’une table d’animaux :
(ANM_ID INT PRIMARY KEY,
ANM_DATA CHAR(8000) NOT NULL);
GO
INSERT INTO T_ANIMAL_ANM VALUES (1, 'Mytilus Edulis')
█ █ █ 1ere solution : une seule table pour toute une même famille :
(ANM_ID INT PRIMARY KEY REFERENCES T_ANIMAL_ANM (ANM_ID),
MSQ_CARAPACE VARCHAR(32),
MSQ_SYMETRIE VARCHAR(32),
MSQ_COULEUR VARCHAR(32),
MSQ_DIAMETRE_MM FLOAT);
â–ˆ â–ˆ â–ˆ 2e solution (pour toutes familles), des tables « génériques » :
(CRT_ID INT PRIMARY KEY,
CRT_LIBELLE VARCHAR(256));
(ANM_ID INT NOT NULL REFERENCES T_ANIMAL_ANM (ANM_ID),
CRT_ID INT NOT NULL REFERENCES T_CARATERISTIQUE_CRT(CRT_ID),
VLR_VALEUR VARCHAR(256),
PRIMARY KEY (ANM_ID, CRT_ID));
Ajoutons les caractéristiques identiques à celle de la solution 1
INSERT INTO T_CARATERISTIQUE_CRT VALUES (222, 'Symétrie');
INSERT INTO T_CARATERISTIQUE_CRT VALUES (333, 'Couleur');
INSERT INTO T_CARATERISTIQUE_CRT VALUES (444, 'Diamètre mm');
â–ˆ â–ˆ â–ˆ Voici maintenant les insertions :
Pour la solution 1 :
VALUES (1, 'Creatine', 'Mono', 'Bleu', '82.5')
Notez que nous n’avons fait qu’une seule requête d’insertion avec correspondance caractéristique / valeur et typage des données
Pour la solution 2 :
SELECT 1, CRT_ID, 'Creatine' FROM T_CARATERISTIQUE_CRT
WHERE CRT_LIBELLE = 'Carapace';
INSERT INTO T_VALEUR_VLR
SELECT 1, CRT_ID, 'Mono' FROM T_CARATERISTIQUE_CRT
WHERE CRT_LIBELLE = 'Symétrie';
INSERT INTO T_VALEUR_VLR
SELECT 1, CRT_ID, 'Bleu' FROM T_CARATERISTIQUE_CRT
WHERE CRT_LIBELLE = 'Couleur';
INSERT INTO T_VALEUR_VLR
SELECT 1, CRT_ID, '82,5' FROM T_CARATERISTIQUE_CRT
WHERE CRT_LIBELLE = 'Diamètre mm';
Pour stocker les mêmes informations, nous avons fait 4 requêtes au lieu d’une seule….
â–ˆ Comparons maintenant le coût des 2 solutions au niveau de l’insertion :
Y’a pas à tortiller 4 requêtes coutant deux fois plus cher qu’une seule et unique requête cela est donc 8 fois plus couteux…
█ █ █ Voyons maintenant la facilité de requêtage :
interrogation multicritères ET avec solution 1
FROM T_ANIMAL_ANM AS A
JOIN T_MOLUSQUE_MSQ AS M
ON A.ANM_ID = M.ANM_ID
WHERE MSQ_CARAPACE = 'Creatine'
AND MSQ_SYMETRIE IN ('Mono', 'Double')
AND MSQ_COULEUR LIKE 'Bleu%' --> peut être Bleue, Bleu-marine...
AND MSQ_DIAMETRE_MM BETWEEN 75 AND 92;
la requête fait 8 lignes et 1 jointure. Elle en fera une de plus pour chaque critère supplémentaire avec juste quelques chose à rajouter à la clause WHERE…
interrogation multicritères ET avec solution 2
FROM T_ANIMAL_ANM AS A
JOIN T_VALEUR_VLR AS V1
ON A.ANM_ID = V1.ANM_ID
JOIN T_CARATERISTIQUE_CRT AS C1
ON V1.CRT_ID = C1.CRT_ID
JOIN T_VALEUR_VLR AS V2
ON A.ANM_ID = V2.ANM_ID
JOIN T_CARATERISTIQUE_CRT AS C2
ON V2.CRT_ID = C2.CRT_ID
JOIN T_VALEUR_VLR AS V3
ON A.ANM_ID = V3.ANM_ID
JOIN T_CARATERISTIQUE_CRT AS C3
ON V3.CRT_ID = C3.CRT_ID
JOIN T_VALEUR_VLR AS V4
ON A.ANM_ID = V4.ANM_ID
JOIN T_CARATERISTIQUE_CRT AS C4
ON V4.CRT_ID = C4.CRT_ID
WHERE C1.CRT_LIBELLE = 'Carapace'
AND V1.VLR_VALEUR = 'Creatine'
AND C2.CRT_LIBELLE = 'Symétrie'
AND V2.VLR_VALEUR IN ('Mono', 'Double')
AND C3.CRT_LIBELLE = 'Couleur'
AND V3.VLR_VALEUR LIKE 'Bleu%'
AND C4.CRT_LIBELLE = 'Diamètre mm'
AND V4.VLR_VALEUR BETWEEN '75' AND '92'
La requête fait 26 lignes avec 8 jointures… Il faudra rajouter 6 lignes à chaque critère supplémentaire, dont 4 dans la clause FROM et 2 dans le WHERE, et encore, on ne sait pas dans la clause SELECT à quelle caractéristique se réfère chacune des valeurs… Il faudrait aussi customiser la clause SELECT…
Y’a pas à tortiller la solution 1 est bien plus simple en matière d’écriture…
█ Quelles sont les performances des requêtes de recherches des deux solutions ?
Évidemment la première solution est 4,5 fois moins couteuse…
Mais il y a pire… J’ai du mettre des chiffres en chaine de caractères pour le diamètre (AND V4.VLR_VALEUR BETWEEN ’75’ AND ’92’). Si je tente de les typer en entier ou réel (AND V4.VLR_VALEUR BETWEEN 75 AND 92), cela provoque une erreur d’exécution (SQL Server) :
Msg*245, Niveau*16, État*1, Ligne*75
Échec de la conversion de la valeur varchar ‘Creatine’ en type de données int.
Logique car pour effectuer une comparaison avec une valeur numérique, il faut convertir la colonne en numérique, mais comme il y a autres chose que de purs nombres (chaines de caractères) cela se passe mal.
Bref certaines requêtes seront impossible à faire !
Plus catastrophique encore… mettre des index ne servira quasiment à rien ! En effet un index sur une chaine de caractères n’ordonne pas les valeurs de la même façon que s’il s’agissait de nombre…
En sus rien n’interdira à quelqu’un de saisir 8,2 au lieu de 8.2 (une virgule au lieu d’une point) comme diamètre, dans ce modèle inepte, alors qu’avec le bon modèle les types sont respectés…. Si tel était le cas, la valeur ne pourra pas être retrouvée !!! Il existe bien évidemment une solution pour contourner, un tout petit peu, ce modèle pourri, pour le typage. Il consiste à rajouter autant de tables de valeurs que de type… Mais il n’est pas possible de rajouter des contraintes spécifique à chaque type (par exemple vérifier qu’une pourcentage est bien entre 0 et 100 – notion de domaine SQL), ni, bien entendu, de rajouter des contraintes multicritères (une valeur dépendant d’une autre) comme c’est le cas par exemple de la chronologie des temporels de type « début » et « fin ».
Et dans tous les cas, les requêtes seront encore plus complexes à élaborer et les performances encore plus lamentables…
En guise de conclusion
Nous vous avons démontré les performances lamentable et la complexité horrifiante d’un tel modèle….
Mais comment se fait-il que des internautes osent affirmer de pareilles stupidités ?
Il est amusant de voir que certains développeur incultes affirment n’importe quoi, par ignorance, ou par bêtise, ne prenant même pas la peine de tester ce qu’ils disent… Ensuite ces mêmes développeurs, sans aucune expérience, iront vous raconter que les SGBDR c’est pas performant… Ils vous vendrons alors du NoSQL, ce qui, dans beaucoup de cas, sera pire encore !
Comme disent les américains : « garbage in, garbage out » qui peut se traduire si tu fais de la merde en entrée, tu auras de la merde en sortie ! Autrement dit, si le modèle de données est inepte, la qualité des données sera pourrie et les performances lamentables…
Décortiquons d’ailleurs les affirmations de l’internate que nous avons cité :
« tout dépend de votre SGBD (pas précisé..), mais dans tous les cas, il suffit de mettre des ID numérique pour que les requêtes soient optimisées. Créez vos tables avec des id uniques (sous Oracle on peux utiliser les séquences) et vous aurez des traitements optimisées au mieux… mais bon, je vous rassure, sous Oracle, même avec des clefs en Varchar, pour 600 enregistrements, y a aucun pb.. »
Première affirmation stupide : « tout dépend de votre SGBD (pas précisé..) »
Comme nous sommes dans un forum sur les SGBDR, sachez cher monsieur, que tout les SGBDR fonctionnent de la même façon. Certains allant plus vite que d’autre (une Ferrari va généralement plus vite qu’une Peugeot), certains avec plus de qualité que d’autres (une Rolls-Royce est généralement de meilleure qualité qu’une Dacia).
Seconde affirmation stupide : « il suffit de mettre des ID numérique pour que les requêtes soient optimisées »
Si c’était le cas alors, pas besoin d’index ni de contraintes… On se demande donc pourquoi les éditeurs de SGBDR font de tels effort en R&D pour trouver de nouvelles formes d’index toujours plus performant… Et la plupart des informaticiens ignorent que les contraintes permettent de mieux optimiser les plans de requête… Hélas ceci est très rarement enseigné par les professeurs des universités et encore moins ceux des écoles d’ingénieurs… (je le sais pertinemment puisque j’en étais un…).
Troisième affirmation stupide : « mais bon, je vous rassure, sous Oracle, même avec des clefs en Varchar »
Et bien, non, plus une clef est longue en nombre d’octets, plus ses performances décroissent. Pour avoir la même longueur qu’un entier auto incrémenté il faudrait se limiter à 4 caractères… En sus certaines phénomènes apparaissent avec des clefs non numériques et en particulier pour des clefs littérales :
Quatrième affirmation stupide : « sous Oracle, même avec des clefs en Varchar, pour 600 enregistrements, y a aucun pb.. »
Nous avons déjà dit précédemment que les clef littérales (VARCHAR) étaient une plaie… Mais ce qui ne va pas dans cette phrase est le terme « enregistrement« … Cet internaute sait-il au moins que les SGBDR ne travaillent pas avec des « enregistrement » propre aux bases de données de type « fichier » comme c’était las cas dans les années 50 à 70 ? Les SGBDR structurent les lignes des tables dans des pages qui sont modifiées en mémoire et non pas directement sur le disque (notion physique d’enregistrement). Les pages étant physiquement écrites de temps à autres quand il y en a suffisamment à écrire par un processus asynchrone travaillant en tâche de fond.
Bref voici q’un internaute ne connaissant ni la modélisation des données ni les technologies des SGDB, vient donner des conseils stupides hélas parfois repris dans certaines applications….
CQFD
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
Bonjour,
Est-ce que dans ce genre de situations l’héritage de table (disponible dans PostgreSQL) ne rend pas encore plus simple la mise en place d’un modèle spécifique ?
Bien Cordialement
Bonjour,
Merci pour ce billet.
Cependant je trouve que certains aspect ne sont pas couvert par celui-ci. En conséquence de quoi, il ne peux être une réponse absolue à cette problématique de modélisation / conception.
Je m’explique, la solution préconisée ici présuppose un modèle figé dans le temps, sans ajout de critère, ce qui peut poser un problème d’usage de l’outil qui s’appuiera sur un tel modèle. Vous me direz qu’il est tout à fait possible d’ajouter une colonne à la table cible par le biais de ce même outils, mais nous créons alors un problème de qualification de la données en déléguant à l’utilisateur final une opération dont il ne maîtrise pas, à priori, les tenants et aboutissants.
En sommes, la solution n’est pas aussi simple que vous semblez le décrire en restant essentiellement sur la théorie des modèles, là où ce que vous appelez, avec peu de courtoisie, développeur incultes affirmant n’importe quoi, par ignorance, ou par bêtise, sont confronté à la vie concrète, et que les entreprises essentiellement TPE / PME n’ont pas les moyens d’avoir un informaticien à demeure.
Pour conclure, je pense que le constat est bon que la réponse apporté est bien sur valable pour ce cas précis et dans la théorie des modèles, mais qu’il n’apporte pas une réponse complète à la problématique qu’il prétend résoudre.
Dans l’attente de vous lire,
Cordialement
Belle démonstration Fred, avec le style qu’on te connait
Je ne peux que plussoyer, je suis d’ailleurs intervenu chez un client pourtant très connu (une banque très célèbre) dont l’outil fraîchement développé par une SSII a fonctionné… 10 jours avant que les performances (plusieurs dizaine de secondes, voir plus d’une minute) dramatiques nécessite mon intervention…
Et pourtant la base ne faisait en tout et pour tout que… 60 mo!
C’est article résonne comme un WARNING contre l’improvisation. La différence démontrée entre les 2 solutions est un cris d’alarme, une puce à l’oreille dans l’oreille d’entrepreneurs qui nécessitent d’applications à développer. En simple analyse le choix entre les deux solutions peut sembler équivalent. L’article démontre bien le contraire. Une des solutions constitue une grave erreur de conception à éviter. Combien de pièges sont-ils parsemés sur le cheminement de la conception d’une base de données? J’imagine que la remédiation de la contre-performance d’une architecture de base de données provoque un coût récurrent qui peut être soit prévenu (consultation spécialisée) soit stoppé (analyse du cas).