How to relate tables to a multi-line form
I have a table whose sole purpose is to fill out an extensive form. Its records are children of a table called "Projects":
Projects ---< Forms
On each form, I have 15 places where I have to list People. The form looks like this:
Person 1 (LEADER)
Person 13 (ARRANGER)
Person 14 (ORCHESTRATOR)
Person 15 (COPYIST)
I always list someone as LEADER and someone as ARRANGER. For Persons 2-12, there could be any number of people. And, each person can be listed more than once (e.g. person A could be listed as LEADER and also 1-4 additional slots).
I assumed I would connect this Forms table to my People table with a join table, like this:
Forms ---< Listed >--- People
But I'm not sure if this is the best way to handle this. I would like to be able to see Forms associated with each person when looking at a layout in the People table, but…
1) I will have multiple/duplicate entries for each person, so would need to deal with that when viewing the Listed records from the People table
2) Would I need to have dedicated fields for LEADER, ARRANGER, ORCHESTRATOR, and COPYIST, and use a portal for Persons 2-12?
3) Or is there some better way to do this?