Database History Table / Tracking Table

Question

Currently I want to structure a tracking/history table like this:

• PrimaryKey - ID
• OtherTableId - fk
• fieldName - name of the field its tracking
• OldValue
• NewValue
• UserName
• CreateDateTime

So basically I want to have a table that will track another tables history, store the column name of the changed field with the new and old value. My question is can anyone poke holes in this? Also, what is the easiest way to ensure that only a column name from the tables its tracking is entered into the fieldName column? Currently my options are to have a enum in the service I'm building, or create another status table and make the fieldName an fk. Any better ideas?

Edit Goal: There are currently only 2 fields that we care to track. One field will be shown on a web page to display history, the other field will only be accessed by one department and they have access to a view of the database which they’d be able to query. They’d be querying just this one field to get information on who changed the field and what to. This is the reason we wanted to set it where a database field defines the table column rather than having an exact copy of the table record history. We only want two fields tracked with the possibilities of adding or removing fields in the future.

Thanks!

Answer 1

Poking holes: what if the database schema is changed at same point later in time, and a column name changes, or the column is deleted completely? Lots of database system allow this. What will happen to your "fieldName" then?

For data integrity: you must make sure that every update or delete operation will for sure update your tracking table. That is best accomplished by triggers calling a stored procedure. You should make sure only those stored procedure has writing access to your tracking table, so noone else can write wrong values.

If you can live with a db vendor specific solution: most db systems have system tables where the schema information (table names, table ids, column names etc) is stored. You can check if it is possible to set a foreign key reference to such a system table. That would allow to replace the field name by a field ID if the database supports something like this.

Actually, if you need to track whole rows of the specific table including all columns (and not just a small subset of the columns), you should consider @sarfeast's suggestion. Read this article about the drawbacks of name-value-pair models.

Answer 2

The most successful change audit (history tracking) implementation I've seen is less generic and far simpler. It involves creating a change log table for each table you wish to monitor, keeping identical column names and data types (with an additional column for the time stamp).

The end goal, that is, what you'd like to do with the audited data will help evaluate how suitable each approach might be.

Answer 3

In short: You need to set Audit Trail mechanism for the tables that you want to track the value change.

Single audit trail table:

Create a table to log the table name, field name abd old and new versions of the data. For this method it is usual to log both old and new versions of the data and only those fields that have changed. To implement this in triggeres it is a requirement that either there is a primary key on the table or only single rows are updated.

Here is a good post with scripts on how to achieve it - Creating Audit Trails

Other useful references to look:

• Audit Trail – Tracing Data Changes in Database
• Leaving an Audit Trail In Your Database

Answer 4

You might want to check out NHibernate Envers project documentation for ideas.

Basically, you have one revision table where you can add extra data like a timestamp or user. Then, each table you track gets an additional audit table with all the columns duplicated, an fk to the revision table, and the type of revision (add, modify, delete). AFAIK, you wouldn't want your audit tables to have an actual FK to the real table because that would prevent deletes.