Temporal Databases (Database design questions)

[M.-A. Lemburg]

On 2008-06-18 09:41, David wrote:
> Question 3: Temporal databases
> 
> http://en.wikipedia.org/wiki/Temporal_database
> 
> I haven't used them before, but I like the idea of never
> deleting/updating records so you have a complete history (a bit like
> source code version control).
> 
> How well do temporal databases work? Do RDBMS (like Postgresql) need
> add-ons to make it effective, or can you just add extra temporal
> columns to all your tables and add them to your app queries? Does this
> increase app complexity and increase server load a lot?
> 
> Are there Python libraries which simplify this? (eg: add-ons for
> Elixir or SQLAlchemy).
> 
> Or should apps all implement their own 'temporal data access' module,
> which transparently uses the current date & time until queried for
> historical data?

You can have complete history in a database schema by:

* adding a version column
* adding a modification timestamp (and modification username,
   if that's relevant for you)
* updating the version upon INSERT and UPDATE
* have a history table for each "live" table that gets
   filled using a trigger on the version column which moves
   the inserted/updated/deleted rows into the history table
* the history table will have to have an additional column
   for storing the method of how the row got into the table
   (ie. insert/update/delete)

The main app will only use the "live" tables with the current
data. An audit tool would then provide access to the history
tables.

This works for all databases that have triggers, even SQLite.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jun 18 2008)
 >>> Python/Zope Consulting and Support ...        http://www.egenix.com/
 >>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2008-07-07: EuroPython 2008, Vilnius, Lithuania            18 days to go

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


    eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
     D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
            Registered at Amtsgericht Duesseldorf: HRB 46611