Yes, that's what I (hope) I would have thought of with a couple more
An associative or 'bridging' entity.
*sigh* don't get to do real DM anymore, or at least, rarely.
"Whittle Jerome Contr NCI"
Sent by: ml-errors_at_fatcity.com
11/06/2003 09:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
Subject: RE: datamodelling question: updating foreign keys
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
NCI Information Systems Inc.
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
Please see the official ORACLE-L FAQ: http://www.orafaq.net
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: ListGuru_at_fatcity.com (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).