SQL et les données historique

Dans cette article, nous parcourons les méthodes de modélisation des données historique. Les exemples sont fait en SQL Server 2005/2008 mais rien ne vous empêche appliquer ces structures pour les autres SGBD.

Il existe 2 cas principales dont la modélisation des données historiques est nécessaire :

  1. Modification d'état d'un objet au fil du temps
  2. Enregistrement des événements associés d'une entité

Stockage d'une date de modification

Cette méthode est relativement simple : on ne rajout qu'une attribut de type « date » et on l'inclut dans le clé primaire (PK - primary key). Le sens du champ « Changed » est le moment de modification d'état d'un objet.

Il est très facile modéliser l'intervalle ouverte : elle commence à partir de la date maximale (ou minimale). Mais cela provoque aussi les désavantages : pour trouver la période fermé à partir de la date donnée il nous faut interroger les autres enregistrements dans la même table en faisant la jointure réflexive (self join) dans nos requêtes. Par exemple, voici la requête qui récupère le document dont l'état est correspond à date donné :

SELECT D1.* 
  FROM Documents D1
  WHERE Changed = (SELECT MAX(Changed) 
                    FROM Documents D2
                    WHERE D2.Changed <= '2009-02-01' -- la date donné
                          AND D2.DocumentId = D1.DocumentId)
        AND D1.DocumentId = 101 -- document donné

Avantages :

  • La structure est simple
  • Les intervalles ouvertes
  • Il n'y a pas de la nature redondante
  • L'insertion rapide des enregistrements

Inconvénients :

  • Les requêtes contenant les jointures réflexives sont nécessaires
  • Pour modéliser l'absence d'une période (c'est le cas rare mais...) il faudra rajouter un champ supplémentaire

Recommandation : l'insertion des données intensive (i.e. données d'audit ou des appareils capteurs), les requêtes réflexives sont rares relativement

Stockage d'une intervalle

Afin d'alléger les requêtes « dures » on rajoute la colonne contenant la date de la fin de l'intervalle.

Et voila, notre requête est devenu simple.

SELECT * 
  FROM ProductPrices
  WHERE @CurrentDate BETWEEN DateFrom AND DateTo

Par contre, rien nous empêche d'insérer les lignes dont les intervalles ont l'intersection. Pour éviter ce conflit il nous faut développer le trigger vérifié que les périodes ne sont pas intersecté. D'autre part, la modélisation des périodes ouverts est plus compliqué maintenant : il est possible d'utiliser les valeurs spécifiques (i.e. 01/01/1900), la valeur vide pour la date de la fin ou les attributs de type booléen pour indiquer que cette période est ouverte au début ou à la fin.

Avantages :

  • Les requêtes sont simples est légères

Inconvénients :

  • Les dépenses supplémentaires pour supporter l'intégrité des données
  • L'insertion est moins rapide (à cause de trigger)
  • La modélisation des intervalles ouvertes est plus compliqué

Recommandation : les requêtes intensives de recherche d'une intervalle, les insertions sont rares relativement, les triggers sont supportés par SGBD

Stockage d'une numéro de la période

C'est la combinaison des avantages et des inconvénients des méthodes précédentes. Elle est très efficace en cas les mêmes périodes sont utilisées par les différents entités (i.e. les périodes de la comptabilité).

La requête est moins simple mais est toujours légère :

SELECT *
  FROM Sales S INNER JOIN Periods P ON S.PeriodNum = P.PeriodNum
  WHERE @CurrentDate BETWEEN P.DateFrom AND P.DateTo

En plus, la modélisation des périodes ouverts est moins compliqué puisqu'il nous intéresse plutôt le numéro maximal ou minimal de la période que ces dates.

Avantages :

  • Les requêtes sont légères
  • Réutilisation des périodes
  • La logique de stockage des intervalles est bien séparé de celle des autres entités

Inconvénients :

  • Les dépenses supplémentaires pour supporter l'intégrité des données
  • L'insertion est moins rapide (à cause de trigger)

Recommandation : la comptabilité, la traitement analytique (OLAP)

La version plus détaillé de cette article est publié en "PC World (Russian Eedition)" N°5-2007