La technique des méta données

La technique des méta données est pertinente pour laisser toute liberté aux utilisateurs de rajouter autant de rubriques de données qu'ils le souhaitent sans jamais de crainte quand à leur volume ni leur facilité d'interrogation. Et tout cela sans jamais modifier l'architecture de la base de données...

Comment cela est-il possible ?

Article lu   fois.

L'auteur

Profil ProSite personnelSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

Préambule

Imaginons une application. La plus belle que vous ayez écrite... Dès qu'elle se trouve en exploitation, les utilisateurs râlent parce que certaines données n'ont pas été modélisées.
Par exemple dans la prospection commerciale vous avez oublié les données "chiffre d'affaire" ou encore "forme juridique de la société"...

Dans une approche classique il vous faut modifier la table des prospects en y ajoutant les colonnes demandées. Si vous voulez une base performante, il vous faudra peut être rajouter une table de références proposant les différentes formes juridiques de société, afin que les utilisateurs ne saisissent pas n'importe quelle information que vous aurez du mal ensuite à retrouver, pour, par exemple, extraire les prospects dont la société est une SA... chacun ayant l'habitude de codifier ou de saisir à sa manière. Puis il vous faudra modifier nombre de requêtes afin de faire apparaître ces informations dans les différents écrans. Enfin, si la nouvelle colonne est très sollicitée en recherche, il vous faudra l'indexer afin d'obtenir des performances acceptables...

Dans une approche par méta modèle, vous n'aurez besoin... d'aucune modification de la structure de la base et d'aucun ajout dans aucune requête d'aucune sorte pour retrouver rapidement les informations pertinentes. Vous n'aurez pas non plus besoin d'ajouter un index puisqu'il sera déjà en place !
Comment ce miracle est-il possible ?

Nous allons d'abord analyser la méthode classique et ses inconvénients puis vous présenter la technique de méta modélisation...

1. Approche classique

Nous prendrons comme exemple une base de données constituée d'une seule table, la table des prospects, ainsi modélisée :

Image non disponible

Toutes les requêtes, d'insertion, de modification et d'affichage, ainsi que les écrans y afférent ont été réalisés, et patatras, on vous demande d'ajouter une colonne pour spécifier le dernier chiffre d'affaire réalisé par le prospect.

Bien entendu, vous modifiez la table à l'aide d'un ordre ALTER, comme suit :

 
Sélectionnez

ALTER TABLE T_PROSPECT ADD PRC_DERNIER_CA DECIMAL (16,2)

Cela, c'est la partie la plus simple. Il vous faut ensuite modifier les écrans de saisie, de visualisation et bien entendu les requêtes sous jacentes, sans compter les états.
Bref une bonne journée de boulot vous attend pour un simple ajout numérique.

Quelques semaines plus tard, on vous demande de rajouter la forme de la société en obligeant la saisie dans une liste paramétrable.
Votre modèle devient :

Image non disponible

Et là il vous faut ajouter une table, des valeurs prédéfinies, une interface de saisie modification de la liste prédéfinie des valeurs, altérer la table des prospects pour y ajouter la contrainte d'intégrité, redéfinir à nouveau tout vos écrans et toutes les requêtes sous jacentes portant sur le concept de prospect.

S'en est trop... Vous décidez alors de lire la suite de cet article, afin de devenir un fainéant performant, auquel on suppliera de modifier encore et encore le modèle de données et vous répondrez que cela est possible dans un laps de temps plus réduit qu'auparavant...
Vous irez alors jouer au tennis quelques heures sur votre temps de travail et en fin de journée, il vous suffira de quelques minutes pour assurer cet ajout. Vous déciderez alors d'aller voir le demandeur en l'informant que la modification est faite en ayant l'air désespérément occupé par l'effroyable surcroit de travail qu'une telle demande vous a occasionné ! Si vous préférez ne pas jouer au tennis, alors n'hésitez pas à demander une augmentation de salaire pour la performance de rapidité de développement que vous avez eût pour ce travail effectué en un temps record, avant de dévoiler à votre chef la superbe technique mise en oeuvre !

2. Le méta modèle

Le méta modèle propose une solution générique basée sur un modèle souple de données. Nous verrons quand même qu'il possède des limites heureusement peu contraignantes. En revanche il permet de laisser toute latitude aux utilisateurs pour ajouter toutes les rubriques passées et à venir que vous avez oublié, et ce quelle que soit la table dans laquelle ces rubriques doivent figurer...

2.1. Le principe

Il s'agit d'ajouter des éléments dans notre base permettant de décrire la structure des données à ajouter ainsi que les valeurs de ces données.

Une première ébauche nous conduirait au modèle suivant :

MCD
Image non disponible
MPD
Image non disponible

Notez bien le lien de cardinalité (1, 1) en entité fille dépendante de l'entité T_PROSPECT_PRC qui conduit à ajouter à une clef composite dans le modèle physique. Ainsi pour toute ligne de la table T_PROSPECT_PRC nous pouvons avoir n caractéristiques de libellé différentes avec n valeurs associées.

Ce pourrait par exemple être la forme de la société et le dernier CA !

Dorénavant, quelles que soit les pseudo colonnes à rajouter, nous n'avons plus à faire bouger le modèle de données.

Pour interroger les données dans un tel modèle, rien de plus simple. Ainsi récupérer les enseigne des clients dont le dernier chiffre d'affaire aura été supérieur à 50000, il suffit de tapper la requête :

 
Sélectionnez

SELECT PRC_ENSEIGNE
FROM   T_PROSPECT_PRC PRC
       JOIN T_CARACTERISTIQUE_PROSPECT_CRP CRP
            ON PRC.PRC_ID = CRP.PRC_ID
WHERE  CRP.CRP_LIBELLE = 'DERNIER CA'
  AND  CAST(CRP.CRP_VALEUR AS INTEGER) > 50000

Nous constatons quand même qu'il vaudrait mieux savoir a quel type de données nous avons à faire, ne serait-ce que pour pouvoir fortement typer les données afin que les comparaisons et les filtres opèrent du mieux possible. Le rajout d'une simple colonne contenant le type de données suffit dans ce premier modèle.

Mais nous avons une table peu performante, puisque celle-ci contient une clef sous forme de chaîne de caractères. De plus le contenu de la colonne CRP_LIBELLE va s'avérer fortement redondant puisqu'il y aura (certes au maximum) autant de fois le nom de la caractéristiques que de ligne dans la table des prospects...
D'où l'idée d'externaliser le nom de la caractéristique dans une table annexe.

D'où le modèle affiné, suivant :

MCD
Image non disponible
MPD
Image non disponible

Pour les mêmes raisons, nous avons tout intérêt à externaliser le type de données et le "statifier".

MCD
Image non disponible
MPD
Image non disponible

Pour ce qui est du stockage et de la modification des données, on veillera à bien "caster" les données dans le type SQL attendu, même si les données sont stockées sous forme de chaînes de caractères.
Par exemple la date du dernier contact avec le prospect sera insérée de la façon suivante :

 
Sélectionnez

INSERT INTO TJ_CARACTERISTIQUE_PROSPECT_CRP (PRC_ID, CAR_ID, CRP_VALEUR)
VALUES (33, 2, CAST(CAST(laDate AS DATE) AS CHAR(32)))

Pour le prospect de clef 33 et si la caractéristique 2 est bien la date du dernier contact.

Si nous désirons que les valeurs possibles soit prérenseignées, afin par exemple de contraindre la saisie dans une liste de choix, il suffit de rajouter une table contenant la liste de toutes les valeurs possibles de toutes les caractéristiques pour laquelle nous désirons une telle liste :

MCD
Image non disponible
MPD
Image non disponible

Bien entendu vous n'êtes pas obligé de pré remplir la table TR_VALEURS_VLR pour chacune des caractéristiques. Et rien ne vous empêche d'ajouter dans la table des type de données un type particulier hors SQL qui serait le type "LIST" !

Dès lors vous avez tous les éléments en main pour utiliser votre méta modèle et ajouter au gré de la demande toute nouvelle caractéristique à la table des prospects...

Mais il est possible de faire mieux... En effet : pourquoi se cantonner à la table des prospects et ne pas en faire profiter toutes les tables de votre base ?

Il suffit de rajouter à notre méta modèle, une nouvelle table, la table des tables qui fera la jonction entre la table cible et les caractéristiques spécifiques à telle ou telle table... Mais le seul ajout de cette table des tables ne suffit pas, car nous devons supprimer la jointure avec notre unique table des prospects et perdons du même coup la ligne identifiant une caractéristique donnée... Il nous faut donc, outre la table des tables, rajouter un identifiant de ligne.
Or, pourquoi ne pas en profiter pour changer notre association "associé" en entité "données" ?

2.2. Le modèle

Notre modèle complet, devient alors :

MCD
Image non disponible
MPD
Image non disponible

En 4 tables, et 14 colonnes, dont 9 sont des entiers, nous avons obtenu un méta modèle capable de s'adapter à n'importe quelle base de données, et permet de rajouter sans aucune intervention du développeur autant de colonnes que nous voulons dans n'importe quelle table de la base...

Bien entendu, j'en entends déjà dire que tout stocker dans une unique colonne, c'est de la folie compte tenu de la quantité de données et les requêtes vont être très pénalisantes...
Remarquez qu'il ne vous suffit que d'un seul index posé sur la colonne CRE_VALEUR de la table T_DONNEES_DON pour vous assurer des performances bien supérieure à ce que vous auriez attendu en requêtant votre base habituelle sur de multiples colonnes. En effet alors que dans un modèle traditionnel vous aurez sollicité autant de colonne que de paramètres de recherche, ici ce n'est qu'une seule colonne qui est concerné, même si vous recherchez les prospects dont la forme de société est SA, ayant fait un CA de plus de 50000 et dont le dernier conctact remonte à plus d'un mois...
C'est d'ailleurs ainsi que fonctionnent bon nombres de bases de données orientées OLAP / ROLAP permettant de gérer des hypercubes et de s'amuser à faire du datamining. Or, ces SGBD là traitent des volumes énormes de données...

Un autre avantage non négligeable du méta modèle, c'est qu'il minimise le volume des données et donc améliore les performances des recherches dans les tables méta caractérisées. En effet au lieu de se retrouver avec de nombreuses colonnes pas toujours remplies, les tables méta caractérisées se trouvent pourvues de peu de colonnes (les rubriques essentielles) les plus souvent renseignées. Le volume de chaque table est donc bien moindre que dans le modèle ordinaire. De plus il n'y a aucun "trou" dans les tables du méta modèle, puisque c'est l'absence de la ligne dans la table T_DONNEES_DON qui correspond à la valeur NULL dans le modèle ordinaire !

2.3. Les contraintes d'exploitation

Il n'y a guère que deux contraintes parfaitement justifiées concernant la mise en place d'un tel modèle :

  • que toute les tables auquel on désire ajouter de l'information possède une clef unique de même type (l'entier étant le mieux)
  • que les rubriques à ajouter ne dépasse pas la longueur prévue dans le méta modèle (32 caractères est généralement un bon compromis)

Mais il ne faut pas oublier de bien indexer un tel modèle !

En revanche s'il faut gérer l'intégrité référentielle, cela est possible à condition que la base accepte la pose de trigger. En effet il suffit de placer dans les tables méta caractérisées, un trigger qui supprime les références de ligne en cascade dans la table T_DONNEE_DON.

2.4. Primitives SQL de manipulation

Voici quelques unes des requêtes à manipuler pour utiliser un tel modèle...

Ajout de types de données dans la table TR_TYPE_DONNEES_TDN :

 
Sélectionnez

INSERT INTO TR_TYPE_DONNEES_TDN (TDN_ID, TDN_SQL_TYPE, TDN_LONGUEUR) 
VALUES (1, 'VARCHAR', 32)
INSERT INTO TR_TYPE_DONNEES_TDN (TDN_ID, TDN_SQL_TYPE, TDN_LONGUEUR) 
VALUES (2, 'CHAR', 8)
INSERT INTO TR_TYPE_DONNEES_TDN (TDN_ID, TDN_SQL_TYPE, TDN_LONGUEUR) 
VALUES (3, 'DATE', NULL)
INSERT INTO TR_TYPE_DONNEES_TDN (TDN_ID, TDN_SQL_TYPE, TDN_LONGUEUR) 
VALUES (4, 'INTEGER', NULL)
INSERT INTO TR_TYPE_DONNEES_TDN (TDN_ID, TDN_SQL_TYPE, TDN_LONGUEUR) 
VALUES (5, 'LIST', NULL)
INSERT INTO TR_TYPE_DONNEES_TDN (TDN_ID, TDN_SQL_TYPE, TDN_LONGUEUR) 
VALUES (1, 'FLOAT', NULL)

Ajout de références de tables dans la table TR_TABLE_TBL :

 
Sélectionnez

INSERT INTO TR_TABLE_TBL (TBL_ID, TBL_SQL_NOM)
VALUES (1, 'T_PROSPECT_PRP')
INSERT INTO TR_TABLE_TBL (TBL_ID, TBL_SQL_NOM)
VALUES (2, 'T_PRODUIT_PRD')

Ajout de caractéristiques dans la table TR_CARACTERISTIQUE_CAR :

 
Sélectionnez

INSERT INTO TR_CARACTERISTIQUE_CAR (CAR_ID, TDN_ID, CAR_LIBELLE)
VALUES (1, 5, 'FORME SOCIÉTÉ')
INSERT INTO TR_CARACTERISTIQUE_CAR (CAR_ID, TDN_ID, CAR_LIBELLE)
VALUES (2, 4, 'CHIFFRE D''AFFAIRE')
INSERT INTO TR_CARACTERISTIQUE_CAR (CAR_ID, TDN_ID, CAR_LIBELLE)
VALUES (3, 3, 'DATE DERNIER CONTACT')
INSERT INTO TR_CARACTERISTIQUE_CAR (CAR_ID, TDN_ID, CAR_LIBELLE)
VALUES (4, 1, 'COULEUR')

Ajout d'une liste d'aide à la saisie pour la caractéristiques "FORME SOCIÉTÉ" dans la table TR_VALEUR_VLR :

 
Sélectionnez

INSERT INTO TR_VALEUR_VLR (VLR_LIBELLE, CAR_ID)
VALUES ('SOCIETE ANONYME', 1)
INSERT INTO TR_VALEUR_VLR (VLR_LIBELLE, CAR_ID)
VALUES ('SOCIETE ANONYME A RESPONSABILITÉ LIMITÉE', 1)
INSERT INTO TR_VALEUR_VLR (VLR_LIBELLE, CAR_ID)
VALUES ('SOCIETE ANONYME CÔTÉ EN BOURSE', 1)
INSERT INTO TR_VALEUR_VLR (VLR_LIBELLE, CAR_ID)
VALUES ('GROUPEMENT D''INTÉRÊTE ÉCONOMIQUE', 1)
INSERT INTO TR_VALEUR_VLR (VLR_LIBELLE, CAR_ID)
VALUES ('COLLECTIVITÉ LOCALE', 1)
INSERT INTO TR_VALEUR_VLR (VLR_LIBELLE, CAR_ID)
VALUES ('ASSOCIATION LOI 1901', 1)

Ajout de données caractérisant la ligne 168 de la table T_PROSPECT, dans la table T_DONNEES_DON :

 
Sélectionnez

INSERT INTO TR_DONNEES_DON (TBL_ID, CAR_ID, CRE_LIGNE, CRE_VALEUR)
VALUES (1, 1, 168, 'SOCIETE ANONYME')
INSERT INTO TR_DONNEES_DON (TBL_ID, CAR_ID, CRE_LIGNE, CRE_VALEUR)
VALUES (1, 2, 168, '12896875')
INSERT INTO TR_DONNEES_DON (TBL_ID, CAR_ID, CRE_LIGNE, CRE_VALEUR)
VALUES (1, 3, 168, CAST(CURRENT_DATE AS CHAR(32))

Modification de la donnée 'DERNIER CONTACT' caractérisant la ligne 168 de la table T_PROSPECT, dans la table T_DONNEES_DON :

 
Sélectionnez

UPDATE TR_DONNEES_DON
SET    CRE_VALEUR = CAST(CAST('2003-01-15' AS DATE) AS CHAR(32))
WHERE  TBL_ID = (SELECT TBL_ID 
                 FROM   TR_TABLE_TBL 
                 WHERE  TBL_LIBELLE = 'TR_PROSPECT_PRP')
  AND  CAR_ID = (SELECT CAR_ID 
                 FROM TR_CARACTERISTIQUE_CAR 
                 WHERE CAR_LIBELLE = 'DATE DERNIER CONTACT')
  AND  CRE_LIGNE = 168

Suppression de la caractéristique CHIFFRE D'AFFAIRE :

 
Sélectionnez

DELETE FROM TR_DONNEES_DON
WHERE  CAR_ID = (SELECT CAR_ID 
                 FROM TR_CARACTERISTIQUE_CAR 
                 WHERE CAR_LIBELLE = 'CHIFFRE D''AFFAIRE')

DELETE FROM TR_VALEUR_VLR
WHERE  CAR_ID = (SELECT CAR_ID 
                 FROM TR_CARACTERISTIQUE_CAR 
                 WHERE CAR_LIBELLE = 'CHIFFRE D''AFFAIRE')

DELETE FROM TR_CARACTERISTIQUE_CAR
WHERE  CAR_LIBELLE = 'CHIFFRE D''AFFAIRE')

Pour l'interrogation :

 
Sélectionnez

SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
       LEFT OUTER JOIN TR_TABLES_TBL TBL
            ON DON.TBL_ID = TBL.TBL_ID
       LEFT OUTER JOIN TR_CARACTERISTIQUE_CAR CAR
            ON DON.CAR_ID = CAR.CAR_ID
WHERE  TBL.TBL_SQL_NOM = 'TR_PROSPECT_PRP'
  AND  CAR.CAR_LIBELLE = 'Carac_1'
  AND  CRE.CRE_VALEUR = valeur 1
  AND  CAR.CAR_LIBELLE = 'Carac_2'
  AND  CRE.CRE_VALEUR = valeur 2
  AND  CAR.CAR_LIBELLE = 'Carac_3'
  AND  CRE.CRE_VALEUR = valeur 3
  AND  CAR.CAR_LIBELLE = 'Carac_4'
  AND  CRE.CRE_VALEUR = valeur 4
... 

Bien entendu les jointures sur TR_TABLES_TBL et TR_CARACTERISTIQUE_CAR ne sont pas nécessaires si l'on connait les identifiant repectivement de la table cible et des caractéristiques visées :

 
Sélectionnez

SELECT *
FROM   TR_PROSPECT_PRP PRP
       LEFT OUTER JOIN T_DONNEE_DON DON
            ON PRP.PRP_ID = DON.CRE_LIGNE
WHERE  DON.TBL_ID = 44            -- lien pour la table TR_PROSPECT_PRP
  AND  DON.CAR_ID = 123           -- lien pour une caractéristique donnée, par exemple 'CHIFFRE D''AFFAIRE'
  AND  CRE.CRE_VALEUR = valeur 1  -- valeur demandée pour la caractéristique sus mentionnée
  AND  DON.CAR_ID = 475
  AND  CRE.CRE_VALEUR = valeur 2
  AND  DON.CAR_ID = 74
  AND  CRE.CRE_VALEUR = valeur 3
  AND  DON.CAR_ID = 7
  AND  CRE.CRE_VALEUR = valeur 4
...

Mais le fin du fin, consiste à prévoir une procédure stockée qui réalise la requête "automatiquement", car il convient de se pencher sur la problématique des valeurs NULL... qui dans ce modèle, peuvent ne pas figurer...

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Livres
SQL - développement
SQL - le cours de référence sur le langage SQL
Avant d'aborder le SQL
Définitions
SGBDR fichier ou client/serveur ?
La base de données exemple (gestion d'un hôtel)
Modélisation MERISE
Mots réservés du SQL
Le SQL de A à Z
Les fondements
Le simple (?) SELECT
Les jointures, ou comment interroger plusieurs tables
Groupages, ensembles et sous-ensembles
Les sous-requêtes
Insérer, modifier, supprimer
Création des bases
Gérer les privilèges ("droits")
Toutes les fonctions de SQL
Les techniques des SGBDR
Les erreur les plus fréquentes en SQL
Les petits papiers de SQLPro
Conférence Borland 2003
L'héritage des données
Données et normes
Modélisation par méta données
Optimisez votre SGBDR et vos requêtes SQL
Le temps, sa mesure, ses calculs
QBE, le langage de ZLOOF
Des images dans ma base
La jointure manquante
Clefs auto incrémentées
L'indexation textuelle
L'art des "Soundex"
Une seule colonne, plusieurs données
La division relationnelle, mythe ou réalité ?
Gestion d'arborescence en SQL
L'avenir de SQL
Méthodes et standards
Les doublons
SQL Server
Eviter les curseurs
Un aperçu de TRANSACT SQL V 2000
SQL Server 2000 et les collations
Sécurisation des accès aux bases de données SQL Server
Des UDF pour SQL Server
SQL Server et le fichier de log...
Paradox
De vieux articles publiés entre 1995 et 1999 dans la défunte revue Point DBF

  

Copyright © 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.