Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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!

share|improve this question
    
Do you know in advance how many tables you will be tracking? –  ofraski Jan 8 '13 at 16:12
    
This table will only track one other table, so far we are only tracking this one table. –  user76982 Jan 8 '13 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. –  ofraski Jan 8 '13 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. –  ofraski Jan 8 '13 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. –  FrustratedWithFormsDesigner Jan 8 '13 at 18:19
show 1 more comment

4 Answers

up vote 6 down vote accepted

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.

share|improve this answer
add comment

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:

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Hello Sarfeast, I added the end goal I want to achieve. Sorry for not including this to start with. –  user76982 Jan 8 '13 at 16:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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