Design question-versioning using timestamps on rows

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 Wed Jul 23 1997 - 00:00:00 CDT