Re: How to store temporal info?
Date: Mon, 03 Mar 2003 19:43:53 GMT
"Lennart Jonsson" <lelle2_at_bonetmail.com> wrote in message
> 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
> *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:
- 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.
- 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
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
WHERE resource = :R AND :T BETWEEN start_time AND end_time
Similarly for queries that must calculate the duration of particular resource-category associations.
> Anyhow, thanx to both of you for your comments
Received on Mon Mar 03 2003 - 20:43:53 CET