Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: How to store temporal info?

Re: How to store temporal info?

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Mon, 03 Mar 2003 04:47:04 GMT
Message-Id: <slrnb65oes.rd4.ajglist@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 Windows
Received on Sun Mar 02 2003 - 22:47:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US