I am trying to set up a database of past video shoots, including who worked on them and what position they worked as. I have a table of people, a table of positions (director, cinematographer, producer, audio, makeup, etc), and a table of projects.
The issue I'm running into is that our team often juggles positions, so John may be the director one day, and producer the next. Sue may be doing makeup, and then audio another day. (I admit it's chaotic and we're working to nail down positions... Part of why I specified that this is a database of past video shoots!)
So, simply linking the people and projects tables doesn't work, because that doesn't say what position each person worked as. I could add individual fields to the projects table for each position and select the director, the producer, the audio tech, etc, but that seems clunky and would make it hard to simply view a list of who was on the shoot.
The solution I've found is to create another table that simply links contacts with positions, and uses a formula to automatically format the name of the record—"John nDirector]" "John nProducer]" "Sue eMakeup]" "Sue eAudio Tech]" etc—but that also feels pretty clunky because I need to make a new record every time someone works in a new role.
I'm dreaming of a way to simply pair records up as they're being linked to another record. Is that a thing that exists?
EDIT: Seems like I may have discovered and invented junction tables... Is this really the best solution?