RE: Design Issue - Quick response appreciated

From: Rao, Maheswara <Maheswara.Rao_at_Sungardp3.com>
Date: Fri, 21 Sep 2001 10:37:42 -0700
Message-ID: <F001.00394EB3.20010921103022@fatcity.com>

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:cspence_at_FuelSpot.com]Sent: Friday, September 21, 2001 1:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Design Issue - Quick response appreciated

<SPAN

  style="COLOR: navy; FONT-FAMILY: Arial; FONT-SIZE: 10pt">Generally<FONT 
  color=navy face=Arial size=2><SPAN 
  style="COLOR: navy; FONT-FAMILY: Arial; FONT-SIZE: 10pt"> it is bad practice

to use columns in the primary key, which change. They introduce many different problems.
<SPAN

style="COLOR: navy; FONT-FAMILY: Arial; FONT-SIZE: 10pt">

<SPAN

 style="COLOR: navy; FONT-SIZE: 12pt; mso-no-proof: yes">"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."
<SPAN

 style="COLOR: navy; FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 18pt; mso-no-proof: yes">Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275
<SPAN

 style="COLOR: navy; FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt; mso-no-proof: yes">Fuelspot
<SPAN

 style="COLOR: navy; FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt; mso-no-proof: yes">73 Princeton Street North, Chelmsford 01863

<SPAN

 style="FONT-FAMILY: Tahoma; FONT-SIZE: 10pt">-----Original Message-----From: Rao,
 Maheswara [mailto:Maheswara.Rao_at_Sungardp3.com] Sent: Friday, September 21, 2001 12:35 PMTo: Multiple recipients of
 list ORACLE-LSubject: 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 Received on Fri Sep 21 2001 - 12:37:42 CDT