469,955 Members | 2,494 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,955 developers. It's quick & easy.

Re: Temporal Databases (Database design questions)

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
(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

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

Marc-Andre Lemburg

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
Jun 27 '08 #1
0 816

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Otis Green | last post: by
2 posts views Thread by Gary L. Burnore | last post: by
reply views Thread by Cara | last post: by
5 posts views Thread by Thomas Jespersen | last post: by
3 posts views Thread by LurfysMa | last post: by
8 posts views Thread by situ | last post: by
1 post views Thread by dreamznatcher | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.