Is there a best practice for maintaining history in a database?

Question

I don't do database work that often so this is totally unfamiliar territory for me.

I have a table with a bunch of records that users can update. However, I now want to keep a history of their changes just in case they want to rollback. Rollback in this case is not the db rollback but more like revert changes two weeks later when they realized that they made a mistake. The distinction being that I can't have a transaction do the job.

Is the current practice to use a separate table, or just a flag in the current table?

It's a small database, 5 tables each with < 6 columns, < 1000 rows total.

Answer 1

You could use a timestamp to indicate when the record was created and a status indicator to indicate if the record is "Active" or not.

When they make an update to a record, insert a copy of it as a new record (triggers can keep the timestamps current) and then set the old record status to "Archive" and the new one to "Active". If you want, you can also have other audit fields such as "Last Updated timestamp", "Date Archived", "Date Unarchived" (for when you rollback), "Created by User", "Last Modified by User",... If you want a complete history of auditing, you'd need a separate audit table.

Rolling back is simply choosing a version and marking it as "Active" and the others as "Archive".

There are other schemes that are more flexible but also more complex, but you might not need them.

Answer 2

One option is to have all of your tables contain an ID column and a VersionNo column. Then instead of running UPDATES, you insert a new record witht he same ID and an incremented VersionNo.

You can then create a number of views for each of your table that returns just the latest version of each ID to make your Queries easier to manage.

Answer 3

How would you flag all the changes in the current table? I think it would be far easier to use a separate table, linked by the primary key from the main table, using a timestamp for uniqueness.

Speaking from a MS-SQL perspective, you could put FOR INSERT triggers on the tables to populate your history tables.

You could then see, for each table, all the revisions ever done to a record.

Answer 4

FROM THIS QUESTION

http://stackoverflow.com/questions/2536819/how-to-keep-history-of-record-updates-in-mysql

One simple way to keep version history is to create basically an identical table (eg. with _version suffix). Both of the tables would have a version field, which for the main table you increment for every update you do. The version table would have a composite primary key on (id, version).

Whenever you do an update on the actual table, you also INSERT a new row in the version table with duplicate data. Whenever you want to find the version history, all you need to do is something such as SELECT * FROM content_version WHERE id = CONTENT_ID ORDER BY version.

If you use something like Doctrine ORM, it has a behavior that does this for you automatically via event listeners. You can check it out here: http://www.doctrine-project.org/documentation/manual/1_2/en/behaviors#core-behaviors:versionable

OR

The easiest solution (depending on your specific needs) would probably be to add an on update/insert/delete trigger to your table, so you can perform extra logging when data is inserted/updated/deleted. That way even manual interventions on the db will be covered...

Check http://dev.mysql.com/doc/refman/5.1/en/triggers.html for more information.

Answer 5

Frustrated's answer is one solution - another is an audit table that records the changes, when, and by whom. It's really a question of approach. If performance on your application tables is critical, and they could grow massively using an 'active row' approach, then the audit table is better, as it would separate the history from the active stuff (and I would hope that fixing user errors is less common than 'normal' transactions).

Answer 6

I would recommend a second table. A super simple and easy way would be to duplicate the schema of each table and a Date/Time Stamp column and "ModifiedByUserID" column that will store what the original data was.

Alternatively if users are not modifying a lot of records in the same row at one time you could save space and get a better view of what's happening by making the schema below:

ChangesTable

ChangeID 
[TABLE UNIQUE ID] 
UserName 
Field 
OldValue
NewValue 
CreatedDate