Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
11-06-2011, 12:27 AM #1
- Join Date
- Aug 2010
- Thanked 2 Times in 2 Posts
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
11-06-2011, 03:19 AM #2
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.
Notice the NULL in those references (except productID).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) );
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.