La technique des métadonnées

SQLPro

La technique des métadonné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 quant à 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 ? ♪

- Titre : La technique des métadonnées

- Auteur : SQLPro

- Parution : 26 août 2003

- Licence : 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.

Sommaire

- Préambule

- I. Approche classique

- II. Le méta modèle

- II-A. Le principe

- II-B. Le modèle

- II-C. Les contraintes d'exploitation

- II-D. Primitives SQL de manipulation

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

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

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'AFFAIRES réalisé par le prospect.

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

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 :

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 tous 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 pré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 œuvre !

II - 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ées, et ce quelle que soit la table dans laquelle ces rubriques doivent figurer…

II-A - 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

MPD

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 :

MCD

MPD

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

MCD

MPD

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 :

MCD

MPD

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

II-B - Le modèle

Notre modèle complet, devient alors :

MCD

MPD

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 !

II-C - 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 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.

II-D - 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 :

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…