Re: How to store temporal info?
Date: Mon, 03 Mar 2003 04:47:04 GMT
Message-Id: <slrnb65oes.rd4.ajglist_at_izzy.net>
In article <b3tip2$1ohils$1_at_ID-167942.news.dfncis.de>, Lennart Jonsson wrote:
> create table resourcecategory_history (
> category integer not null,
> resource integer not null,
> start timestamp not null,
> stop timestamp,
> primary key (category, resource, start)
> foreign key (category) references resourcecategory
> foreign key (resource) references resource
> )
> The second alternative is appealing because it better reflects the actual
> situation. It is however much more difficult to ask simple questions like
>
> select category, count(*) from result group by category
>
> since temporal info has to be dealt with
>
> select rch.category, count(*)
> from result r, resultcategory_history rch
> where r.resourceid = rch.resource and
> rch.start >= r.resulttime and
> (rch.stop is null or rch.stop < r.resulttime)
> group by rch.category
>
> besides one will have to add some checks (possibly via triggers) that
> overlapping does not occur
> Having to use triggers always kinda rings a bell that there is something
> fishy with the design. How is these designs normally done? Any thoughts,
> anyone?
I'm using the *_history desgin, with a slight change. Rather than use a null value for something not yet stopped, I use a the value 9999/12/31 00:00:00. That way your query becomes:
SELECT Rch.category, COUNT(*)
FROM Result, Resultcategory_History Rch
WHERE Result.resourceid = Rch.resource AND Result.resulttime BETWEEN Rch.start AND Rch.stop; GROUP BY Rch.category
Thers is a book by a fellow named Snodgrass that I am slowly working through, that talks about this concept, which I believe is called a valid-time table in his book.
Developing Time-Oriented Database Applications in SQL http://www.amazon.com/exec/obidos/ASIN/1558604367
-- Alan Gutierrez - ajglist_at_izzy.net http://khtml-win32.sourceforge.net/ - KHTML on WindowsReceived on Mon Mar 03 2003 - 05:47:04 CET