| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Design Issue - Quick response appreciated
<span
style='font-size:10.0pt;font-family:Arial;color:navy'>Generally<font
size=2 color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'> it is bad practice to use columns in the primary key, which
change. 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<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 12:35 PM
To: Multiple recipients of list
ORACLE-L
Subject: Design Issue - Quick
response appreciated
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>List,
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>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'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>The transaction table
layout.
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Security ID
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Account ID
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Account Type
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Trade Date
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>And other columns in this
table.
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>In the above table, the
primary key is -- Security ID + Account ID + Account Type + Trade Date
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>There
are many to one relationships built to other child tables
from Transaction Table
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Scenario:
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>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'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>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'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>I tried with the idea of
sequence number generation but it was failing.
<span
style='font-size:12.0pt'>
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Any ideas or
suggestions are much appreciated.
<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
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Maheswara Rao,
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>Oracle DBA
<span
style='font-size:10.0pt;font-family:Arial;color:blue'>SunGard Securities
Received on Fri Sep 21 2001 - 11:25:42 CDT
 |
 |