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
|
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?
