Rao,
I'd add three columns.
Transaction_ID Not Null UK
Parent_Trans_ID Not Null FK to Transaction_ID
Original_Trans_ID Not Null FK to Transaction_ID
Transaction_ID would be the sequence-generated Surrogate Key. For the
original record Parent_Trans_ID and Original_Trans_ID would be equal to
Transaction_ID.
Each new version of the original record would have the original record's
Transaction_ID as its Original_Trans_ID, but would receive the
Transaction_ID of the record it came from as its Parent_Trans_ID. All
information about a new record's "heritage" would be contained in the record
from which it came - no pre-insert lookups required.
That way you could easily get all versions of a record (Original_Trans_ID =
xxx), while also giving yourself the flexibility of tracing the hierarchical
version history via Parent_Trans_ID.
Jack
--------------------------------
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
(512)327-9068
-----Original Message-----
Maheswara
Sent: Monday, September 24, 2001 2:46 PM
To: Multiple recipients of list ORACLE-L
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).
Thanks,
Rao
...
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack C. Applewhite
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
