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.
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)