| temperal data [message #2893] |
Tue, 20 August 2002 03:37  |
Gus
Messages: 18
Registered: December 2000
|
Junior Member |
|
|
hi
I am messing around with temperal db, every table
in the db has a "date_from" and "date_to" column.
date_from contains the date the record was created
and date_to is max date allowed in oracle. records
are never deleted from tables and updates modify
exisiting records "date_to" and "date_from" and inserts a new record. so for any particular record in a table will look like this.
** original record **
id date_from date_to val
------ ------------- -------------- -------------
001 01/01/2002 13:54 31/12/9999 59:59 blahblahblah
** updated record **
id date_from date_to val
------ ------------- -------------- -------------
001 01/01/2002 13:54 08/01/2002 11:53 blahblahblah
001 08/01/2002 11:54 31/12/9999 59:59 yayayayayaya
to select the "current" record you query has where
clause : where sysdate between date_from and date_to
now my problem:
this is nice concept cause you have history af all transactions. but when you start writing queries
it gets a bit messy to cator for temperal data.
can I make some sort of trigger thingy that when
quering the table you can leave out the date part
and the trigger thingy will automactically assume you
are operating on the "current" record unless otherwise
specified.
thanks anybody
|
|
|
|
| Re: temporal data [message #2903 is a reply to message #2893] |
Tue, 20 August 2002 09:39  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
I would suggest creating views for each of your tables. These views would have the WHERE date clause already embedded, so a query against the view automatically locates the "current" row. If you needed to bypass the filter, then you would just query the base table directly.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
