I have a simple schema where I have a set of schools, and a set of people who are affiliated with those schools in various ways (parent, PTSA officer, teacher, etc.). A single person may be affiliated to one or more schools, and in one or more ways for each school.
What is the best way to represent this in a base? At the moment I have a third table with a linked reference to people, a linked reference to school, and a multiple select affiliation type. The two problems I have with this are:
-
If I create a form view to send out for people to fill in, there is no good way for people to enter their affiliations. I’m working around that at the moment by having another, free text field where people type this in, then I will convert those values into proper affiliation records.
-
I can show lookup fields in the people table to show affiliations, but they only show all the schools and all the affiliations, not (affiliation, school) pairs.
Is there a better way to represent this that would make the form view work better and make it easier to see the exact affiliations?
