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.


  • Do you know in advance how many tables you will be tracking?
    – Kofi Sarfo
    Jan 8, 2013 at 16:12
  • This table will only track one other table, so far we are only tracking this one table.
    – user76982
    Jan 8, 2013 at 16:17
  • So this approach might be overkill. Even worse it won't be easy then to query this table with the one being monitored together to recreate the live data at a point in time, should recreating a snapshot be of any value.
    – Kofi Sarfo
    Jan 8, 2013 at 16:22
  • 1
    On the flip side the query to see which columns change most often will be easier using the approach you've proposed.
    – Kofi Sarfo
    Jan 8, 2013 at 16:24
  • You might want to search on dba.stackexchange.com ; similar questions have been asked there and some might have answers you can use. Jan 8, 2013 at 18:19

4 Answers 4


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.


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.


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:

  • Second link is bad now. Jun 19, 2015 at 14:19
  • @cillosis, Thanks for noticing this. It is updated now :)
    – Yusubov
    Jun 26, 2015 at 14:04

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.