I think the best way to tackle the problem is to have the data into three tables (people, food, favorite foods) where favorite foods links people to food items and has a column called preference that is a single select and contains three options: primary, secondary, tertiary. You could then create a view in this favorite foods table that is grouped by people to see groups of all favorite foods per person.
In order to collect the data through forms, you can then keep collecting the food preferences in a form connected to people as you are currently doing. Then you have two options:
- EASIER, LESS FLEXIBLE: you can use Zapier, Integromat, or the Airtable automations to create separate records in the favorite foods table whenever a new person is created, one for each food item chosen
- MORE COMPLEX, BUT MORE FLEXIBLE: you can create a script which does the same thing. It is slightly more complex as it requires coding but is far more flexible as you can possible add complex conditions and logic as you scale the system and increase functionality
Hope it helps!
Calendly: Calendly - Alessio Monino