AnsweredAssumed Answered

Do I need a "join table"?

Question asked by MarkPeters on Jul 5, 2011
Latest reply on Jul 6, 2011 by philmodjunk


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.