Comment conserver l'Historique des Données
Certains collègues et je suis entré dans un débat sur la meilleure façon de stocker les données historiques. Actuellement, pour certains systèmes, j'ai utiliser un tableau pour stocker les données historiques, et je garde une table originale pour le courant, active record. Alors, disons que j'ai de la table FOO. En vertu de mon système, tous les dossiers actifs vont dans FOO, et tous les documents historiques vont dans FOO_Hist. De nombreux domaines différents, dans FOO peut être mise à jour par l'utilisateur, donc je veux garder un compte exact de tout mis à jour. FOO_Hist détient exactement les mêmes champs que FOO, à l'exception d'une auto-incrémentation HIST_ID. Chaque fois que FOO est mis à jour, j'ai effectuer une instruction insert dans FOO_Hist similaires à: insert into FOO_HIST select * from FOO where id = @id.
Mon collègue dit que c'est un mauvais design parce que je ne devrait pas avoir une copie exacte d'un tableau, pour des raisons historiques et faut juste insérer un autre enregistrement dans la table active avec un drapeau indiquant que c'est pour des raisons historiques.
Est-il un standard pour traiter les données historiques de stockage? Il me semble que je ne veux pas encombrer mes dossiers actifs avec tous mes documents historiques dans le même tableau, considérant qu'il est peut-être plus d'un million d'enregistrements (je suis en train de penser à long terme).
Comment vous ou votre entreprise gérer cela?
Je suis à l'aide de MS SQL Server 2008, mais j'aimerais garder la réponse générique et arbitraire de n'importe quel SGBD.
Vous devez vous connecter pour publier un commentaire.
Soutenir l'historique des données directement au sein d'un système d'exploitation sera de faire votre demande beaucoup plus complexe qu'il ne le seraient autrement. En général, je ne recommande pas de le faire, sauf si vous avez un dur obligation de manipuler l'historique des versions d'un enregistrement dans le système.
Si vous regardez de près, la plupart des exigences pour les données historiques se répartissent en deux catégories:
L'enregistrement d'Audit: C'est mieux fait avec de la vérification des tables. Il est assez facile d'écrire un outil qui génère des scripts pour créer de l'audit des tableaux de bord et déclenche par la lecture des métadonnées du système de dictionnaire de données. Ce type d'outil peut être utilisé pour rénover la journalisation d'audit sur la plupart des systèmes. Vous pouvez également utiliser ce sous-système pour changé de capture de données si vous souhaitez mettre en place un entrepôt de données (voir ci-dessous).
Rapports historiques: de Rapports historiques de l'état, de la 'dans' des positions ou des rapports analytiques au fil du temps. Il peut être possible de remplir à la simple historiques exigences en matière de rapports par quering l'enregistrement d'audit tableaux de la nature décrite ci-dessus. Si vous avez des besoins plus complexes, il peut être plus économique à mettre en oeuvre un data mart pour le reporting plutôt que d'essayer et d'intégrer l'histoire directement dans le système d'exploitation.
Les dimensions à évolution lente sont, de loin, le plus simple mécanisme pour le suivi et l'interrogation historique de l'état et une grande partie de l'histoire de suivi peuvent être automatisées. Générique gestionnaires ne sont pas si difficile à écrire. Généralement, des rapports historiques ne pas avoir à utiliser jusqu'à-to-the-minute des données, de sorte qu'un groupées mécanisme d'actualisation est normalement bien. Cela permet de maintenir votre cœur et système de reporting architecture relativement simple.
Si vos besoins en matière de tomber dans l'une de ces deux catégories, vous êtes probablement mieux de ne pas stocker l'historique des données dans votre système d'exploitation. La séparation de l'historique de fonctionnalités dans un autre sous-système sera probablement moins d'effort d'ensemble et de produire de transaction et de vérification/rapport des bases de données qui fonctionnent mieux pour leur usage prévu.
Je ne pense pas qu'il y est une norme particulière façon de le faire, mais je pensais que j'allais jeter dans une méthode possible. Je travaille dans Oracle et de notre propre infrastructure d'application web qui utilise XML pour le stockage des données de l'application.
Nous utilisons ce qu'on appelle un Maître - Détail de modèle qu'à son expression la plus simple se compose de:
De la Table de maître par exemple appelé
Widgetssouvent juste contenant un ID. Souvent contenir des données qui ne changent pas au fil du temps /n'est pas historique.Détail /Table d'Historique par exemple appelé
Widget_Detailscontenant au moins:Donc, essentiellement, une entité commence par avoir 1 ligne dans le maître et 1 ligne dans le détail. Le détail ayant une valeur NULL à la fin de la date et de l'STATUS_CONTROL de "C".
Lorsqu'une mise à jour se produit, le courant de ligne est mise à jour pour avoir END_DATETIME de l'heure et de la status_control est définie sur NULL (ou 'A' si l'on préfère). Une nouvelle ligne est créée dans la table de détail, toujours le même maître, avec status_control "C", l'id de la personne qui fait la mise à jour et les nouvelles données stockées dans le XMLDATA colonne.
C'est la base de notre modèle historique. Créer /mettre à Jour la logique est géré dans un package Oracle PL/SQL, donc il vous suffit de passer la fonction que le courant ID, votre ID utilisateur et les nouvelles données XML et en interne, il fait toutes les mise à jour, l'insertion de lignes pour la représenter dans le modèle historique. Le début et la fin des temps de l'indiquer lors de cette ligne dans la table est active.
De stockage n'est pas cher, nous n'avons généralement pas de SUPPRIMER des données, et préfèrent conserver une piste de vérification. Cela nous permet de voir ce que nos données ressemblait à un moment donné. Par indexation status_control = 'C' ou de la Vue, de l'encombrer n'est pas exactement un problème. Evidemment, vos requêtes ont besoin de prendre en compte, vous devez toujours utiliser le courant (NULL end_datetime et status_control = 'C') la version de l'enregistrement.
Je pense que l'approche est correcte. Tableau historique doit être une copie de la table sans index, assurez-vous de mettre à jour l'horodatage dans le tableau.
Si vous essayez de l'autre approche assez tôt, vous ferez face à des problèmes:
Dans SQL Server 2016 et au-dessus de, il est une nouvelle fonctionnalité appelée Temporelle des Tableaux qui vise à résoudre ce défi avec un minimum d'effort de la part du développeur. La notion temporelle de table est similaire à la Capture de Données modifiées (CDC), avec la différence que le temps de la table a une abstraction de la plupart des choses que vous avez eu à le faire manuellement si vous étiez à l'aide de la CDC.
cette question est un peu vieux, mais les gens toujours à travailler sur cette question. 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
De capture de données modifiées: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017
Il est pris en charge dans SQL Server 2008 R2, il aurait été pris en charge dans SQL Server 2008.
Vous pouvez simplement partitionner les tables de no?
"Table partitionnée et les Stratégies d'Index à l'Aide de SQL Server 2008
Lorsqu'une table de base de données augmente la taille de centaines de gigaoctets ou plus, il peut devenir plus difficile à charger de nouvelles données, de supprimer les anciennes données, et de maintenir les index. Juste la taille de la table est la cause de ces opérations prennent beaucoup plus de temps. Même les données qui doivent être chargés ou supprimé peut être très importante, rendant INSÉRER et SUPPRIMER des opérations sur la table impraticable. Microsoft SQL Server 2008 logiciel de base de données fournit la table de partitionnement pour rendre ces opérations plus faciles à gérer."
La vraie question est avez-vous besoin d'utiliser des données historiques et active les données pour le rapport? Si donc les garder dans un tableau, de partition et de créer une vue pour les dossiers actifs à utiliser dans les requêtes actives. Si vous avez seulement besoin de regarder de temps en temps (à la recherche leagal questions ou quelque chose du genre) puis les mettre dans un tableau distinct.
JOINdeux tables dans un couple de rapports historiques ou est-il plus difficile de modifier chaque table insert/update/delete pour être au courant de l'historique des préoccupations? En fait, un journal d'audit permettrait d'inclure même les données actuelles dans l'histoire de la table, de sorte que le tableau actuel ne devrait même pas être nécessaire dans un rapport.Une autre option est d'archiver les données d'exploitation sur un [jour|horaire|whatever] base. La plupart des moteurs de base de données l'extraction des données dans une archive.
Fondamentalement, l'idée est de créer un planifiées de Windows ou une tâche CRON qui
De nombreux SQL base de données des moteurs de venir avec un outil qui peut être utilisé à cette fin. Par exemple, lors de l'utilisation de MySQL sur Linux, la commande suivante peut être utilisée dans une tâche CRON pour planifier l'extraction:
Je Sais ce vieux post mais je voulais Juste ajouter quelques points.
La norme pour de tels problèmes est ce qui fonctionne le mieux pour la situation. la compréhension de la nécessité d'un tel stockage, et de l'utilisation potentielle de l'historique/audit/suivi des modifications de données est très importation.
De vérification (objectif de sécurité) : Utiliser une table commune pour tous vos vérifiable tables. définir la structure pour stocker le nom de colonne , valeur avant et après la valeur des champs.
Archive/Historique: pour les cas comme le suivi précédente adresse , numéro de téléphone etc. création d'un tableau distinct FOO_HIST est mieux si vous avez votre transaction active schéma de la table ne change pas de façon significative à l'avenir(si votre table d'historique doit avoir la même structure).
si vous prévoyez de la table de la normalisation , de type de données de changement de ajout/suppression de colonnes, de stocker l'historique de vos données au format xml . définir un tableau avec les colonnes suivantes (ID,Date, Version du Schéma, XMLData). cela permettra de gérer facilement les modifications de schéma . mais vous avez à traiter avec xml et qui pourraient présenter un niveau de complication pour la récupération des données .
Vous pouvez utiliser MSSQL Server fonctionnalité d'Audit. À partir de la version SQL Server 2012, vous trouverez cette fonction dans toutes les éditions:
http://technet.microsoft.com/en-us/library/cc280386.aspx
Vous pouvez créer un matérialisé/vues indexées sur la table. En fonction de vos besoins vous pouvez complète ou mise à jour partielle de la vue. Merci de voir pour créer mview et un journal. Comment créer des vues matérialisées dans SQL Server?
Voulais juste ajouter une option que j'ai commencé à utiliser parce que je utiliser Azure SQL et les multiples tableau de la chose était trop lourde pour moi. J'ai ajouté un insert/update/delete déclencher sur ma table, et ensuite convertis avant/après changement de json à l'aide de la "JSON AUTO" caractéristique.
Qui retourne une représentation JSON de fo le dossier avant/après le changement. J'ai ensuite stocker ces valeurs dans une table d'historique avec un horodatage quand le changement s'est produit (j'ai également stocker l'ID de l'enregistrement courant du problème). En utilisant le processus de sérialisation, je peux contrôler la façon dont les données sont remplies dans le cas de modifications du schéma.
Je l'ai appris à partir de ce lien ici