Conception de base de données pour les révisions ?

Question

Nous avons une exigence dans le projet de stocker toutes les révisions(Historique des modifications) pour les entités dans la base de données. Actuellement, nous avons 2 conçu propositions pour ceci:

par exemple, pour les "Employés" de l'Entité

Conception 1:

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- Holds the Employee Revisions in Xml. The RevisionXML will contain
-- all data of that particular EmployeeId
"EmployeeHistories (EmployeeId, DateModified, RevisionXML)"

Conception 2:

-- Holds Employee Entity
"Employees (EmployeeId, FirstName, LastName, DepartmentId, .., ..)"

-- In this approach we have basically duplicated all the fields on Employees 
-- in the EmployeeHistories and storing the revision data.
"EmployeeHistories (EmployeeId, RevisionId, DateModified, FirstName, 
      LastName, DepartmentId, .., ..)"

Est-il un autre moyen de faire cette chose?

Le problème avec le "1" est que nous avons à analyser le XML à chaque fois quand vous en avez besoin pour accéder à des données. Cela va ralentir le processus et également ajouter quelques limitations comme nous ne peut pas ajouter des jointures sur les révisions des champs de données.

Et le problème avec la "2" est que nous avons pour dupliquer chaque et chaque domaine sur toutes les entités (Nous avons environ 70 à 80 entités pour lesquelles nous voulons maintenir les révisions).

Answer 1

Je pense que la principale question à se poser ici est "Qui / qu'est-Ce que va être l'aide de l'histoire"?

Si ça va être surtout pour les rapports / lisible par l'homme de l'histoire, nous avons mis en œuvre ce système dans le passé...

Créer une table appelée "AuditTrail" ou quelque chose qui a les champs suivants...

[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[EventDate] [datetime] NOT NULL,
[TableName] [varchar](50) NOT NULL,
[RecordID] [varchar](20) NOT NULL,
[FieldName] [varchar](50) NULL,
[OldValue] [varchar](5000) NULL,
[NewValue] [varchar](5000) NULL

Vous pouvez ensuite ajouter un " LastUpdatedByUserID colonne à toutes vos tables, qu'il faut donner à chaque fois que vous effectuez une mise à jour / à insérer sur la table.

Vous pouvez ensuite ajouter un déclencheur à chaque table pour attraper l'insertion / mise à jour qui arrive et crée une entrée dans cette table pour chaque champ modifié. Parce que la table est également fourni avec le 'LastUpdateByUserID' pour chaque mise à jour / insert, vous pouvez accéder à cette valeur dans le déclenchement et l'utiliser lors de l'ajout à la table d'audit.

Nous utilisons le RecordID champ pour stocker la valeur du champ clé de la table en cours de mise à jour. Si c'est une clé commune, nous venons de faire une concaténation de chaîne avec un '~' entre les champs.

Je suis sûr que ce système peut avoir des inconvénients, pour largement mises à jour des bases de données de la performance peut être touché, mais pour mon application web, nous recevons beaucoup plus de lectures que les écritures et il semble fonctionner assez bien. Nous avons même écrit un peu VB.NET utilitaire pour écrire automatiquement les déclencheurs basés sur les définitions de table.

Juste une pensée!

Answer 2

1. Ne pas tout mettre dans un tableau avec une IsCurrent descriminator attribut. Cela ne fait que causer des problèmes sur la ligne, il faut des clés de substitution et toutes sortes d'autres problèmes.
2. Conception 2 a des problèmes avec les changements de schéma. Si vous modifiez la table des Employés, vous devez changer les EmployeeHistories table et toutes les sprocs qui vont avec. Potentiellement double vous de schéma effort de changement.
3. Design 1 place fonctionne bien et si c'est fait correctement ne coûte pas beaucoup en termes de performances. Vous pouvez utiliser un schéma xml et même des indices pour obtenir plus possible les problèmes de performance. Votre commentaire sur l'analyse du xml est valide, mais vous pouvez facilement créer une vue à l'aide de xquery - que vous pouvez inclure dans les requêtes et les rejoindre. Quelque chose comme ceci...

CREATE VIEW EmployeeHistory
AS
, FirstName, , DepartmentId

SELECT EmployeeId, RevisionXML.value('(/employee/FirstName)[1]', 'varchar(50)') AS FirstName,

  RevisionXML.value('(/employee/LastName)[1]', 'varchar(100)') AS LastName,

  RevisionXML.value('(/employee/DepartmentId)[1]', 'integer') AS DepartmentId,

FROM EmployeeHistories 

Answer 3

L’article Tables d’historique dans le blog de Programmeur de base de données peut être utile - couvre une partie des points évoqués ici et discute le stockage des deltas.

Answer 4

Nous avons mis en place une solution très similaire à la solution que propose Chris Roberts, et cela fonctionne assez bien pour nous.

Seule différence, c’est que nous stockons uniquement la nouvelle valeur. L’ancienne valeur est stockée après tout dans la ligne de l’histoire précédente

Disons que vous avez une table avec 20 colonnes. De cette façon, qu'il vous suffit de stocker la colonne exacte qui a changé au lieu d’avoir à stocker l’ensemble de la ligne.

Answer 5

Si vous avez de l'histoire de magasin, faire une ombre de la table avec le même schéma que la table que vous êtes suivi et une "Date de Révision" et "Révision" Type de colonne (par exemple, 'supprimer', 'mise à jour'). Écrire (ou de générer des - voir ci-dessous) un ensemble de déclencheurs pour remplir le tableau d'audit.

Il est relativement simple d'en faire un outil qui va lire le système de dictionnaire de données pour une table et de générer un script qui crée l'ombre de la table et d'un ensemble de déclencheurs pour la remplir.

N'essayez pas d'utiliser XML pour cela, stockage XML est beaucoup moins efficace que le natif de table de base de données de stockage que ce type de déclencheur.