Comment gérer au mieux le stockage des données historiques ?

Question

J'essaie de déterminer comment je dois stocker les données transactionnelles historiques.

Dois-je les stocker dans une table unique où l'enregistrement est simplement réinséré avec un nouvel horodatage à chaque fois ?

Dois-je répartir les données historiques dans un tableau "historique" distinct et ne conserver que les données actuelles dans le tableau "actif" ?

Si oui, quelle est la meilleure façon de le faire ? Avec un déclencheur qui copie automatiquement les données dans la table d'historique ? Ou avec une logique dans mon application ?

Mise à jour selon le commentaire de Welbog :

Il y aura de grandes quantités de données historiques (des centaines de milliers de lignes - éventuellement des millions).

Les recherches et les opérations de reporting seront principalement effectuées sur les données historiques.

Les performances sont un sujet de préoccupation. Les recherches ne devraient pas avoir à tourner toute la nuit pour produire des résultats.

Answer 1

Si les besoins sont uniquement liés à la production de rapports, envisagez de créer un entrepôt de données distinct. Cela vous permet d'utiliser des structures de données telles que des dimensions à évolution lente, qui sont bien meilleures pour le reporting historique mais ne fonctionnent pas bien dans un système transactionnel. La combinaison qui en résulte permet également de retirer les rapports historiques de votre base de données de production, ce qui constitue un gain en termes de performances et de maintenance.

Si vous avez besoin que cet historique soit disponible dans l'application, vous devez implémenter une sorte de versioning ou de fonction de suppression logique, ou faire en sorte que tout soit entièrement contre-passé et retraité (c'est-à-dire que les transactions ne sont jamais supprimées, mais seulement annulées et retraitées). Réfléchissez bien pour savoir si vous realmente Nous n'en avons pas besoin car cela ajouterait beaucoup de complexité. Réaliser une application transactionnelle capable de reconstruire correctement l'état historique est beaucoup plus difficile qu'il n'y paraît. Les logiciels financiers (par exemple les systèmes de souscription d'assurance) échouent beaucoup plus souvent que vous ne le pensez.

Si vous avez besoin de l'historique uniquement pour l'enregistrement d'audit, créez des tables fantômes et des déclencheurs d'enregistrement d'audit. C'est beaucoup plus simple et plus robuste que d'essayer d'implémenter correctement et complètement la journalisation d'audit dans l'application. Les déclencheurs détecteront également les modifications apportées à la base de données par des sources extérieures à l'application.

Answer 2

Cette question s'inscrit dans le cadre de la logique d'entreprise. Il faut d'abord connaître les besoins de l'entreprise, puis partir de là. Un entrepôt de données est une bonne solution pour ce genre de situation. L'ETL vous donnera beaucoup d'options pour traiter les flux de données. Votre concept de base d'"historique" par rapport à "actif" est tout à fait correct. Vos données historiques seront plus efficaces et plus flexibles si elles sont conservées dans un entrepôt de données avec toutes leurs tables de dimensions et de faits.