Re: Database design: Temporal databases

On Wed, 2008-06-18 at 14:05 +0200, David wrote:
> How well do temporal databases 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? Does this
> increase app complexity and increase server load a lot?

I think PostgreSQL provides the best tools for this, although I'd like
to make them even better.

As Richard pointed out, I have implemented a "period" data type here:
http://pgfoundry.org/projects/temporal

The things that need to be added, in my opinion, are:

(1) Temporal keys, that is, an index that can implement a non-
overlapping constraint. Right now we only support the UNIQUE constraint,
but non-overlapping is necessary for real temporal keys using a period
data type.

(2) Temporal foreign keys

(3) Temporal joins

I am going to make an attempt to accomplish #1, but I am still in the
planning stages.

As for application complexity, the fact that PostgreSQL keeps historical
data should be transparent to the application entirely, unless that
applications cares to look at the historical data.

I am trying to make performance reasonable by providing the period data
type (which is indexable).

Regards,
Jeff Davis