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