I am developing an relational database and am stuck on how to accomplish something.
I have many tables, but two of which are the issue. I have a table of several thousand marine species, called "species". I have another table that contains the global oceans divided into 188 different sub regions, called "subRegions". Naturally, each species can be found in multiple sub regions. The subRegions table had "id", "name", and "coordinates" as it fields.
It is easy to set a foreign key to the subRegions id, from the species id, but how to set this so that multiple regions can be displayed, per species, at once?
Any ideas?
So what you have is a many-to-many relationship with optional participation in each. So you'll find your species in none, one or many subRegions; each subRegion may contain none, one or many species.
Implementing this, you would have an intermediary table to record the relationship. That is your new table would be called speciesSubRegions. The table will contain species_id and subRegion_id as a composite primary key.
The relationship then looks like species has a one-to-many relationship with speciesSubRegions with optional participation on the species side and mandatory participation on the speciesSubRegions side.
Then subRegions has a one-to-many relationship with speciesSubRegions which is optional on the subRegions side and mandatory on speciesSubRegions.
Clear as mud?