Comment stocker les données historiques

Question

Certains collègues et moi avons entamé un débat sur la meilleure façon de stocker les données historiques. Actuellement, pour certains systèmes, j'utilise une table séparée pour stocker les données historiques, et je conserve une table originale pour l'enregistrement actif actuel. Ainsi, disons que j'ai la table FOO. Dans mon système, tous les enregistrements actifs sont placés dans FOO, et tous les enregistrements historiques dans FOO_Hist. De nombreux champs de la table FOO peuvent être mis à jour par l'utilisateur, et je veux donc garder une trace précise de toutes les mises à jour. FOO_Hist contient exactement les mêmes champs que FOO, à l'exception d'un HIST_ID qui s'incrémente automatiquement. Chaque fois que FOO est mis à jour, j'exécute une instruction d'insertion dans FOO_Hist, comme suit : insert into FOO_HIST select * from FOO where id = @id .

Mon collègue dit que c'est une mauvaise conception parce que je ne devrais pas avoir une copie exacte d'une table pour des raisons historiques et que je devrais simplement insérer un autre enregistrement dans la table active avec un drapeau indiquant que c'est à des fins historiques.

Existe-t-il une norme pour le traitement du stockage des données historiques ? Il me semble que je ne veux pas encombrer mes enregistrements actifs avec tous mes enregistrements historiques dans la même table, étant donné qu'il peut s'agir de plus d'un million d'enregistrements (je pense à long terme).

Comment vous ou votre entreprise gérez-vous cette situation ?

J'utilise MS SQL Server 2008, mais j'aimerais que la réponse reste générique et qu'elle soit arbitraire par rapport à tout SGBD.

Answer 1

La prise en charge des données historiques directement dans un système opérationnel rendra votre application beaucoup plus complexe qu'elle ne le serait autrement. D'une manière générale, je ne vous recommande pas de le faire, sauf si vous devez absolument manipuler les versions historiques d'un enregistrement dans le système.

Si vous regardez de près, la plupart des exigences en matière de données historiques entrent dans l'une des deux catégories suivantes :

• Enregistrement de l'audit : Il est préférable de le faire avec des tableaux d'audit. Il est assez facile d'écrire un outil qui génère des scripts pour créer des tables d'audit et des triggers en lisant les métadonnées du dictionnaire de données du système. Ce type d'outil peut être utilisé pour mettre à niveau la journalisation d'audit sur la plupart des systèmes. Vous pouvez également utiliser ce sous-système pour la capture de données modifiées si vous souhaitez mettre en œuvre un entrepôt de données (voir ci-dessous).

• Rapports historiques : Établissement de rapports sur l'état historique, les positions "en l'état" ou les rapports analytiques dans le temps. Il peut être possible de répondre à des exigences simples en matière de rapports historiques en interrogeant des tables d'enregistrement d'audit du type décrit ci-dessus. Si vous avez des exigences plus complexes, il peut être plus économique de mettre en œuvre un mart de données pour le reporting que d'essayer d'intégrer l'historique directement dans le système opérationnel.

  Les dimensions à évolution lente sont de loin le mécanisme le plus simple pour suivre et interroger l'état historique et une grande partie du suivi de l'historique peut être automatisée. Les gestionnaires génériques ne sont pas si difficiles à écrire. En général, les rapports historiques n'ont pas besoin d'utiliser des données de dernière minute, de sorte qu'un mécanisme de rafraîchissement par lots convient normalement. L'architecture de votre noyau et de votre système de reporting reste ainsi relativement simple.

Si vos besoins entrent dans l'une de ces deux catégories, il est probablement préférable de ne pas stocker les données historiques dans votre système opérationnel. La séparation de la fonctionnalité historique dans un autre sous-système demandera probablement moins d'efforts dans l'ensemble et produira des bases de données transactionnelles et d'audit/de rapports qui fonctionneront beaucoup mieux pour l'objectif visé.

Answer 2

Je ne pense pas qu'il y ait une manière standard particulière de le faire, mais j'ai pensé que je pourrais proposer une méthode possible. Je travaille pour Oracle et notre cadre d'application web interne qui utilise le XML pour stocker les données de l'application.

Nous utilisons ce que l'on appelle un modèle Master - Detail qui, dans sa forme la plus simple, consiste à :

Table des maîtres par exemple appelé Widgets contenant souvent juste un identifiant. Contiendront souvent des données qui ne changeront pas dans le temps / ne sont pas historiques.

Tableau de détail / historique par exemple appelé Widget_Details contenant au moins :

• ID - clé primaire. Détail/ID historique
• MASTER_ID - par exemple, dans ce cas, appelé "WIDGET_ID", il s'agit du FK de l'enregistrement principal.
• START_DATETIME - Horodatage indiquant le début de cette ligne de la base de données.
• END_DATETIME - Horodatage indiquant la fin de cette ligne de la base de données.
• STATUS_CONTROL - colonne à caractère unique indiquant le statut de la ligne. 'C' indique l'état actuel, NULL ou 'A' indique l'état historique/archivé. Nous l'utilisons uniquement parce que nous ne pouvons pas indexer sur END_DATETIME étant NULL.
• CREATED_BY_WUA_ID - stocke l'ID du compte à l'origine de la création de la ligne.
• XMLDATA - stocke les données réelles

Donc, essentiellement, une entité commence par avoir 1 ligne dans le master et 1 ligne dans le détail. Le détail a une date de fin NULL et STATUS_CONTROL de 'C'. Lorsqu'une mise à jour se produit, la ligne actuelle est mise à jour pour avoir END_DATETIME à l'heure actuelle et status_control est mis à NULL (ou 'A' si vous préférez). Une nouvelle ligne est créée dans la table des détails, toujours liée au même master, avec status_control 'C', l'identifiant de la personne effectuant la mise à jour et les nouvelles données stockées dans la colonne XMLDATA.

C'est la base de notre modèle historique. La logique de création et de mise à jour est gérée dans un package Oracle PL/SQL. Il vous suffit donc de transmettre à la fonction l'identifiant actuel, votre identifiant d'utilisateur et les nouvelles données XML pour qu'elle effectue en interne la mise à jour et l'insertion des lignes afin de les représenter dans le modèle historique. Les heures de début et de fin indiquent le moment où cette ligne de la table est active.

Le stockage est bon marché, nous ne supprimons généralement pas les données et préférons conserver une piste d'audit. Cela nous permet de voir à quoi ressemblent nos données à tout moment. En indexant status_control = 'C' ou en utilisant une vue, l'encombrement n'est pas vraiment un problème. Bien entendu, vos requêtes doivent tenir compte du fait que vous devez toujours utiliser la version actuelle (NULL end_datetime et status_control = 'C') d'un enregistrement.

Answer 3

Je pense que votre approche est correcte. La table historique doit être une copie de la table principale sans index, assurez-vous d'avoir l'horodatage des mises à jour dans la table également.

Si vous essayez l'autre approche, vous serez rapidement confronté à des problèmes :

• frais généraux d'entretien
• plus de drapeaux dans les sélections
• ralentissement des requêtes
• la croissance des tableaux, des index

Answer 4

Cette question est assez ancienne mais les gens travaillent toujours sur ce problème. donc si vous utilisez oracle vous pourriez être intéressé par oracle flashback : http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm

Answer 5

Vous pourriez simplement partitionner les tables, non ?

" Stratégies de tables et d'index partitionnés avec SQL Server 2008 Lorsqu'une table de base de données atteint une taille de plusieurs centaines de gigaoctets ou plus, il peut devenir plus difficile de charger de nouvelles données, de supprimer d'anciennes données et de maintenir des index. La taille même de la table fait que ces opérations prennent beaucoup plus de temps. Même les données qui doivent être chargées ou supprimées peuvent être très volumineuses, ce qui rend les opérations INSERT et DELETE sur la table peu pratiques. Le logiciel de base de données Microsoft SQL Server 2008 fournit le partitionnement de table pour rendre ces opérations plus faciles à gérer."