Ways to have a history of changes of database entries

Question

What are ways to allow the versioning of database entries (data)?

Think of the content-managment-systems abilities to revert back changes of articles.

What are their pros/cons?

Answer 1

There are basically two approaches: an audit table, with all previous values stored in it, or include a start/end date as part of the table, and all updates create a new record while closing out the old one.

Answer 2

Idea behind is to Use Insert Only Databases. The basic idea is that you never update or update data on a row. Each table that is required to track will has 2 datetime columns from and to.

They start with the value null in each (beginning of time to end of time)

When you need to change the row you add a new row, at the same time you update the to in the previous row to Now and the from in the row you are adding to Now.

For more detailed info look at the post - What is the best database design for managing historical information.

Answer 3

You're probably aware of Sqlite db engine. The whole db is saved in a single file. The api also supports virtual file systems so basically you can organize the storage anywhere and with any format, just respond to read and write operations at particular file offsets. Possible applications for this could be encryption, compression and so on. The best part of it that the container layer should not know anything about databases, sql or sqlite file format, just obey xRead and xWrite callbacks.

One of the ideas was to implement time-machine feature. So any xWrite operation saves every segment it would overwrite in "undo" history and the user can choose a date in the past to see what the db contained (probably read-only mode). I don't have working example yet (there was a discussion about it at sqlite mail list), but probably other engines supply VFS APIs so something similar is possible. And once it implemented, it should be compatible with database structures of any complexity.

Answer 4

I think you can use triggers for each table and maintain the data in _history (or you can give any name) and on every insert, update, delete on main table will trigger your trigger and you can save the details in this table.Trigger mechanism is also available with SQLite database if you are using one.

This mechanism is useful for large projects as well. In this table you can log information of user who have made the changes along with the time-stamp of the changes. you then can restore your table to any of the time-stamp matching to your requirements.

Every Database has its own way to write and code triggers. If you are using SQLite visit SQLite.org for the syntax. For other databases you can visit their official sites.

Answer 5

I'm doing a version of this now. for every record I have an Inserted Date, Modified date and and Active Record boolean flag. For the initial insert Inserted and Modified dates are both set to Now() (This example is in Access) and the Active record flag is set to true. then if I modify that record I copy the whole thing to a new record, changing the field(s) the user is changing, I leave the Insert date equal to the original and change the Modified date to Now(). I then flip the Active Record flag of the original record to false and the new record to true. I also have a field for ModifiedRecordsParentID where I save the identity of the original record.

Then If I even need to query I can just return records where ActiveRecord = true and I will only get the most up to date information.

Answer 6

The method we use for versioning database entries is to use an auditing table. The table has a schema along the lines of:

Seq      - Int      ' Unique identifier for this table
Event    - Char     ' Insert / Update / Delete
TblName  - Char     ' Table that had field value changed
FldName  - Char     ' Field that was changed
KeyValue - Char     ' delimited list of values for fields that make up the PK of table changed
UsrId    - Char     ' User who made the change
OldValue - Char     ' Old value (converted to character)
NewValue - Char     ' New value (converted to character)
AddTs    - DateTime ' When the change was made

We then have triggers on Insert / Update / Delete of the tables that we want to track.

Pros:

• All the data is in one table
• Can be setup to track all fields or specific fields in a table
• Easy to show versioning on each field for a table

Cons:

• Having all auditing information in one table results in an extremely large number of records
• Lots of triggers needed