CODEWITHSUNDEEP
sqldatabasedatabase-design
hebime
hebime

Reputation: 577

SQL history table design

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!

Upvotes: 22

Views: 24668

Answers (3)

E.J. Brennan
E.J. Brennan

Reputation: 46879

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.

Upvotes: 20

Paul Keister
Paul Keister

Reputation: 13097

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.

Upvotes: 15

Kermit
Kermit

Reputation: 34062

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

Upvotes: 4

Related Questions