Structure de base de données pour stocker des données historiques
Préface:
Je pensais l'autre jour à propos d'une nouvelle structure de base de données pour une nouvelle application et réalisé que nous avions besoin d'un moyen de stocker l'historique des données de manière efficace. Je voulais quelqu'un d'autre de prendre un coup d'oeil et voir si il y a des problèmes avec cette structure. Je me rends compte que cette méthode de stockage de données peut très bien avoir été inventé avant (je suis presque sûr qu'il y en a) mais je n'ai aucune idée si elle a un nom et quelques recherches sur google que j'ai essayé de ne pas produire quoi que ce soit.
Problème:
Disons que vous avez un tableau pour les commandes, et les commandes sont liés à un client de la table pour le client qui a passé la commande. Dans un normal la structure de base de données que vous pourriez vous attendre à quelque chose comme ceci:
orders
------
orderID
customerID
customers
---------
customerID
address
address2
city
state
zip
Assez simple, orderID a une clé étrangère de code client qui est la clé primaire de la table client. Mais si nous devions nous rendre et exécuter un rapport sur le tableau de commande, nous allons rejoindre la table clients à la table des commandes, qui ramènera l'enregistrement en cours pour que l'ID du client. Si lors de la commande, l'adresse du client est différent et qu'il a été modifié par la suite. Maintenant, notre ordre ne reflète plus l'histoire de l'adresse du client, au moment de la commande. Fondamentalement, par la modification de l'enregistrement du client, nous avons juste changé toute l'histoire de ce client.
Maintenant il y a plusieurs façons de contourner cela, dont l'une serait de copier le dossier lorsqu'une ordonnance a été créé. Ce que j'ai est bien ce que je pense serait un moyen plus facile de faire ce qui est peut-être un peu plus élégant, et a l'avantage supplémentaire de l'exploitation forestière à chaque fois qu'une modification est apportée.
Ce que si je faisais une structure comme ceci à la place:
orders
------
orderID
customerID
customerHistoryID
customers
---------
customerID
customerHistoryID
customerHistory
--------
customerHistoryID
customerID
address
address2
city
state
zip
updatedBy
updatedOn
s'il vous plaît pardonnez la mise en forme, mais je pense que vous voyez l'idée. Fondamentalement, l'idée est qu'à chaque fois qu'un client est changé, d'insertion ou de mise à jour, la customerHistoryID est incrémenté et la table clients est mis à jour avec les dernières customerHistoryID. Le tableau de commande maintenant, non seulement les points pour le code client (qui vous permet de voir toutes les révisions de la fiche client), mais aussi à la customerHistoryID, qui pointe à une révision particulière de l'enregistrement. Aujourd'hui, cet ordre reflète l'état des données au moment où l'ordre a été créé.
Par l'ajout d'une updatedby et updatedon colonne à la customerHistory table, vous pouvez également voir une "du journal d'audit" des données, de sorte que vous pourriez voir qui a fait les changements et quand.
Un potentiel de baisse pourrait être supprime, mais je ne suis pas vraiment inquiet à propos de ce besoin que rien ne doit jamais être supprimé. Mais même encore, le même effet peut être obtenu en utilisant un activeFlag ou quelque chose comme cela en fonction sur le domaine des données.
Ma pensée est que toutes les tables à utiliser cette structure. À tout moment l'historique des données est en cours de récupération, il serait jointe à l'encontre de l'histoire de la table à l'aide de la customerHistoryID pour afficher l'état des données de cette commande.
Récupération d'une liste de clients est facile, il suffit de se joindre à la table des clients sur le customerHistoryID.
Quelqu'un peut voir les problèmes avec cette approche, à partir d'un point de vue design, ou la performance des raisons pourquoi c'est mauvais. Rappelez-vous, peu importe ce que je fais j'ai besoin pour s'assurer que les données historiques sont préservées, de sorte que par la suite, les mises à jour des enregistrements de ne pas changer l'histoire. Est-il un meilleur moyen? Est-ce une vieille idée qui a un nom, ou de toute documentation à ce sujet?
Merci pour toute aide.
Mise à jour:
C'est un exemple très simple de ce que je vais vraiment avoir. Mon application réelle aura des "ordres" avec plusieurs clés étrangères vers d'autres tables. Origine/de destination de l'information, information de la clientèle, renseignements sur l'installation, l'utilisateur de l'information, etc. Il a été suggéré une couple de fois que j'ai pu copier les informations dans l'ordre à ce point, et j'ai vu faire de cette façon beaucoup de temps, mais il en résulterait un enregistrement avec des centaines de colonnes, ce qui n'est vraiment pas possible dans ce cas.
source d'informationauteur Ryan Guill
Vous devez vous connecter pour publier un commentaire.
Lorsque j'ai rencontré ce genre de problèmes une alternative est de faire la commande le tableau de l'historique. Son fonctionnement est le même, mais c'est un peu plus facile à suivre
EDIT: si le nombre de colonnes obtient élevé pour votre convenance, vous pouvez séparer toutefois vous le souhaitez.
Si vous allez avec l'autre option, et en utilisant les tables d'historique, vous devriez envisager d'utiliser bitemporale de données, car vous pourriez avoir à faire face à la possibilité que des données historiques qui doit être corrigée. Par exemple, la Clientèle a Changé son adresse actuelle De A à B, mais vous avez aussi à la bonne adresse dans un ordre existant qui est actuellement à être remplies.
Aussi, si vous utilisez MS SQL Server, vous pouvez envisager d'utiliser des vues indexées. Qui va vous permettre de commerce d'un petit différentiels insertion/mise à jour de la perf de baisse pour une grande sélectionnez perf augmentation. Si vous n'êtes pas à l'aide de MS SQL server, vous pouvez reproduire cela à l'aide de déclencheurs et les tables.
Lors de la conception de vos structures de données, être très carful pour stocker les relations correctes, pas quelque chose qui est similaire à la bonne relations. Si l'adresse d'un ordre doit être maintenu, alors que c'est parce que l'adresse est une partie de la commande, pas le client. Aussi, les prix unitaires sont une partie de la commande, et non pas le produit, etc.
Essayer un arrangement comme ceci:
Si vous avez vraiment besoin de stocker histoire pour quelque chose, comme le suivi des modifications à une commande dans le temps, alors vous devriez le faire avec un journal ou d'un audit de la table, non pas avec vos tables de transactions.
Normalement les commandes il suffit de stocker l'information, puisqu'il est au moment de la commande. Cela est particulièrement vrai des choses comme des numéros de pièces, des noms et des prix ainsi que l'adresse du client et le nom. Ensuite, vous ne t ont à se joindre à 5 ou six tables pour obtenir les informations qui peuvent être stockées dans une. Ce n'est pas la dénormalisation que vous avez réellement besoin d'avoir la innformation telle qu'elle existait au moment de la commande. Je pense, est moins susceptible que d'avoir cette information dans l'ordre et de l'ordre du détail (magasins de chacun des articles commandés) tables est moins risquée en termes de modification accidentelle de données.
Votre table de l'ordre n'aurait pas des centaines de colonnes. Vous avez une table de l'ordre et un ordre détail de la table grâce à un de nombreuses relations. Tableau de commande comprendrait. id de client 9so vous pouvez rechercher tout ce client a jamais commandé, même si le nom a été changé), le nom du client adresse du client (notez que vous n'avez pas besoin de l'etat de la cité zip etc, mettre l'adresse dans un champ), la date de commande et peut-être quelques autres domaines qui se rapportent directement à l'ordre, à un niveau supérieur. Ensuite, vous avez une commande en détail le tableau qui a le numéro de commande, detail_id, numéro de pièce description de la pièce (ce peut être une consolidation d'un tas de champs tels que la taille, la couleur, etc. ou vous pouvez séparer le plus commun), Pas d'articles, le type d'unité, prix par unité, les taxes, montant du prix, date de livraison, le statut. Vous mettez une entrée pour chaque article commandé.
Moi, j'aime garder les choses simples. Je voudrais utiliser deux tables, une table client et un client de l'histoire de la table. Si vous avez la clé (par exemple, le code client) dans le tableau de l'historique il n'y a aucune raison de faire une rencontre de la table, sélectionnez sur cette touche vous donnera tous les records.
Vous aussi vous n'avez pas de vérification de l'information (par exemple, date de modification, qui l'a modifié, etc) dans l'histoire de la table comme vous le montrer, j'attends que vous voulez.
Donc mien pourrait ressembler à quelque chose comme ceci:
La DataOfChagne champ est la date à laquelle le tableau a été modifié (à partir des valeurs dans ce dossier) pour les valeurs plus récent enregistrement des valeurs dans la CustomerTable
Vous les commandes de la table a juste besoin d'un code client si vous avez besoin de trouver les informations sur le client au moment de la commande, il est un simple select.
Ce que vous voulez est appelé un datawarehouse. Depuis datawarehouses sont OLAP et pas OLTP, il est recommandé d'avoir autant de colonnes que vous avez besoin pour atteindre vos objectifs. Dans votre cas, la
orders
table dans le datawarehouse aura 11 champs comme ayant un "instantané" d'ordres comme ils viennent, quel que soit les comptes utilisateurs des mises à jour.C'est un bon début.
Notre système de paie utilise date dans de nombreux tableaux. La table des ADRESSES est à la clé sur numéro d'identification et EFFDT. Cela nous permet de suivre chaque fois qu'un employé du changement d'adresse. Vous pouvez utiliser la même logique pour suivre l'historique des adresses pour les clients. Vos requêtes suffit d'inclure une clause qui compare la date de la commande à l'adresse du client date qui était en vigueur au moment de la commande. Par exemple
L'objectif est de sélectionner la ligne la plus récente dans les clients ayant une date d'effet avant ou à la date de la commande. Cette stratégie pourrait être utilisée pour conserver les données historiques sur les prix des produits.
Si vous êtes réellement intéressé dans de tels problèmes, je ne peux que vous suggérer de prendre un air sérieux à Temporelles de Données et le Modèle Relationnel".
Warning1 : il n'y a pas de SQL là, et presque tout ce que vous pensez savoir sur le modèle relationnel sera réclamé un mensonge. Avec une bonne raison.
Warning2 : vous êtes censé penser, et de dur.
Warning3 : le livre est au sujet de ce qu'est la solution pour cette famille de problèmes devraient ressembler, mais que l'introduction dit, il ne s'agit pas de toute la technologie disponible aujourd'hui.
Cela dit, le livre est une véritable illumination. À tout le moins, il aide à le rendre clair que la solution de ces problèmes ne seront pas trouvés dans SQl tel qu'il est aujourd'hui, ou dans Orm comme celui d'aujourd'hui, pour cette question.