Thread: Database design: Temporal databases
Hi list. Some background information on the subject: 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 (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? 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? David.
On Wed, Jun 18, 2008 at 5:05 AM, David <wizzardx@gmail.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
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