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-----
> From: ryan_oracle_at_cox.net [SMTP:ryan_oracle_at_cox.net]
>
> 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: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

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).