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

Search Discussions

  • Kevin Toepke at Nov 6, 2003 at 4:54 pm
    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 old one (setting the active_flag to false)

    Kevin

    -----Original Message-----
    Sent: Thursday, November 06, 2003 9:45 AM
    To: Multiple recipients of list ORACLE-L

    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:
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Kevin Toepke
    INET: ktoepke_at_rlcarriers.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).
  • Whittle Jerome Contr NCI at Nov 6, 2003 at 5:04 pm
    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).
  • Todd Boss at Nov 6, 2003 at 5:09 pm
    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 problem of inconsistent handling of records. Where some
    programmers don't know about this "deleted" flag and just go ahead
    and update the records directly as you normally would.

    Lets go back to the original question. Why does doing a simple
    foreign key update coause "contention?" If you have an index on
    your FK column in the child table, the update on the child table
    can use the index to find the PK record quickly, and the update
    goes on as planned. Where's the contention?

    Todd
    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 old one (setting the active_flag to false)

    Kevin

    -----Original Message-----
    Sent: Thursday, November 06, 2003 9:45 AM
    To: Multiple recipients of list ORACLE-L


    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: ryan_oracle_at_cox.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Kevin Toepke
    INET: ktoepke_at_rlcarriers.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Todd Boss
    INET: boss_at_i-sphere.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).
  • AK at Nov 6, 2003 at 5:19 pm
    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
    truck_id
    load
    date_delivered
    blah
    blah
    from
    to

    This takes care of split cargo ( consigment ) .

    -Ak

    Original Message -----
    To: "Multiple recipients of list ORACLE-L"
    Sent: Thursday, November 06, 2003 6:44 AM
    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: ryan_oracle_at_cox.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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: AK
    INET: oramagic_at_hotmail.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).
  • Thomas Day at Nov 6, 2003 at 5:44 pm
    Have a table SHIPMENTS. When a CARGO moves to another TRUCK it becomes a
    new SHIPMENT. SHIPMENTS has foreign keys to TRUCKS and CARGOES.

    To: Multiple recipients of list ORACLE-L
    Sent by: cc:
    ml-errors Subject: datamodelling question: updating foreign keys

    11/06/2003 09:44
    AM
    Please respond
    to ORACLE-L

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

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: Thomas Day
    INET: tday6_at_csc.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).
  • Jared.Still_at_radisys.com at Nov 6, 2003 at 6:24 pm
    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 of time on it, hope it
    works
    for you, answer.

    As you said though, the current process is highly suspect.

    Jared

    Sent by: ml-errors_at_fatcity.com
    11/06/2003 06:44 AM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: datamodelling question: updating foreign keys

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

    --
    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).
  • Jared.Still_at_radisys.com at Nov 6, 2003 at 6:29 pm
    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"
    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
    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 6, '03 at 2:44p
activeNov 6, '03 at 6:29p
posts8
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase