How to keep a history of data changes?

Question

I need to store data change history in a database. For example, for some time some users change some properties of some data. Expected Result: we can get change histories for one information, for example

Tom changed title to 'Title one;' James changed name to 'New name' Steve added new_tag 'tag23' 

Based on these change histories, we can get all versions for some data.

Any good idea for this? Not limited to the traditional relationship base.

Answer 1

These are commonly called audit tables. As I usually understand, this is the use of triggers in the database. For each insert / update from the source table, the trigger copies the data to another table with the name of the same table with _AUDIT attached to it (the naming convention does not matter, this is what I use). ORACLE provides you with something called log tables. Using the ORACLE constructor (or manually), you can achieve the same, and developers often add _JN at the end of the log / audit table. This, however, works the same way: triggers in the source table copy data to the audit table.

EDIT: It should also be noted that you can create a new separate schema to manage only your audit tables or you can save them in your schema using the source tables. I do both, it just depends on the situation.

Answer 2

I wrote an article about various options: http://blog.schauderhaft.de/2009/11/29/versioned-data/

If you are not tied to a relational database, there are things called "adding only" databases (I think) that never change data, but only add new versions. For your case, it sounds like perfect. Unfortunately, I do not know any implementation.