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
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
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
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
Have a table SHIPMENTS. When a CARGO moves to another TRUCK it becomes a
new SHIPMENT. SHIPMENTS has foreign keys to TRUCKS and CARGOES.
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
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
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
