Re: Database design: Temporal databases

On Wed, Jun 18, 2008 at 5:05 AM, David <wizzardx(at)gmail(dot)com> wrote:

> 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).

Well depending on what kind of temporal behavior you are modeling,
there may be cases where you will update a temporal segment. That is
why the bi-temporal tables were introduced. They track the changes
that are many to another tables temporal segment.

> How well do temporal databases work?

They work really well. But the are alot of work to create and because
of this changing their structure is also alot of work.

> Do RDBMS (ie 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?

Postgresql contains most of the features needed to make a temporal
database. However, the hard part of temporal database is developing
your own referential integrity constraints that would need to exists
between related entity time line tables.

> Does this
> increase app complexity and increase server load a lot?

I would expect that it would add abit, since you need to remember that
you not really deleteing a record or updating. you are only altering
and adding timeline segments.

> Are there Python libraries which simplify this? (eg: add-ons for
> Elixir or SQLAlchemy).

Not to sure.

> Or should apps all implement their own 'temporal data access' module,
> which transparently uses the current date & time until queried for
> historical data?

Currently there is a project to add temporal functionality to
postgresql. Currently is doesn't add any referential integrity. It
only adds special temporal data types and functions for evaluating
temporal comparisons.

http://pgfoundry.org/projects/temporal/

Although there is a desire to add RI to PG. However, this
functionality would need to be directly added to the core of
postgresql. It will probably be a long while before this happens.

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug