| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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" <Jerome.Whittle_at_scott.af.mil>
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 <ORACLE-L_at_fatcity.com>
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.
jerome.whittle_at_scott.af.mil
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?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Jared.Still_at_radisys.com
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). Received on Thu Nov 06 2003 - 12:29:26 CST
 |
 |