Examples of temporal database designs? [closed]
- by miku
I'm researching various database design for historical record keeping - because I would like to implement a prototypical web application that (excessively) tracks changes made by users and let them undo things, see revisions, etc.
I'd love use mercurial or git as backend (with files as records) - since they already implement the kind of append-only changes I imagine. I tried git and dulwich (python git API) - and it went ok - but I was concerned about performance;
Bi-temporal database design lets you store a row along with time periods, when this record was valid. This sure sound more performant than to operate on files on disk (as in 1.) - but I had little luck finding real-world examples (e.g. from open source projects), that use this kind of design and are approachable enough to learn from them.
Revisions à la MediaWiki revisions or an extra table for versions, as in Redmine. The problem here is, that DELETE would take the whole history with it.
I looked at NoSQL solutions, too. With a document oriented approach, it would be simple to just store the whole history of an entity within the document itself - which would reduce design plus implementation time in contrast to a RDBMS approach. However, in this case I'm a bit concerned about ACID-properties, which would be important in the application.
I'd like ask about experiences about real-world and pragmatic designs for temporal data.
