Re: How to store temporal info?

Thanx Alan, I knew I've seen references to "howto do temporal things in sql", before in this group, I just couldnt find them. I'll keep this one in a safe place ;-)

/Lennart

"Alan Gutierrez" <ajglist_at_izzy.net> wrote in message news: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 Windows
Received on Mon Mar 03 2003 - 10:33:21 CET