Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Effective strategy for leaving an audit trail/change history for DB applications?

What are some strategies that people have had success with for maintaining a change history for data in a fairly complex database. One of the applications that I frequently use and develop for could really benefit from a more comprehensive way of tracking how records have changed over time. For instance, right now records can have a number of timestamp and modified user fields, but we currently don't have a scheme for logging multiple change, for instance if an operation is rolled back. In a perfect world, it would be possible to reconstruct the record as it was after each save, etc.

Some info on the DB:

  • Needs to have the capacity to grow by thousands of records per week
  • 50-60 Tables
  • Main revisioned tables may have several million records each
  • Reasonable amount of foreign keys and indexes set
  • Using PostgreSQL 8.x
like image 301
Dana the Sane Avatar asked Aug 23 '08 00:08

Dana the Sane


People also ask

What is audit trail in database?

Audit records provide information about the operation that was audited, the user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.

What makes a good audit trail?

Your audit trail should be easy to follow. Anyone looking at the trail should be able to quickly identify mistakes and show if and when they were corrected. It's especially helpful if your audit management program makes it easy to generate an automated audit report for internal and external stakeholders.

How audit trails improve data security?

Audit trails maintain a record of system activity both by system and application processes and by user activity of systems and applications. In conjunction with appropriate tools and procedures, audit trails can assist in detecting security violations, performance problems, and flaws in applications.


2 Answers

One strategy you could use is MVCC, Multi-Value Concurrency Control. In this scheme, you never do updates to any of your tables, you just do inserts, maintaining version numbers for each record. This has the advantage of providing an exact snapshot from any point in time, and it also completely sidesteps the update lock problems that plague many databases.

But it makes for a huge database, and selects all require an extra clause to select the current version of a record.

like image 181
Eric Z Beard Avatar answered Nov 08 '22 13:11

Eric Z Beard


If you are using Hibernate, take a look at JBoss Envers. From the project homepage:

The Envers project aims to enable easy versioning of persistent JPA classes. All that you have to do is annotate your persistent class or some of its properties, that you want to version, with @Versioned. For each versioned entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.

This is somewhat similar to Eric's approach, but probably much less effort. Don't know, what language/technology you use to access the database, though.

like image 21
Martin Klinke Avatar answered Nov 08 '22 12:11

Martin Klinke