Re: Design question-versioning using timestamps on rows

dluik_at_sr.csg.com (ADP / CSG ) wrote:
>
>I am looking for opinions from database designers on a versioning sche
>me.
>
>One of our database applications needs very flexible versioning. We
>need
>to be able to independently version parent and detail data and reconst
>ruct
>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 s
>chema
>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 timest
>amp.
>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 ma
>y
>also be used in a commercial application (PeopleSoft) that I recently
> saw
>presented. The disadvantages I can see are that queries joining paren
>t and
>child tables will not be able to use equi-joins.
>Also, referential integrity will have to be enforced with specially wr
>itten
>triggers rather than declarative referential constraints. Ad hoc repo
>rting
>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
>

I am familiar with the schema from PS you describe. If your versioning is very dynamic for each child and is not a controllable event, then the system PS uses is quite flexible. Of course, you already recognize some limitations and workarounds.

On the other hand, if your versions are more controlled(e.g. once a month, a software release, one a day, etc) and many updates are related to a particular _release_ or _version_ then a simple table(named Version) with it's primary key as a part of master/child table composit key(s) might be an alternative to above. This would require some queries to select a _version_ based on date(s) ranges rather than dates alone.

-- 
*****************************************************************************
* K. E. Fein                      "To thy own opinions be true...           *
* kef_at_tucsonpo.jewelway.com         ...those expressed herein are but mine" *
*****************************************************************************