How to store temporal info?

I have this situation which I am hoping to get some reflections on (very simplified model)

create table person (

        personid integer not null primary key,
        name char(20) not null,

create table resourcecategory (

        categoryid integer not null primary key,
        name char(20) not null,

create table resource (

        resourceid integer not null primary key,
        name char(20) not null,
        category integer,
        foreign key (category) references resourcecategory

Now, a person that uses a resource creates a result. The problem is that resources may belong to different categories as time goes bye. So first alternative is to store both resource and category in result. To prevent someone from faking the category a trigger or something similar is used

create table result1 (

        resultid integer not null primary key,
        resource integer not null,
        person integer not null,
        category integer not null,
	resulttime timestamp not null,
	score integer not null,
        foreign key (resource) references resource,
        foreign key (actorid) references person,
        foreign key (category) references resourcecategory,
	check (score between 0 and 100)

create trigger fix_res_cat
no cascade before insert on result
referencing new as r
for each row mode db2sql

        set r.category = (select categoryid from resource
                          where resourceid = r.resourceid)

The other alternative is to not store category in result, and instead keep track on how resources switch category over time (hence category is removed from resource).

create table result2 (

        resultid integer not null primary key,
        resource integer not null,
        person integer not null,
	resulttime timestamp not null,
	score integer not null,
        foreign key (resource) references resource,
        foreign key (actorid) references person,
	check (score between 0 and 100)

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)

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?

/Lennart Received on Sun Mar 02 2003 - 19:34:18 CET