SQL history table design

Question

I need to design a history table to keep track of multiple values that were changed on a specific record when edited.

Example:
The user is presented with a page to edit the record.

Title: Mr.
Name: Joe
Tele: 555-1234
DOB: 1900-10-10

If a user changes any of these values I need to keep track of the old values and record the new ones.

I thought of using a table like this:

History
---------------

id
modifiedUser
modifiedDate
tableName
recordId
oldValue
newValue

One problem with this is that it will have multiple entries for each edit. I was thinking about having another table to group them but you still have the same problem.

I was also thinking about keeping a copy of the row in the history table but that doesn't seem efficient either.

Any ideas?

Thanks!

Answer 1

I would recommend that for each table you want to track history, you have a second table (i.e. tblCustomer and tblCustomer_History) with the identical format - plus a date column.

Whenever an edit is made, you insert the old record to the history table along with the date/time. This is very easy to do and requires little code changes (usually just a trigger)

This has the benefit of keeping your 'real' tables as small as possible, but gives you a complete history of all the changes that are made.

Ultimately however, it will come down to how you want to use this data. If its just for auditing purposes, this method is simple and has little downside except the extra disk space and little or no impact on your main system.