Bear with me as I'm very new with databases. This may be a somewhat simple question.

I'm building a website of items that can be found in multiple places around the globe.

I've broken down the various levels into individual tables, with foreign keys: table1=continents, table2=subcontinents, table3=countries and table4=districts/states/etc.

Where I run into trouble is with the Item table. Some items will be in several dozen countries on several different continents. I don't want a hundred fields just so I can show multiple locations. One foreign key isn't going to do it, if I understand how it works. There could be dozens of combinations/permutations per item.

Is this something I can do with the Set field? If so, can I get a layman's explanation of it? I've looked it up online, and I get that it might be my solution, but then my brain starts spinning in its description.

thanks!

this sounds like a location hierarchy

for instance, a country belongs to one and only one continent, yeah?

(if you conveniently want to ignore turkey)

so if an item belongs to a country, you automatically can conclude that the item also belongs to the continent that the country belongs to

so all you have to do is link your items to table4

and of course this has to be a many-to-many relationship, so you need an items_to_table4 table

the mysql SET datatype is ~not~ what you're looking for

Ok... I think what I haven't been understanding is that there needs to be a table between the two I'm concerned about.

so for example if I have a plants table, and a locations table:

I need to build a table that is plant_locations. with something like a key id, then a foreign key for the plant and a foreign key for the locations. So there would be individual entries for each possible location?

so when I build the form for somebody to enter plant locations, they're actually putting info into this linking table?

(just to clarify, there's a separate form & table of plants that has other characteristics unrelated to location)

yes, except without the key id

CREATE TABLE plant_locations
( plant_id INTEGER NOT NULL
, location_id INTEGER NOT NULL
, PRIMARY KEY ( plant_id, location_id )
, FOREIGN KEY ( plant_id ) REFERENCES plants ( id )
, FOREIGN KEY ( location_id ) REFERENCES locations ( id )
);