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:[EMAIL PROTECTED]]
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).
