Rao,
I had a similiar requirement for a past project, we added a STATUS column to
the tables, the "A" is the current or active record, and "I" were the
inactive records. Obviously, the software was coded to retrieve only active
records. Since we used surrogate keys this setup was not a problem. We
also had a problem with developers forgetting to check for active records,
which resulted in some early bugs, so we ended up creating views with status
= 'A' hardcoded.
2) So with the above in mind you can retrieve all the records based on the
four columns. You can tell which one is active or current based on the
STATUS column. Obviously, you can throw in some date and userid columns in
there. Obviously, you can keep as many "history" records as you want, there
easily identified.
HTH.
Chris
-----Original Message-----
Sent: Monday, September 24, 2001 2:30 PM
To: Multiple recipients of list ORACLE-L
Chris,
Thanks for the reply.
This application is a securities processing system. Once a record is
inserted into the table, then, as per business requirement, no changes could
be made to an already inserted record due to audit regulations. Hence, if
any change is required for an already inserted record, then the user inserts
another record with a change.
2. In my case, a record is identified uniquely by a combination of 4
columns. After a record is inserted, a user could insert another record
with a changed values for any one of the 4 columns. Now, the question is
how many prior id's do I need to maitain? and how could I retrieve all the
records pertaining to a original transaction?
Thanks,
Rao
-----Original Message-----
Sent: Saturday, September 22, 2001 1:05 AM
To: Multiple recipients of list ORACLE-L
Rao,
1) Since the primary key value(s) can change then it cannot be the primary
key. You have to use a surrogate key. That solves that design rule.
2) When the user changes HP to IBM, why are you inserting a new record? Why
cant you just update HP to IBM? If, for some reason, you have to insert a
record, say the other values in the record change, then I would create a
"PRIOR_ID" field, like someone else suggested, which would point to the "HP"
record's ID. This field sould also be foreign keyed to the ID column.
Chris
"May Oracle be with you...always"
-----Original Message-----
Sent: Friday, September 21, 2001 4:58 PM
To: Multiple recipients of list ORACLE-L
Christopher,
Thanks for the reply.
Surrogate key solves the problem of having a record with a unique identifier
for a record. But I still have the following problems.
1. How do I link the previous records whenever a query is issued by the
user?
2. Please see my requirement below:
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.
How do I do this?
Thanks,
Rao
-----Original Message-----
Sent: Friday, September 21, 2001 3:17 PM
To: Multiple recipients of list ORACLE-L
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-----
Sent: Friday, September 21, 2001 2:30 PM
To: Multiple recipients of list ORACLE-L
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-----
Sent: Friday, September 21, 2001 1:35 PM
To: Multiple recipients of list ORACLE-L
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-----
Sent: Friday, September 21, 2001 12:35 PM
To: Multiple recipients of list ORACLE-L
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rao, Maheswara
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rao, Maheswara
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
