La technique des métadonnées

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 :

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 enseignes des clients, dont le dernier chiffre d'affaires, aura été supérieur à 50000, il suffit de taper la requête :

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 à 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éristique 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 :

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

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 :

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

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 types 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 » ?

Notre modèle complet, devient alors :

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érieures à 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 colonnes que de paramètres de recherche, ici ce n'est qu'une seule colonne qui est concernée, 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 contact remonte à plus d'un mois…
C'est d'ailleurs ainsi que fonctionnent bon nombre 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 !

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

• que toutes les tables auxquelles on désire ajouter de l'information possèdent une clef unique de même type (l'entier étant le mieux) ;
• que les rubriques à ajouter ne dépassent pas la longueur prévue dans le méta modèle (32 caractères sont 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.

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 :

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 :

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 :

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''AFFAIRES')
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éristique « FORME SOCIÉTÉ » dans la table TR_VALEUR_VLR :

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 :

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 :

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'AFFAIRES :

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

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

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

Pour l'interrogation :

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 :

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''AFFAIRES'
  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…