RE: Design Issue - Quick response appreciated
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 into table B. Only the current value(s) for key_field1 - 4 are in table A. -Original Message- From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] Sent: lundi, 24. septembre 2001 12:46 To: Multiple recipients of list ORACLE-L Subject: RE: Design Issue - Quick response appreciated 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 toying with the following idea. Please point out if there are any probs with this. 1. I would create a surrogate key whenever a record is inserted and then insert this record in the transaction table with generation number 1 (please see item # 3 below). 2. I would maintain a separate table - say - KEY TABLE with the surrogate key + all the keys that uniquely identify the record. 3. I would also maintain a table - say - GENERATION TABLE. The columns in this would be - surrogate key + generation number. 4. Whenever, a record is being inserted, I would check key table. If no record with the keys are present, then, I would insert a record in the key table + insert one record in GENERATION table; in this table, generation number would be 1 for this record. 5. If a user tries insert a record which is already existing in the KEY TABLE, then, I would update the generation number column in GENERATION table. In the above way, whenever, I want to get all the previous records, I would go to GENERATION Table and then get the generation number for that surrogate key. (I would be getting the maximum generation number because I always updating this column with the last generation number). Once, I know the last generation number, then, it is a question pulling all the records with the surrogation key + (all the generation numbers).
RE: Design Issue - Quick response appreciated
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 equal to Transaction_ID. Each new version of the original record would have the original record's Transaction_ID as its Original_Trans_ID, but would receive the Transaction_ID of the record it came from as its Parent_Trans_ID. All information about a new record's "heritage" would be contained in the record from which it came - no pre-insert lookups required. That way you could easily get all versions of a record (Original_Trans_ID = xxx), while also giving yourself the flexibility of tracing the hierarchical version history via Parent_Trans_ID. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Maheswara Sent: Monday, September 24, 2001 2:46 PM To: Multiple recipients of list ORACLE-L 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 toying with the following idea. Please point out if there are any probs with this. 1. I would create a surrogate key whenever a record is inserted and then insert this record in the transaction table with generation number 1 (please see item # 3 below). 2. I would maintain a separate table - say - KEY TABLE with the surrogate key + all the keys that uniquely identify the record. 3. I would also maintain a table - say - GENERATION TABLE. The columns in this would be - surrogate key + generation number. 4. Whenever, a record is being inserted, I would check key table. If no record with the keys are present, then, I would insert a record in the key table + insert one record in GENERATION table; in this table, generation number would be 1 for this record. 5. If a user tries insert a record which is already existing in the KEY TABLE, then, I would update the generation number column in GENERATION table. In the above way, whenever, I want to get all the previous records, I would go to GENERATION Table and then get the generation number for that surrogate key. (I would be getting the maximum generation number because I always updating this column with the last generation number). Once, I know the last generation number, then, it is a question pulling all the records with the surrogation key + (all the generation numbers). Thanks, Rao ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite 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).
RE: Design Issue - Quick response appreciated
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 toying with the following idea. Please point out if there are any probs with this. 1. I would create a surrogate key whenever a record is inserted and then insert this record in the transaction table with generation number 1 (please see item # 3 below). 2. I would maintain a separate table - say - KEY TABLE with the surrogate key + all the keys that uniquely identify the record. 3. I would also maintain a table - say - GENERATION TABLE. The columns in this would be - surrogate key + generation number. 4. Whenever, a record is being inserted, I would check key table. If no record with the keys are present, then, I would insert a record in the key table + insert one record in GENERATION table; in this table, generation number would be 1 for this record. 5. If a user tries insert a record which is already existing in the KEY TABLE, then, I would update the generation number column in GENERATION table. In the above way, whenever, I want to get all the previous records, I would go to GENERATION Table and then get the generation number for that surrogate key. (I would be getting the maximum generation number because I always updating this column with the last generation number). Once, I know the last generation number, then, it is a question pulling all the records with the surrogation key + (all the generation numbers). Thanks, Rao -Original Message- Sent: Monday, September 24, 2001 3:01 PM To: Multiple recipients of list ORACLE-L 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
RE: Design Issue - Quick response appreciated
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 l
RE: Design Issue - Quick response appreciated
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 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 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 as the only prior_id column. > > With regards to the second question you can then go > back as far as you like > using the START WITH/CONNECT BY clauses in your > SELECT statement. > > Jay Miller > > -Original Message- > Sent: Friday, September 21, 2001 3:45 PM > To: Multiple recipients of list ORACLE-L > > > 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 two > records into the transaction table pertaining to > original transaction. How > do I retrieve earlier three records? i.e., the > latest change in the > account_id=IBM. If the user is querying based on > this, he would get two > records. But he would not get the record where he > changed security_ID. (My > primary key = Security ID + Account ID + Account > Type + Trade Date). > > 3. How do manage and retrieve the records from the > child tables? > > Thanks, > > Rao > > > -Original Message- > Sent: Friday, September 21, 2001 2:26 PM > To: Multiple recipients of list ORACLE-L > > > 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 account. > > > Jay Miller > > -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: Miller, Jay > 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,
RE: Design Issue - Quick response appreciated
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 Database Administrator Fairfield Resorts, Inc. 954-935-4117 -Original Message- From: Rao, Maheswara [SMTP:[EMAIL PROTECTED]] Sent: Monday, September 24, 2001 2:24 PM To: Multiple recipients of list ORACLE-L Subject: RE: Design Issue - Quick response appreciated 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 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 as the only prior_id column. With regards to the second question you can then go back as far as you like using the START WITH/CONNECT BY clauses in your SELECT statement. Jay Miller -Original Message- Sent: Friday, September 21, 2001 3:45 PM To: Multiple recipients of list ORACLE-L 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 two records into the transaction table pertaining to original transaction. How do I retrieve earlier three records? i.e., the latest change in the account_id=IBM. If the user is querying based on this, he would get two records. But he would not get the record where he changed security_ID. (My primary key = Security ID + Account ID + Account Type + Trade Date). 3. How do manage and retrieve the records from the child tables? Thanks, Rao -Original Message- Sent: Friday, September 21, 2001 2:26 PM To: Multiple recipients of list ORACLE-L 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 account. Jay Miller -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: Miller, Jay 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 PROTE
RE: Design Issue - Quick response appreciated
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
RE: Design Issue - Quick response appreciated
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 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 as the only prior_id column. With regards to the second question you can then go back as far as you like using the START WITH/CONNECT BY clauses in your SELECT statement. Jay Miller -Original Message- Sent: Friday, September 21, 2001 3:45 PM To: Multiple recipients of list ORACLE-L 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 two records into the transaction table pertaining to original transaction. How do I retrieve earlier three records? i.e., the latest change in the account_id=IBM. If the user is querying based on this, he would get two records. But he would not get the record where he changed security_ID. (My primary key = Security ID + Account ID + Account Type + Trade Date). 3. How do manage and retrieve the records from the child tables? Thanks, Rao -Original Message- Sent: Friday, September 21, 2001 2:26 PM To: Multiple recipients of list ORACLE-L 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 account. Jay Miller -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: Miller, Jay 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: Miller, Jay INET: [EMAIL PROTECTED] Fat City Netwo
RE: Design Issue - Quick response appreciated
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 transaction where prior_id = XXX; If you're going to do this, then prior id becomes a slightly confusing name for the field; something like group id may be clearer. HTH GS -Original Message- Sent: Saturday, 22 September 2001 06:05 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
RE: Design Issue - Quick response appreciated
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 ---
RE: Design Issue - Quick response appreciated
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 as the only prior_id column. With regards to the second question you can then go back as far as you like using the START WITH/CONNECT BY clauses in your SELECT statement. Jay Miller -Original Message- Sent: Friday, September 21, 2001 3:45 PM To: Multiple recipients of list ORACLE-L 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 two records into the transaction table pertaining to original transaction. How do I retrieve earlier three records? i.e., the latest change in the account_id=IBM. If the user is querying based on this, he would get two records. But he would not get the record where he changed security_ID. (My primary key = Security ID + Account ID + Account Type + Trade Date). 3. How do manage and retrieve the records from the child tables? Thanks, Rao -Original Message- Sent: Friday, September 21, 2001 2:26 PM To: Multiple recipients of list ORACLE-L 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 account. Jay Miller -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: Miller, Jay 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: Miller, Jay 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 thi
RE: Design Issue - Quick response appreciated
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, Chelmsford 01863 -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-
RE: Design Issue - Quick response appreciated
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).
RE: Design Issue - Quick response appreciated
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 two records into the transaction table pertaining to original transaction. How do I retrieve earlier three records? i.e., the latest change in the account_id=IBM. If the user is querying based on this, he would get two records. But he would not get the record where he changed security_ID. (My primary key = Security ID + Account ID + Account Type + Trade Date). 3. How do manage and retrieve the records from the child tables? Thanks, Rao -Original Message- Sent: Friday, September 21, 2001 2:26 PM To: Multiple recipients of list ORACLE-L 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 account. Jay Miller -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: Miller, Jay 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).
Re: Design Issue - Quick response appreciated
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 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
RE: Design Issue - Quick response appreciated
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- From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 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- From: Christopher Spence [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 1:35 PM To: Multiple recipients of list ORACLE-L Subject: 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 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- From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 12:35 PM To: Multiple recipients of list ORACLE-L Subject: 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
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-From: Christopher Spence [mailto:[EMAIL PROTECTED]]Sent: Friday, September 21, 2001 1:35 PMTo: Multiple recipients of 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 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-From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] 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
RE: Design Issue - Quick response appreciated
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 account. Jay Miller -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: Miller, Jay 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).
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 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- From: Rao, Maheswara [mailto:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 12:35 PM To: Multiple recipients of list ORACLE-L Subject: 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
