Re: How to store temporal info?

"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:
>
> [...]
> >
> > I would second the use of the latest-representable date, instead of NULL, to
> > represent a valid time that's currently valid.
> >
> > The Snodgrass book is quite good and complete. You might also be interested
> > in the latest by Date and Darwen, "Temporal Data and the Relational Model",
> > which you can find out more about at www.thethirdmanifesto.com.
> >
>
> They had a nice summary on the site (Temporal Data and the Relational
> Model, Hugh Darwen's summary of the book). I only had a glimpse at it, but
> that alone gave me ideas I think I will ba able to use. To summarise,
> A Resource belongs to exactly one ResourceCategory at a time. Hence the
> relation:
>
> *Resource, *ResourceCategory, *Since
>
> will serve my needs. The problem is then reduced to finding the max(since)
> <= resulttime for a given resource. Besides from not having to deal with
> nulls (or infinite date) in todate, it also eliminates the need of
> "checking for overlaps" at the time of insert

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

Hopefully some food for thought.

Regards,
jag

> Anyhow, thanx to both of you for your comments
>
> /Lennart
>
> [...]
Received on Mon Mar 03 2003 - 13:43:53 CST