Temporal Databases (Database design questions)
mal at egenix.com
Wed Jun 18 11:16:48 CEST 2008
On 2008-06-18 09:41, David wrote:
> Question 3: Temporal databases
> 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
The main app will only use the "live" tables with the current
data. An audit tool would then provide access to the history
This works for all databases that have triggers, even SQLite.
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
More information about the Python-list