meilleur design pour un changelog / audit de la table de base de données?

Question

j'ai besoin de créer une table de base de données pour stocker les différents changement log/audit (quand quelque chose a été ajouté,supprimé,modifié,etc). je n'ai pas besoin de stocker particulièrement détaillée de l'info, donc je pensais à quelque chose le long des lignes de:

• id (par événement)
• l'utilisateur qui l'a déclenchée
• nom de l'événement
• description de l'événement
• horodatage de l'événement

suis-je manqué quelque chose? évidemment, je peux continuer à améliorer la conception, bien que je n'ai pas de plan, c'est compliqué (créer d'autres tables pour les types d'événements ou des trucs comme ça, c'est hors de question puisque c'est une complication pour mon besoin)

Answer 1

Dans le projet sur lequel je travaille, journal d'audit a également commencé à partir de la très design minimaliste, comme celui que vous avez décrit:

event ID
event date/time
event type
user ID
description

L'idée est la même: pour garder les choses simples.

Cependant, il est rapidement devenu évident que cette conception minimaliste n'était pas suffisant. Typique de la vérification était d'ébullition bas sur ce genre de questions:

Who the heck created/updated/deleted a record 
with ID=X in the table Foo and when?

Donc, pour être en mesure de répondre à de telles questions rapidement (à l'aide de SQL), nous avons fini par avoir deux colonnes supplémentaires dans la table d'audit

object type (or table name)
object ID

C'est lors de la conception de notre journal d'audit vraiment stabilisé (depuis quelques années maintenant).

Bien sûr, le dernier "amélioration" ne fonctionne que pour les tables qui avait les clés de substitution. Mais devinez quoi? Toutes nos tables qui valent la peine d'audit ont une telle clé!

Answer 2

Nous avons également enregistrer les anciennes et les nouvelles valeurs et de la colonne, ils sont ainsi que de la clé primaire de la table sont vérifiés lors d'une vérification en détail de la table. Pensez à ce que vous avez besoin de la table d'audit? Non seulement voulez-vous savoir qui a fait un changement et quand, mais quand un mauvais changement se produit, vous voulez un moyen rapide de mettre les données en arrière.

Lorsque vous concevez, vous devez écrire le code pour récupérer les données. Lorsque vous avez besoin de récupérer, il est généralement pressé, mieux-être déjà préparé.

Answer 3

Il y a plusieurs autres choses que vous pourriez vouloir vérification de la table/les noms de colonne, l'ordinateur /l'application à partir de laquelle la mise à jour et plus.

Maintenant, cela dépend de la façon dont l'audit détaillé vous avez vraiment besoin et à quel niveau.

Nous avons commencé à construire notre propre déclenchement de l'audit basée sur la solution et nous avons voulu tout vérifier et ont également une option de récupération à portée de main. Cela s'est avéré trop complexe, nous avons donc fini de l'ingénierie inverse de déclenchement en fonction troisième partie de l'outil de ApexSQL Audit de créer notre propre solution personnalisée.

Conseils:

-Inclure avant/après les valeurs

-Inclure 3,4 colonnes pour le stockage de la clé primaire (dans le cas où c'est une clé composite)

-Stocker des données en dehors des principales base de données comme déjà suggéré par Robert

-Passer la bonne quantité de temps sur la préparation de rapports – en particulier ceux que vous pourriez avoir besoin pour la récupération

-Plan pour le stockage d'hôte/nom de l'application – cela pourrait être très utile pour le suivi des activités suspectes

Answer 4

Il y a beaucoup de réponses intéressantes ici et dans les autres questions. La seule chose que je peux ajouter à partir de l'expérience personnelle est...

1. Mettez votre audit de la table dans une autre base de données. Idéalement, vous voulez de la séparation de l'origine des données. Si vous avez besoin de restaurer votre base de données, vous ne voulez pas vraiment à la restauration de la piste d'audit.

2. Pour éliminer autant que raisonnablement possible. Vous souhaitez que le tableau que quelques dépendances de l'original que possible des données. La table d'audit devraient simple et rapide de foudre pour récupérer des données à partir d'. Pas de fantaisie, des jointures ou des recherches à travers d'autres tables pour obtenir les données.

Answer 5

Il y a beaucoup de façons de le faire. Ma façon préférée est:

0 - Ajouter un mod_user champ à votre table source (celui que vous voulez vous connecter)

1 - Créer une table de journal qui contient les champs que vous voulez vous connecter, en plus d'une log_datetime et seq_num champ. seq_num est la clé primaire.

2 - Construire un trigger sur la table source, qui vérifie pour un changement à l'une de champs surveillée, et insère l'enregistrement en cours dans la table du journal sur tout changement

Maintenant, vous avez un enregistrement de tous les changements et qui l'a fait.