1 Reply Latest reply on May 3, 2016 7:42 AM by Mike_Mitchell

    Relational database structure question

    reeflifeapps

      Good day,

      I am developing an fm14 database and am stuck on how to accomplish something.   I have many tables, but two of which are the issue.  I have a database (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. 

       

      Not sure how to set up the relationship to accomplish this.  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? 

       

      Also, i have the data structure in that, when exported to excel, the column headers are the region, the first row is the species ID with the main body with "1" or blanks if the boolean is true or not.  Not sure how to set up the fields?

       

      This data will be exported to a MySQL database.

       

      Thank you, David

        • 1. Re: Relational database structure question
          Mike_Mitchell

          To the first: You have what's referred to as a "many to many" join. Each species can appear in multiple subRegions, and each subRegion can have multiple species. The typical way to implement a many to many is to use a join table, where each record represents the unique combination of parent records (in this case, species and subRegion). You would minimally include the key fields from the two parent tables.

           

          Then, you can attach different species to their regions by creating a record in the join table. Delete the record to break the tie.

           

          To your second question:

           

          reeflifeapps wrote:

           

          Also, i have the data structure in that, when exported to excel, the column headers are the region, the first row is the species ID with the main body with "1" or blanks if the boolean is true or not.

           

          What boolean?

           

          Regardless, you can create a separate field that calculates to "1" if the condition is true, or "" if it is (or vice versa, depending on the need).