Title: RE: Design Issue - Quick response appreciated
Here's another thought:
Table A: id, key_field1, key_field2, key_field3, key_field4, data
Table B: id, key_field1, key_field2, key_field3, key_field4
An update trigger on table A inserts the previous value(s) of key_field1 - 4
Rao,
I'd add three columns.
Transaction_ID Not Null UK
Parent_Trans_IDNot Null FK to Transaction_ID
Original_Trans_ID Not Null FK to Transaction_ID
Transaction_ID would be the sequence-generated Surrogate Key. For the
original record Parent_Trans_ID and Original_Trans_ID would be equa
Chris,
Thanks for the suggestion. In our case, once a record is inserted, we
cannot update the record. If any column need to be changed, then, we insert
another record which would contain all the data of the columns of the
previous record + the data of the changed column (or columns).
I am toy
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.
Not if you have the appropriate indexes - typically on
each side of the connect by, and the start-with column
(if its different to the connect by ones)
hth
connor
--- "Rao, Maheswara" <[EMAIL PROTECTED]>
wrote: > Thanks Jay.
>
> Quetion:When we use start with/connect by clause,
> does Oracle d
Title: RE: Design Issue - Quick response appreciated
Rao,
The only way to tell is to autotrace or tkprof. I would strongly advise doing so because connect by is a performance killer. If you have to use it, be sure the query isn't doing anything unacceptable.
Lisa Koivu
Oracle Dat
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 inserte
Thanks Jay.
Quetion:When we use start with/connect by clause, does Oracle do full table
scan?
Thanks,
Rao
-Original Message-
Sent: Friday, September 21, 2001 5:25 PM
To: Multiple recipients of list ORACLE-L
Ouch!
I was assuming (for no good reason) that the field you used in your exa
Probably stating the obvious here, but if the account can change several
times, it may make sense to put the first prior id into all the records.
Suppose that when you create a new account, you populate prior id with the
value from the id field. Then your query gets nice and simple:
select * from
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 ins
Ouch!
I was assuming (for no good reason) that the field you used in your example
(ACCOUNT_ID) is the only one that would change.
In this case I agree with Christopher. Go with a generated key as your
primary key (your current primary key columns can be an alternate key), then
you can use that a
Sounds like the case of super trigger
"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
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:
Jay,
Good thought. Questions:
1. How many prior_ID's do I need to maintain? Logically, user could change
any of the columns in a primary key.
2. Say, a transaction udergoes 2 times changes i.e., first time, account_ID
is changed. Second time, Security_id is changed. This means, I inserted tw
Rao ,
Does it mean you want all the transaction in that
session .If yes then , Do you keep
session_id somewhere ?
-BigP
- Original Message -
From:
Rao, Maheswara
To: Multiple recipients of list ORACLE-L
Sent: Friday, September 21, 2001 9:35
AM
Subject: Design I
2:30 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Design Issue - Quick
response appreciated
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
list ORACLE-LSubject: RE:
Design Issue - Quick response appreciated
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 whe
One thought is to have an additional column called something like
'PRIOR_ID'. If the Account_id is "changed" (actually a new value inserted)
then the PRIOR_ID for the new row is set to the ACCOUNT_ID of the old row.
That way you can always trace back if the transaction used to have a
different ac
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. Spenc
19 matches
Mail list logo
