Conception de base de données pour l’enregistrement d’audit

Question

Chaque fois que j'ai besoin de desing une nouvelle base de données je passe très peu de temps la réflexion sur comment je dois configurer le schéma de base de données pour garder un journal d'audit de les modifications.

Certaines questions ont déjà été posées ici sur ce sujet, mais je ne suis pas d'accord que il y a une meilleure approche unique pour tous les scénarios:

• Conception De Base De Données Pour Les Révisions
• Meilleur design pour un changelog audit de la table de base de données
• Des idées sur la conception de base de données pour la capture des pistes de vérification

J'ai aussi tombé sur cet article intéressant sur le Maintien d'un Journal des Modifications de Base de données qui tente de liste les pro et les inconvénients de chaque approche. C'est très bien écrit et a des informations intéressantes, mais il a fait de mes décisions encore plus difficile.

Ma question est: Est-il que je peux utiliser, peut-être un livre ou quelque chose comme un arbre de décision que je peux utiliser pour décider de quelle façon dois-je faire, basé sur certains les variables d'entrée, comme:

• La maturité du schéma de base de données
• Comment les journaux seront interrogés
• La probabilité qu'il sera nécessaire de recréer les dossiers
• Ce qui est plus important: écrire ou de lire des performances
• La Nature des valeurs qui sont enregistrées (chaîne de caractères, des nombres, des gouttes)
• L'espace de stockage disponible

Les approches que je connais sont:

1. Ajouter des colonnes de création et de modification de la date et de l'utilisateur

Exemple de Table:

• id
• valeur_1
• value_2
• value_3
• created_date
• modifed_date
• created_by
• modified_by

Principaux inconvénients: Nous perdons l'historique des modifications. Ne peut pas rollback après validation.

2. Insérez uniquement des tables

Exemple de Table:

• id
• valeur_1
• value_2
• value_3
• à partir de
• pour
• supprimé (boolean)
• l'utilisateur

Principaux inconvénients: Comment garder les clés étrangères jusqu'à ce jour? Immense espace nécessaire

3. Créer un tableau de l'historique de chaque tableau

Histoire de la table exemple:

• id
• valeur_1
• value_2
• value_3
• value_4
• l'utilisateur
• supprimé (boolean)
• timestamp

Principaux inconvénients: Nécessite de dupliquer tout vérifié tables. Si les modifications du schéma, il sera nécessaire à la migration de tous les journaux.

4. Créer un Consolidés Tableau de l'historique de Toutes les Tables

Histoire de la table exemple:

• table_name
• champ
• l'utilisateur
• nouvelle_valeur
• supprimé (boolean)
• timestamp

Principaux inconvénients: Vais-je être en mesure de recréer les dossiers (rollback), si besoin est facilement? Le nouvelle_valeur colonne doit être une énorme chaîne de caractères, donc il peut prendre en charge tous les différents types de colonne.

Answer 1

Une méthode qui est utilisée par un peu de wiki plates-formes est de séparer les données d'identification et le contenu de l'audit. Il ajoute de la complexité, mais vous vous retrouvez avec une piste de vérification des dossiers complets, et non pas juste des listes de champs qui ont été modifiés que vous aurez ensuite à fusionner pour donner à l'utilisateur une idée de ce que l'ancien record ressemblait.

Ainsi, par exemple, si vous aviez une table appelée Possibilités pour suivre les transactions de vente, vous auriez fait de créer deux tableaux distincts:

Possibilités
Opportunities_Content (ou quelque chose comme ça)

Les Opportunités de table comporte des informations que vous souhaitez utiliser pour identifier de manière unique l'enregistrement et serait à la maison de la clé primaire, vous auriez référence pour vos relations de clé étrangère. Le Opportunities_Content table contenir tous les champs de vos utilisateurs peuvent changer et pour lequel vous souhaitez conserver une piste de vérification. Chaque enregistrement dans le Contenu de la table permettrait de comprendre son propre PK et modifiée par et modifié des données. Les Possibilités de table d'inclure une référence à la version actuelle ainsi que des informations sur le moment où l'enregistrement principal a été initialement créé et par qui.

Voici un exemple simple basé sur ScrewTurn du schéma de données:

CREATE TABLE dbo.Page(  
    ID int PRIMARY KEY,  
    Name nvarchar(200) NOT NULL,  
    CreatedByName nvarchar(100) NOT NULL, 
    CurrentRevision int NOT NULL, 
    CreatedDateTime datetime NOT NULL

Et le contenu:

CREATE TABLE dbo.PageContent(
    PageID int NOT NULL,
    Revision int NOT NULL,
    Title nvarchar(200) NOT NULL,
    User nvarchar(100) NOT NULL,
    LastModified datetime NOT NULL,
    Comment nvarchar(300) NULL,
    Content nvarchar(max) NOT NULL,
    Description nvarchar(200) NULL

Je serais probablement faire de la PK de la table de contenu multi-colonne de clé de PageID et de la Révision prévue de la Révision était un type d'identité. Vous devez utiliser la Révision de la colonne de la FK. Vous tirez ensuite les états d'enregistrement en se Joignant comme ceci:

SELECT * FROM Page
JOIN PageContent ON CurrentRevision = Revision AND ID = PageID

Il y a peut être quelques erreurs là-haut...c'est hors de ma tête. Il devrait vous donner une idée d'un autre modèle.

Josh

Answer 2

Si vous utilisez SQL Server 2008, vous devez probablement envisager Capture de données modifiées. C’est une nouveauté pour 2008 et pourrait vous faire économiser une quantité considérable de travail.

Answer 3

Je ne sais pas du tout référence, mais je suis sûr que quelqu'un a écrit quelque chose.

Toutefois, si le but est simplement d’avoir un compte rendu de ce qui s’est passé-l’utilisation plus typique d’un audit log-alors pourquoi ne pas simplement garder tout :

Sans doute c’est maintenu par un déclencheur.

Answer 4

Je pense qu'il n'y a rien de tel qu'un arbre de décision. Depuis certains des avantages et des inconvénients (ou les conditions) ne sont pas vraiment dénombrable. Comment mesurer la Maturité, par exemple?

Donc, juste la ligne de vos besoins pour votre enregistrement d'audit. Essayer de prévoir comment ces exigences pourrait changer dans l'avenir et de générer vos exigences techniques. Maintenant vous pouvez comparer les avantages et les inconvénients et de choisir la bonne/meilleure option.

Et rassurez vous, il n'a pas d'importance comment vous décider, il y aura toujours quelqu'un qui pense que vous avez pris la mauvaise décision. Cependant, vous avez fait vos devoirs et de vous justifier votre décision.