By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,835 Members | 1,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,835 IT Pros & Developers. It's quick & easy.

Re: Temporal Databases (Database design questions)

P: n/a
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
Jun 27 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.