RE: Design Issue - Quick response appreciated

Here's another thought:
Table A: id, key_field1, key_field2, key_field3, key_field4, data

Table B: id, key_field1, key_field2, key_field3, key_field4

An update trigger on table A inserts the previous value(s) of key_field1 - 4 into table B. Only the current value(s) for key_field1 - 4 are in table A.

-----Original Message-----
From: Rao, Maheswara [mailto:Maheswara.Rao_at_Sungardp3.com] Sent: lundi, 24. septembre 2001 12:46
To: Multiple recipients of list ORACLE-L Subject: RE: Design Issue - Quick response appreciated

Chris,

Thanks for the suggestion.  In our case, once a record is inserted, we cannot update the record.  If any column need to be changed, then, we insert another record which would contain all the data of the columns of the previous record + the data of the changed column (or columns).

I am toying with the following idea.  Please point out if there are any probs with this.

1. I would create a surrogate key whenever a record is inserted and then insert this record in the transaction table with generation number 1 (please see item # 3 below).
2. I would maintain a separate table - say - KEY TABLE with the surrogate key + all the keys that uniquely identify the record.
3. I would also maintain a table - say - GENERATION TABLE. The columns in this would be - surrogate key + generation number.
4. Whenever, a record is being inserted, I would check key table. If no record with the keys are present, then, I would insert a record in the key table + insert one record in GENERATION table; in this table, generation number would be 1 for this record.
5. If a user tries insert a record which is already existing in the KEY TABLE, then, I would update the generation number column in GENERATION table.

In the above way, whenever, I want to get all the previous records, I would go to GENERATION Table and then get the generation number for that surrogate key.  (I would be getting the maximum generation number because I always updating this column with the last generation number).  Once, I know the last generation number, then, it is a question pulling all the records with the surrogation key + (all the generation numbers). Received on Mon Sep 24 2001 - 21:26:16 CDT