Re: Temporal Data (long)

From: James A. Campbell <jcampbel_at_vitgsysa.telecom.com.au>
Date: 1996/08/09
Message-ID: <4ue6g9$j5q_at_cdn_news.telecom.com.au>#1/1

In article <1996Aug7.232510.15823_at_giant>,

   dwyche_at_giant.intranet.com (A little left of plumb) wrote:
>
> Temporal Data
>
>My apologies in advance for the lack of brevity. I really tried hard for
>clarity instead...
>
 <snip>
>
>where Kn are key fields and Dn are data fields, will be stored instead
>in the following structure:
>
> K1, K2, K3, KT, D1, D2, D3, D4, ...
>
>where the new KT field is a timestamp that indicates the date/time that
>the information in the row became effective.
>
>The difficulty with this approach is in the extraction of the 'correct'
>information for reporting purposes. Our users want current information 99%
>of the time.
 <snip>
>
>TIA, david

David,

Add an additional column, say, KE, which contains the timestamp when the row ceased to be valid (or will cease to be valid). If a row is still valid (ie it hasn't yet reached the time when it will not be valid), use 9999-12-31-23:59:59 (or whatever Oracle's highest timestamp is). Use an index based on KE in descending sequence (so the current row is first).

When a new row is inserted, retire the current row by setting its KE timestamp to the KT timestamp of the new row.

Note that this technique also allows future data to be inserted. ie

	row 1:  KT < today; KE > today
	row 2:  KT = KE(row 1); KE = 9999-12-31-23:59:59 

When KE(row 1) comes along row 2 will automatically become current.

As each row now contains all the data required to determine if it (the row) contains the desired data, all your subqueries disappear.

James Campbell
Telstra Corporation
jcampbel_at_vitgsysa.telecom.com.au

/* All opinions are mine alone etc */ Received on Fri Aug 09 1996 - 00:00:00 CEST