Are there any database implementations which keep all history?

Question

Using a version control system for your source code (like subversion) makes sense because it allows you to back out of mistakes, audit changes, make painless snapshots, discover exactly where something went wrong so that you can improve your process etc. For the same reasons it makes sense to do change tracking of business data, and many systems do so.

There are already a few questions on how to implement this on top of a normal database:

For a feature that is so useful and popular, it seems strange that we all need to reinvent the wheel. Are there any existing database implementations which already solved this problem? I'm imagining that such a system would extend the SQL syntax to allow easy querying of the history.

Matthew Flaschen · Accepted Answer · 2009-07-07T09:44:45.917

6

Take a look at temporal databases, such as TimeDB.

edited Jul 07 '09 at 09:44

answered Jul 07 '09 at 08:56

Matthew Flaschen

255,933
45
489
528

+1 for putting a name on the concept so that I can finally get some relevant google hits :-) – Wim Coenen Jul 07 '09 at 09:13

score 2 · Answer 2 · answered Jul 07 '09 at 09:00

2

Not a relational database (you didn't say it had to be), but CouchDB has versioning built-in.

answered Jul 07 '09 at 09:00

Sasha Chedygov

116,670
26
98
110

+1 interesting. A very fresh approach to the whole problem of storing and querying data. – Wim Coenen Jul 07 '09 at 12:09

score 1 · Answer 3 · answered Jul 07 '09 at 08:56

1

The space requirements would be prohibitive, so this is why you typically roll your own.

There are different solutions, depending on your toolkit:

Hibernate EnVers for plugging in Hibernate;
HBase has limited versioning built-in;

answered Jul 07 '09 at 08:56

Robert Munteanu

63,405
31
191
270

3

This doesn't make sense. Yes, such databases are inherently bulky. Why does that mean you should roll your own, though? – Matthew Flaschen Jul 07 '09 at 08:57
Roll your own - implement it yourself. – Robert Munteanu Jul 07 '09 at 08:58
I know what roll your own means. The question is why. – Matthew Flaschen Jul 07 '09 at 09:44
@Matthew Flaschen - I misread your question, sorry. I meant to say that in my opinion this is a situation where it's too difficult to specify exactly what/how/for how long/where to archive so there is no general implementation available, therefore the solutions are custom-made. – Robert Munteanu Jul 07 '09 at 10:00

score 0 · Answer 4 · answered Jul 07 '09 at 09:50

0

As far as the data goes, I believe it's called "change data capture".

answered Jul 07 '09 at 09:50

duffymo

293,097
41
348
541

score -1 · Answer 5 · answered Jul 07 '09 at 09:04

-1

Given that most countries require that all accounting transactions are logged, pretty well every database lets you record the history for auditing.

answered Jul 07 '09 at 09:04

Pete Kirkham

46,814
5
86
159

I'm looking for "lets you query the history", not "lets you record the history". – Wim Coenen Jul 07 '09 at 09:14
If your original schema has time stamps, it's not a problem. If your original schema does not, load the history adding them. – Pete Kirkham Jul 07 '09 at 10:20

Are there any database implementations which keep all history?

5 Answers5