Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts

    many-to-many/organization question

    I've got an series of tables for continents/continent_regions/countries/states.
    They're all joined to each other with id's.

    I want to set up a "junction" table to list where products are located. But I have different levels. Some products are continent-wide. I don't want to have to enter every single country. Do I have to actually set up a junction table to each of the others? A junction to continent. A junction to region? A junction to state?

    Or is there a way to do this in one junction table that I'm not seeing? A junction with a column for each seems like it would be redundant.

    I'd have a row that listed: product id, Europe id, england id, Somerset id
    And a row that also listed: product id, Europe id, NULL, NULL

    Any thoughts?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,733
    Thanks
    80
    Thanked 4,516 Times in 4,480 Posts
    Believe it or not, the accepted answer is to use one field in your "junction" table per level.

    This has the advantage that you can enforce referential integrity.

    e.g.,
    Code:
    CREATE TABLE productLocation(
        productID REFERENCES products(productID) ON DELETE CASCADE,
        continentID NULL REFERENCES continents(continentID),
        countryID NULL REFERENCES countries(countryid),
        stateID NULL REFERENCES states(stateid)
       );
    Notice the NULL in those references (except productID).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •