Re: How to store temporal info?

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

> "Lennart Jonsson" <lelle2_at_bonetmail.com> wrote in message
> news:b407bq$1qd9l2$1_at_ID-167942.news.dfncis.de...
>> 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

/Lennart

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