RE: datamodelling question: updating foreign keys
Yes. There is a better way to model that. Oh, you wanted a suggestion. How about including a LOADS table that has (at least) 3 colums truck_id cargo_id active_flag That way when cargo is moved to a different truck you add a new record to the LOADS table and update the old one (setting the active_flag to false) Kevin -Original Message- Sent: Thursday, November 06, 2003 9:45 AM To: Multiple recipients of list ORACLE-L I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Kevin Toepke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: datamodelling question: updating foreign keys
Title: RE: datamodelling question: updating foreign keys Ryan, You'll probably like my solution less, but it worked for me. You don't have a parent-child relationship as a Truck can have more than one Cargo and Cargo can be loaded on more than one Truck. Rather you have a many to many relationship. You need a bridging or linking table between Cargo and Trucks. Call it TRIPS and have foreign keys from both the TRUCKS and CARGO tables in it plus some date/time fields for loading and unloading. That way you can track what Cargo was on which Truck and when throughout the entire shipment. What you have now will show where the cargo is now, but you lose any back tracking if part of the cargo is lost for example. I once worked on a database of an automobile transport company and have been though some of these issues. Heaven help you if a cargo gets split between trucks! Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED] I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this?
Re: datamodelling question: updating foreign keys
I don't know if this is a better model at all. In fact, all this accomplishes is leaving behind tons of useless records. I'd only recommend this model if (for any reason) the trail of the truck history for this cargo. This way of marking records also leaves you open for the obvious future problem of inconsistent handling of records. Where some programmers don't know about this deleted flag and just go ahead and update the records directly as you normally would. Lets go back to the original question. Why does doing a simple foreign key update coause contention? If you have an index on your FK column in the child table, the update on the child table can use the index to find the PK record quickly, and the update goes on as planned. Where's the contention? Todd Yes. There is a better way to model that. Oh, you wanted a suggestion. How about including a LOADS table that has (at least) 3 colums truck_id cargo_id active_flag That way when cargo is moved to a different truck you add a new record to the LOADS table and update the old one (setting the active_flag to false) Kevin -Original Message- Sent: Thursday, November 06, 2003 9:45 AM To: Multiple recipients of list ORACLE-L I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Kevin Toepke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Todd Boss INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: datamodelling question: updating foreign keys
Well , How about cargo as table with primary key say cargo_id which unqiely identifies cargo and weight . Now you should have a cargo detail table that will have cargo_id , load , truck_id . So Cargo - cargo_id total_load date blah blah cargo_detail - cargo_id truck_id load date_delivered blah blah from to This takes care of split cargo ( consigment ) . -Ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 6:44 AM I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: datamodelling question: updating foreign keys
Have a table SHIPMENTS. When a CARGO moves to another TRUCK it becomes a new SHIPMENT. SHIPMENTS has foreign keys to TRUCKS and CARGOES. ryan_oracle @cox.netTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: ml-errorsSubject: datamodelling question: updating foreign keys 11/06/2003 09:44 AM Please respond to ORACLE-L I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Re: datamodelling question: updating foreign keys
i like that. you then have a table that lists 'available trucks' and you have a key constraint from the cargo_detail to the truck table. you can also archive when changes happen for history data. I like that one. From: AK [EMAIL PROTECTED] Date: 2003/11/06 Thu PM 12:19:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: datamodelling question: updating foreign keys Well , How about cargo as table with primary key say cargo_id which unqiely identifies cargo and weight . Now you should have a cargo detail table that will have cargo_id , load , truck_id . So Cargo - cargo_id total_load date blah blah cargo_detail - cargo_id truck_id load date_delivered blah blah from to This takes care of split cargo ( consigment ) . -Ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 6:44 AM I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: datamodelling question: updating foreign keys
Seems to me that the proper solution is to create a new record, and use a status indicator to determine where the cargo is currently. Updating the FK causes you to lose any history of where the cargo has been. ie. no cargo tracking possible. This is my off the cuff, didn't spend a great deal of time on it, hope it works for you, answer. As you said though, the current process is highly suspect. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 06:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:datamodelling question: updating foreign keys I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: datamodelling question: updating foreign keys
Yes, that's what I (hope) I would have thought of with a couple more minutes deliberation. An associative or 'bridging' entity. *sigh* don't get to do real DM anymore, or at least, rarely. Jared Whittle Jerome Contr NCI [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 09:04 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: datamodelling question: updating foreign keys Ryan, You'll probably like my solution less, but it worked for me. You don't have a parent-child relationship as a Truck can have more than one Cargo and Cargo can be loaded on more than one Truck. Rather you have a many to many relationship. You need a bridging or linking table between Cargo and Trucks. Call it TRIPS and have foreign keys from both the TRUCKS and CARGO tables in it plus some date/time fields for loading and unloading. That way you can track what Cargo was on which Truck and when throughout the entire shipment. What you have now will show where the cargo is now, but you lose any back tracking if part of the cargo is lost for example. I once worked on a database of an automobile transport company and have been though some of these issues. Heaven help you if a cargo gets split between trucks! Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- I remember seeing this question asked on another forum some time back. I dont like the solution the guy had and Im wondering how some of you might solve problem. Im giving a low level generic example. Lets say you have a parent-child relationship. The parent table is 'TRUCKS' and the child table is 'CARGO'. The foreign key to CARGO tells which truck the cargo is loaded on. When the cargo is moved to another truck, the foreign key is updated. I dont like this approach. it causes contention. what is a better way to design this?
