View Full Version : many-to-many/organization question

11-05-2011, 11:27 PM
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?

Old Pedant
11-06-2011, 02:19 AM
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).