22

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!

hebime
  • 577
  • 1
  • 5
  • 15
  • I can say that we implemented the History table in our system and it works great. Yes, there are multiple entries per edit but if you need to track these details for audit reasons this is the best way to do this. – Taryn Dec 04 '12 at 19:20
  • Thats nearly the same way I've implemented for my projects, except that I'm missing a column `columnName` in your example. I'm historizing only values that really changed (so not all column of a row) and I'm fine with this solution. – rabudde Dec 04 '12 at 20:01

3 Answers3

19

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.

E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
  • Thanks! I think I'm going to go this route. It seems to fit the model of my current system more. – hebime Dec 05 '12 at 19:48
15

You should define what type of efficiency you're interested in: you can have efficiency of storage space, efficiency of effort required to record the history (transaction cost), or efficiency of time to query for the history of a record in a specific way.

I notice you have a table name in your proposed history table, this implies an intention to record the history of more than one table, which would rule out the option of storing an exact copy of the record in your history table unless all of the tables you're tracking will always have the same structure.

If you deal with columns separately, i.e. you record only one column value for each history record, you'll have to devise a polymorphic data type that is capable of accurately representing every column value you'll encounter.

If efficiency of storage space is your main concern, then I would break the history into multiple tables. This would mean having new column value table linked to both an edit event table and a column definition table. The edit event table would record the user and time stamp, the column definition table would record the table, column, and data type. As @njk noted, you don't need the old column value because you can always query for the previous edit to get the old value. The main reason this approach would be expected to save space is the assumption that, generally speaking, users will be editing a small subset of the available fields.

If efficiency of querying is your main concern, I would set up a history table for every table you're tracking and add a user and time stamp field to each history table. This should also be efficient in terms of transaction cost for an edit.

Paul Keister
  • 12,851
  • 5
  • 46
  • 75
  • 2
    I would also like to point out that one audit table for lots of other tables will become a locking pain point. – HLGEM Dec 04 '12 at 19:44
  • 1
    @HLGEM It can become a pain point if it is all in one table. The way we implemented the audit table was to split it up by schema. Your database uses schemas to keep tables separated and each schema has an audit table. – Taryn Dec 04 '12 at 20:33
  • Thanks for your help. You were right in assuming that multiple tables were going to be kept track of in one table. I'm considering using a different history table for each other table as it will only be a small subset of fields that would be editable. This seems to be the best fit. – hebime Dec 05 '12 at 19:52
  • excellent point re: polymorphic data type and trade-offs for space vs transaction cost vs query time – allenwlee Jul 19 '15 at 16:31
4

You don't need to record old and new value in a history table. Just record the newest value, author and date. You can then just fetch the most recent record for some user_id based on the date of the record. This may not be the best approach if you will be dealing with a lot of data.

user (id, user_id, datetime, author, ...)

Sample data

id  user_id  datetime          author  user_title  user_name  user_tele ...
1   1        2012-11-05 11:05  Bob
2   1        2012-11-07 14:54  Tim
3   1        2012-11-12 10:18  Bob
Kermit
  • 33,827
  • 13
  • 85
  • 121