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?
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.
Update: SQL SERVER 2016 supports this as a design pattern/table type — https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-2017
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.
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.
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.
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.
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
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.
also, if you want to store ALL changes to the DB over time, you might want to check out logging (https://stackoverflow.com/questions/3394132/where-can-i-find-the-mysql-transaction-log)