RE: Design Issue - Quick response appreciated

From: Jack C. Applewhite <japplewhite_at_inetprofit.com>
Date: Mon, 24 Sep 2001 12:28:27 -0700
Message-ID: <F001.00396D21.20010924124530@fatcity.com>

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
japplewhite_at_inetprofit.com
(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.

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).
I would maintain a separate table - say - KEY TABLE with the surrogate key + all the keys that uniquely identify the record.
I would also maintain a table - say - GENERATION TABLE. The columns in this would be - surrogate key + generation number.
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.
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: japplewhite_at_inetprofit.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Sep 24 2001 - 14:28:27 CDT