Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
What exactly do you want to version? The schema or the data? –  tdammers Jul 10 '12 at 5:37
    
I want to version the data. To stay at the example of cms, lets say the versions of articles. –  matcauthon Jul 10 '12 at 5:39
    
You may want to look into Datomic. –  dan_waterworth Jul 10 '12 at 11:33

6 Answers 6

up vote 6 down vote accepted

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.

share|improve this answer
    
So the first approach might be more scalable. As the "archived" data will be rarely accessed, the database design could be optimized. And the working table keeps small. Depending on complexity, it should also possible to save only diffs. Is it advisable to use the memento pattern? –  matcauthon Jul 10 '12 at 9:22
    
That will depend on your usage, it may be enough to use triggers to populate the table/s and then provide a way of picking what and how far to rollback. –  jmoreno Jul 10 '12 at 15:02

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.

share|improve this answer

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.

share|improve this answer
    
What do you think is this approach scalable for larger projects? –  matcauthon Jul 10 '12 at 9:24
    
I think this could add large data overhead for big data changes (obviously since every single changed should be saved, although compression for older version can help). Apart from that from the point of you of your schema, as long as it works for two tables, it works for twenty. –  Maksee Jul 10 '12 at 10:11

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.

share|improve this answer

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.

share|improve this answer
    
No need for the ActiveRecord flag. The MAX(*) row should always be the current record. Restoring to a previous version simply inserts said row into the table again. –  invert Jul 10 '12 at 14:19
    
I wasn't sure how to make the select work, but now that you're calling this out I'm thinking about it and have an idea, hmmmm –  Brad Jul 10 '12 at 14:34
    
Usually MAX(column_name) selects the largest value in the table's column. To select the whole row, a simple select top 1 order by id descending will do. –  invert Jul 11 '12 at 7:46
    
Yeah, that works for a simple single record but my table was a collection of child records which would need to be selected at once but could have been modified individually. Just a little more complex. –  Brad Jul 11 '12 at 12:05

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.