RE: Design Issue - Quick response appreciated

[Christopher Spence]

Then use a surrogate key, i.e. sequence number.   Numbers that change are not candidates for key, doing so introduces problems in which you are having as well as others. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:    (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863   -----Original Message----- From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 2:30 PM To: Multiple recipients of list ORACLE-L Subject: RE: Design Issue - Quick response appreciated   In our case, we do not have any other columns --- 1. which could identify uniquely a record and yet do not undergo a change.   Thanks,   Rao -----Original Message----- From: Christopher Spence [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 1:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: Design Issue - Quick response appreciated Generally it is bad practice to use columns in the primary key, which change.  They introduce many different problems.   "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:    (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863   -----Original Message----- From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 12:35 PM To: Multiple recipients of list ORACLE-L Subject: Design Issue - Quick response appreciated   List,   OLTP application with 24x7 requirement. 300,000 records per day are inserted into the transaction table. Environment: Solari 7. Oracle 817.   The transaction table layout.   Security ID  Account ID Account Type Trade Date And other columns in this table.   In the above table, the primary key is -- Security ID + Account ID + Account Type + Trade Date   There are many to one relationships built to other child tables from Transaction Table   Scenario:   User inserts a record into transaction table.  In the first record, Account ID value is "HP" and he might insert a record into the child table (Or this transaction may not insert a record into a child table). After some time, the user queries the original record with the primary key and then changes the value in the column - Account ID to  "IBM".  Now, the original transaction record is NOT UPDATED.  A record IS INSERTED with the new values.  Also, he might or might not insert a record into a child table with this new values of primary key.   Now the user would query the transaction table with Account ID = IBM.  But, the user wants to get all the previous records also; in this case, he want to see the record with Account ID = "HP" also. Also, he want to see the related records from the child tables.   I tried with the idea of sequence number generation but it was failing.   Any ideas or suggestions are much appreciated.   Thanks,   Rao Maheswara Rao, Oracle DBA SunGard Securities