Re: 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