3 Replies Latest reply on Jul 6, 2011 9:25 AM by philmodjunk

    Do I need a "join table"?



      Do I need a "join table"?


      10 tables, ~20 fields each, exactly 94 records per field.  Each record = an element on periodic table.  Each field = a property (boiling point, color, etc.).  Property values were ALREADY entered in excel and imported to FM.

      In an "11th" table, each record = a chem experiment.  I made a tab w/ all 200 portal fields (1 for every property), and a drop-down list for the 94 elements.  When 1 element selected it's 200 properties will display.  Only 1 element per experiment/record need be displayed, but I will change it regularly to refer to different possibilities.   

      I made "indexed, auto-serial" numbers for every table as ID's for connecting tables in relationships.  These happen to be identical to elements' Atomic #'s.  I was able to accomplish every goal I've described so far.  

      However, some data are long decimals (0.00000009) I'd prefer in scientific notation.  My current structure requires me to go one-by-one all 200 fields and "Replace Field Contents", "Replace with Calculated Result", plug in: "If ( Length (Boiling Point)  >  5 ; ScientificNotation ( Boiling Point ; 2 ) ; Boiling Point )."

      Some people are saying my structure is completely wrong and will haunt me later, and I need "A 'join table' to resolve a many-to-many relationship into two one-to-many relationships, with only 'ElementID', 'PropertyID', and 'Value'."  

      I'm struggling to understand what this means!  Below is screenshot of some of current relationships and example of part of one of my 10 properties tables. 


        • 1. Re: Do I need a "join table"?

           Seems like you have 3 possible approaches here:

          1) Keep things like you have set them up. That really complicates your life as you have 10 different tables to maintain with lots of complications in your layout and table design that you don't really have to deal with if you use one of the other approaches.

          2) Merge all 10 tables into a single table with all the property fields included in the same table. 200 fields is a lot of fields for a single table, but not unmanageably so. This is probably your simplest structure but it may not be as flexible as option 3.

          3) Make each record a single property for a single element.

          Thus for Hydrogen, you might have one record that reads: H | Atomic Number | 1 , another record that reads H | Boiling Point | Whatever the BP of Hydrogen is and so forth. For 200 properties for hydrogen, you'd use 200 records, one for each property. This is what has been suggested to you elsewhere. This can be especially useful if you only need to display the properties for a single element at any one time as you can present your properties for that one element in a vertical list where each row is a different property.

          You'd use another table for the elment name and any other data on the element that isn't part of the list of 200 properties:


          I don't see a join table here myself, just a one to many relationship between Element and Properties. Any links to your experiment table, would I think, be linked to the Element table--either directly if there is only one element specified for each experiment or via a different join table if multiple elements are to be linked to the same experiment (which would be typical of any chem labs I've ever worked with.)

          • 2. Re: Do I need a "join table"?

            ok thank you ....i think im just gonna keep it how it is, lol.  because im not gonna be changing the values form these tables, just viewing it for comparisons.  so once it's up and running i won't need to manipulate it.

            • 3. Re: Do I need a "join table"?

              You won't need to change the values, but separate tables like this will complicate how you display the data from them or use these values in any calculations you might choose to use. (Perhaps to generate an answer key for each lab?)