Hello and welcome to our community! Is this your first visit?
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
    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. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 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.

    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).
    Be yourself. No one else is as qualified.


Posting Permissions

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