Re: Temporal Data (long)

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>
>
> Any ideas, thoughts, pointers, etc would be greatly appreciated.
>
> TIA, david

Just an idea:

Add another column to those tables called ACTIVE that has a 'Y' value for the row with the highest date, and a 'N' value for 'history' rows.

The benefit is that queries are natural (on the active column) and can be indexed. The downside is that all deletes and inserts have to maintain the active column, however this can be done with a trigger:

create or replace trigger SetActive
before delete or update or insert on tab for each row
begin

• i assume an inserted row always
• has a 'higher' KT if inserting then update tab set active = 'N' where k1 = :new.k1 and k2 = :new.k2 and active = 'Y'; :new.active = 'Y'; end if;
• when deleting the active row
• make another active if deleting then if :old.active = 'Y' then update tab t1 set active = 'Y' where k1 = :old.k1 and k2 = :old.k2 and kt < :old.kt and kt = (select max(kt) from tab t2 where t2.k1 = t1.k1 and t2.k2 = t1.k2 and t2.kt < t1.kt); end if; -- :old.active = 'Y' end if; -- if deleting
  • i assume kt can not be updated if updating then null; end if; end; I rolled this trigger without oracle (manuals) so there may be some errors in it.

Hope this helps

-- 
Gert Rijs
  gem_at_wirehub.net      (at home)
  gert.rijs_at_corp.ah.nl (at work)