Re: Design question-versioning using timestamps on rows

I'm not sure what all you gain by having the timestamp in the primary key and not as a non-key mandatory attribute. You toss normalization because now the primary key is no longer minimal. Also, as you have pointed out you probably will take some hits performance-wise as a result of the design. I think I would look for some object solution from Oracle 8 or write the joins explicitly with the key and the non-key version attribute. I would also be wary of modeling based upon commercial software. You need to remember that their databases are dedicated to that application. Any change is very controlled.

> I am looking for opinions from database designers on a versioning scheme.
>
> One of our database applications needs very flexible versioning. We
 need
> to be able to independently version parent and detail data and
 reconstruct
> sets of interrelated data as of a particular date. Some of the object
> databases do a very good job of this by versioning data and even the
 schema
> automatically. However, for reasons of robustness, compatibility with
> commercial tools, familiarity, etc. we wish to use Oracle.
>
> 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.
>
> I have seen such a design presented somewhere before and I think it may
> also be used in a commercial application (PeopleSoft) that I recently
 saw
> presented. The disadvantages I can see are that queries joining parent
 and
> child tables will not be able to use equi-joins.
> Also, referential integrity will have to be enforced with specially
 written
> triggers rather than declarative referential constraints. Ad hoc
 reporting
> would also be made more difficult, but views could be used to simplify
> reporting. They would be based on a table into which a pair of dates
> could be inserted which would control the range retrieved. ( Report
> transactions would insert dates but not commit them.)
>
> If those are the tradeoffs, the design seems worthwhile, but other
> considerations/comments would be very helpful. Thanks. Deborah
>
>
Received on Mon Jul 28 1997 - 00:00:00 CDT