RE: Design Issue - Quick response appreciated

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>Then use a surrogate key, i.e. sequence number.

<span style='font-size:

10.0pt;font-family:Arial;color:navy'> 

<span style='font-size:

10.0pt;font-family:Arial;color:navy'>Numbers that change are not candidates for key, doing so introduces problems in which you are having as well as others.

<span style='font-size:12.0pt;

color:navy;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='font-size:18.0pt;

font-family:"Comic Sans MS";color:navy;mso-no-proof:yes'>Christopher R. Spence<font color=navy>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Oracle DBA<span style='color:navy;
mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Phone: (978) 322-5744<span
style='color:navy;mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Fax:    (707) 885-2275<span
style='color:navy;mso-no-proof:yes'>

<span style='font-size:10.0pt;

font-family:"Comic Sans MS";color:navy;mso-no-proof:yes'>Fuelspot<font color=navy>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>73 Princeton Street<span style='color:navy; mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>North, Chelmsford 01863<span
style='color:navy;mso-no-proof:yes'>
 

<span

style='font-size:10.0pt;font-family:Tahoma'>-----Original Message----- From: Rao, Maheswara
[mailto:Maheswara.Rao_at_Sungardp3.com]
Sent<span
style='font-weight:bold'>: Friday, September 21, 2001 2:30 PM To: Multiple recipients of list
ORACLE-L
Subject: RE: Design Issue - Quick
response appreciated

<span

style='font-size:12.0pt'> 

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>In our case, we do not have any other columns --- 1. which could identify uniquely a record and yet do not undergo a change.

<span

style='font-size:12.0pt'> 

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Thanks,

<span

style='font-size:12.0pt'> 

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Rao

<p class=MsoNormal style='mso-margin-top-alt:0in;margin-right:0in;margin-bottom:
12.0pt;margin-left:.5in'><span style='font-size:10.0pt; font-family:Tahoma'>-----Original Message----- From: Christopher Spence
[mailto:cspence_at_FuelSpot.com]
Sent: Friday, September 21, 2001
1:35 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Design Issue - Quick
response appreciated

<span

style='font-size:10.0pt;font-family:Arial;color:navy'>Generally it is bad practice to use columns in the primary key, which change.<span style='mso-spacerun:yes'>  They introduce many different problems.

<span

style='font-size:10.0pt;font-family:Arial;color:navy'> 

<span

style='font-size:12.0pt;color:navy;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='font-size:18.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Christopher R. Spence<span
style='color:navy;mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Oracle DBA<span style='color:navy;
mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Phone: (978) 322-5744<span
style='color:navy;mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Fax:    (707) 885-2275<span
style='color:navy;mso-no-proof:yes'>

<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>Fuelspot<span style='color:navy;mso-no-proof: yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>73 Princeton Street<span style='color:navy; mso-no-proof:yes'>
<span

style='font-size:10.0pt;font-family:"Comic Sans MS";color:navy;mso-no-proof: yes'>North, Chelmsford 01863<span
style='color:navy;mso-no-proof:yes'>
 

<span

style='font-size:10.0pt;font-family:Tahoma'>-----Original Message----- From: Rao, Maheswara
[mailto:Maheswara.Rao_at_Sungardp3.com]
Sent: Friday, September 21, 2001
12:35 PM
To: Multiple recipients of list
ORACLE-L
Subject: Design Issue - Quick
response appreciated

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>List,

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>OLTP
application with 24x7 requirement. 300,000 records per day are inserted into the transaction table. Environment: Solari 7. Oracle 817.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>The
transaction table layout.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>Security
ID 

<font size=2 color=blue

face=Arial>Account
ID

<font size=2 color=blue

face=Arial>Account
Type

<font size=2 color=blue

face=Arial>Trade
Date

<font size=2 color=blue

face=Arial>And other
columns in this table.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>In the
above table, the primary key is -- Security ID + Account ID + Account Type + Trade Date

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>There
are many to one relationships built to other child tables from Transaction Table

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>Scenario:

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>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.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>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.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>I tried
with the idea of sequence number generation but it was failing.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>Any
ideas or suggestions are much appreciated.

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>Thanks,

<span

style='font-size:12.0pt'> 

<font size=2 color=blue

face=Arial>Rao

<font size=2 color=blue

face=Arial>Maheswara
Rao,

<font size=2 color=blue

face=Arial>Oracle
DBA
<font size=2 color=blue

face=Arial>SunGard
Securities
  Received on Fri Sep 21 2001 - 12:58:40 CDT