Re: How to store temporal info?

From: Lennart Jonsson <>
Date: Mon, 03 Mar 2003 21:36:40 +0100
Message-ID: <b40ea5$1qdp76$>

On Mon, 03 Mar 2003 19:43:53 +0000, John Gilson wrote:

> "Lennart Jonsson" <> wrote in message
> news:b407bq$1qd9l2$
>> On Mon, 03 Mar 2003 14:18:49 +0000, John Gilson wrote:

> Two issues come to mind Lennart:
> 1. Will a resource ever stop being mapped to a resource category
> altogether? If so, then the concept of an end-time is still needed.
> For example, in the world of stocks, a stock might always be mapped to
> one and only one industry group at any given time, and this mapping
> can change over time, but companies do merge, are acquired, or go
> bankrupt, so at some point the stock might cease being mapped to any
> industry group because the stock itself ceases.

No, at any given time a resource will belong to exactly one resourcecategory. Resources can be withdrawn and as such, no longer give birth to new results, but they will continue to exist in the system

> 2. Consider the queries to get a resource R's category at time T when
> there's a start-time and when there's both a start-time and end-time.
> When there's just a start-time, the query might be
> SELECT RC1.*, :T
> FROM ResourceCategories
> WHERE resource = :R AND
> since = (SELECT MAX(since)
> FROM ResourceCategories
> WHERE resource = :R AND since <= :T)
> with both a start-time and end-time one can write
> SELECT *, :T
> FROM ResourceCategories
> WHERE resource = :R AND :T BETWEEN start_time AND end_time
> Similarly for queries that must calculate the duration of particular
> resource-category associations.

The main objections (for my scenario) that I have against start and stop is that:

1 if end_time is allowed to be null (indicating that we dont know when it will sease), one will have to deal with that in the queries. I.e. something similar to:

WHERE resource = :R AND :T > start_time AND (end_time is null or :T < end_time)

which is ok, but less elegant than "between" (and probably less efficient, I guess)

2 changing the category for a resource means adding a new row, but also that we will have to locate and update the "current row". With "the since" case, it is sufficient to add a new row

3. some kind of check has to be implemented to assure that we are not creating "overlaps" for a resource. In the "since" case, overlaps are impossible.

Since I am equipped with a new hammer, everything suddenly looks like nails :-)

> Hopefully some food for thought.

Very much so, thanx a lot


[...] Received on Mon Mar 03 2003 - 21:36:40 CET

Original text of this message