4

I need to store data's change histories in database. For example some time some user modify some property of some data. The expected result is we can get the change histories for one data like

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 to achieve this? Not limited to traditional relation database.

donnior
  • 1,055
  • 1
  • 9
  • 12
  • In Oracle, triggers are good for doing this - not sure if it's the same in MySQL or SQLServer - are you looking to do this in a programmatic fashion or by using native database technology? Using native database technology would be the fastest and most likely simplest way to track changes in data. – Zack Macomber Feb 28 '12 at 15:32
  • Thanks, but I don't like to use native database technology. – donnior Feb 28 '12 at 15:34

2 Answers2

2

These are commonly called audit tables. How I generally manage this is using triggers on the database. For every insert/update from a source table the trigger copies the data into another table called the same table name with an _AUDIT appended to it (the naming convention does not matter, it's just what I use). ORACLE provides you with something called journal tables. Using ORACLE designer (or manually) you can achieve the same thing and often developers put a _JN to the end of the journal/audit table. This, however, works the same, with triggers on the source table copying data into the audit table.

EDIT: I should also note that you can create a new separate schema to manage just your audit tables or you can keep them in your schema with the source tables. I do both, it just depends on the situation.

northpole
  • 10,244
  • 7
  • 35
  • 58
1

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 'append only' databases (I think), which never change data, but only append new versions. For your case this sounds kind of perfect. Unfortunately I don't know of any implementation.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I have done something similar using SQLServer. This can be a great solution and like Jens says, you are only ever adding to the table data. It is essentially a journal table and a source table all in one. It should be noted though, that your table data will grow a lot faster then if you separate the source from the audit and can potentially cause performance issues. – northpole Feb 28 '12 at 15:47
  • I think CouchDB is a append only database – velop Jun 22 '16 at 17:57