6 Replies Latest reply on Jul 31, 2017 3:49 PM by ErikWegweiser

    Related Fields - How to best structure relationships

    jcare

      I have a list of hospital names that I want to store inside a hospital table:

       

      Hospital

      - id

      - name

       

      However, these hospitals need to be displayed as a drop-down list as three different fields inside my parent table, Parent.  Note, for this example I'm not actually using my full table Parent for simplicity. 

       

      Parent

      -id

      -name

      -hospital ID

      - hospitalFieldA

      -hospitalFieldB

      -hospitalFieldC

       

      Each hospital field A, B, C represent their own information (to the user) but I do not want to create redundancy by making two other tables with the exact same data as Hospital; these fields need to contain the same data as Hospital because they're the same pool of hospitals. 

       

      What I've tried thus far:

      - Making a new table HospitalA, HospitalB and referenced the Hospital ID inside the Hospital table (because they all should be equal).  I created a value list that lists the values from Hospital name stored inside Hospital ID from Parent.  However, if I make this the same for the other hospital fields, picking one value from the drop-down list changes ALL others values in those fields.

       

      See screenshots