jcare

Related Fields - How to best structure relationships

Discussion created by jcare on Jul 31, 2017
Latest reply on Jul 31, 2017 by ErikWegweiser

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

Outcomes