RE: Design Issue - Quick response appreciated

[Christopher Spence]

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