Re: Design question-versioning using timestamps on rows

ADP / CSG wrote:
>
> We are considering a design wherein every row has a timestamp that is added
> to what would normally be its primary key. The column(s) in the child
> table that would normally form a foreign key do not contain the timestamp.
> Joins of the two tables are achieved by looking for equivalency on the
> "content" columns and a range check on the timestamps.

One of the simpler date architectures for a RDBMS is to add a START_DATE and END_DATE attribute to the entity. This could be added to the detail and/or the master as your need required. The START_DATE specifies when the row "takes effect" and END_DATE (can you guess?) specifies when the row is no longer in effect. The START_DATE cannot be null and a null in the END_DATE means, in effect, "from now on". The select statement for this is setup is kinda hairy:

  select ...
  from master m, detail d
  where <normal equi-join criteria> and

        m.start_date <= &search_date and
        nvl( m.end_date, &search_date + 1 ) > &search_date and
        d.start_date <= &search_date and
        nvl( d.end_date, &search_date + 1 ) > &search_date ;

This returns the master/details that were in effect on the date specified by search_date.

This is a simple one. I am current working on a project where the date architecture consists of four date fields and a status field! It's a tribute to Oracle's efficiency that we get responses zip quick.

Of course, part of the architecture is the rules under which it operates -- whether or not the date range of otherwise identical rows may overlap, or form "gaps", etc.

Hope this helps.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"