Modèle générique vs modèle spécifique

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 :

CREATE TABLE T_ANIMAL_ANM
(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 :

CREATE TABLE T_MOLUSQUE_MSQ
(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 » :

CREATE TABLE T_CARATERISTIQUE_CRT
(CRT_ID              INT PRIMARY KEY,
 CRT_LIBELLE         VARCHAR(256));
CREATE TABLE T_VALEUR_VLR
(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 (111, 'Carapace');
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 :

INSERT INTO T_MOLUSQUE_MSQ (ANM_ID, MSQ_CARAPACE, MSQ_SYMETRIE, MSQ_COULEUR, MSQ_DIAMETRE_MM)
                    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 :

INSERT INTO T_VALEUR_VLR
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 :

Comparaison des plans de requêtes pour 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

SELECT *
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

SELECT *
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 ?

Comparaison des plans de requêtes pour l'extraction

É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….

  • Complexité d’insertion des données
  • Mauvaise performances à l’insertion des données
  • Complexité d’écriture des recherches
  • Mauvaise performances pour la recherches de données
  • Impossibilité de typer finement (notion de domaine)
  • Impossibilité de placer des contraintes multicritères
  • Difficultés pour l’indexation

  • 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 :

  • distribution peu aléatoire (parce que concentrées sur les 26 lettres et 10 chiffres parmi les 256 caractères disponibles) induisant des statistiques d’optimisation mal réparties (histogrammes erratiques)
  • extra-overhead lié à la gestion de la collation (sensibilité ou non à la casse, aux accents…)
  • façon de stocker physiquement les VARCHAR induisant des octets suplementaires et de la fragmentaion en cas d’UPDATE

  • 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


    Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
    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

    MVP Microsoft SQL
Server

    4 réflexions au sujet de « Modèle générique vs modèle spécifique »

    1. Avatar de gnicolasgnicolas

      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

    2. Avatar de iberserkiberserk

      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!

    3. Avatar de CiccilloCiccillo

      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).

    Laisser un commentaire