Re: State of data over the time

"Patrick J." <patrickj_at_kneipN-O-S-P-A-M.com> wrote in message news:<3b974576$1_2_at_news.vo.lu>...
> Hi,
>
> We are currently analyzing an application that will be based on an Oracle
> database. This database will contain about 40 tables, with many foreign keys
> between all these tables.
> An important requirement for this application is that it must be able to
> show the data as it was at any given date and time in the past, this
> concerning every data in the database, and therefore also the links between
> the data. The implementation of this functionality should not compromise the
> integrity of the database (most important point), nor result in too bad
> performances of the application.
> I am looking for the better way to fulfill this requirement, in terms of
> organization of data at the database level. Could you please give me some
> advices, based on your own experience?
>
> Thanks,
>
> Patrick J.

Patrick, I can think of two ways to write an application that keeps track of its history (or state).

The obvious method would be to place triggers on every table to record all modifications to a history table so your 40 primary tables would be backed up by 40 history tables. For current data you query the primary table and to build or display the history you query the history (or both).

The other method is to create a state key that is associated with every piece of data. This state column is added to every PK in the system and when the application selects the current part (or whatever) it retrieves the data for the part + max(state key). Then it uses this record to join to associated tables and the state key is normally invisible to the application. If you use this method you would want a package in front of every table to handle the logic to identify inserts that should be considered duplicates, to handle logical vs physical deletes etc....

• Mark D Powell --