Logging changes for every column in a table

Question

I am busy creating a system where I need to keep track of every change in the system. In other words, when a column in the database is changed, I need to know what table, which column, when the change was made, by which user, from what value, to what value.

My first thought was to create a second table for each table for logging purposes, containing fields like column_name, updated_by, updated_on, from_value, to_value (keeping the from_value and to_value fields as strings for simplicity). This, however, will essentially create a duplicate of the database.

My second option would be to create a massive table of a similar type (table_name, column_name, updated_by, updated_on, from_value, to_value) for all the tables, yet this will result in an unmanageable table, as changes will be happening frequently.

Both these option have the same problem, that I am unsure how to reference the columns of a table, and worst of all, how do I handle a change in column names later in the life of the application.

Any thoughts and suggestions would be appreciated.

Answer 1

I'm going to make some assumptions here:

• you're not constrained by disk space
• you have a non-trivial data model
• you need to be able to report your audit/history information in a human-readable format
• you're not working with extreme performance or scalability requirements
• the audience for your audit data is business user level, not technical level.

In that case, the best solution I know is to make "history" a first-class concept in your design. The link GregL quoted has a good description of this; my simpler implementation basically means having "valid_from" and "valid_until" and "operator_id" columns on every table, and to use "is_valid" rather than the delete operation.

This is better than auditing changes to separate tables, because it allows you to create a complete picture of your data at any given point in history, complete with all the relationships between tables, using the same logic as your regular data access code. That, in turn, means you can create reports using standard reporting tools, answering questions like "which operator changed the prices for all products in the food category", "how many products were less than $100 on 1 Jan?" etc.

It does consume more space, and it does make your database access logic more complex. It also doesn't play nicely with ORM solutions.