Pairing two records together before linking to another record

[Max_Goldberg1]

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 [Director]" "John [Producer]" "Sue [Makeup]" "Sue [Audio 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?

[Ben_Young1 · Accepted solution]

Ah!

What you're missing is a junction table!
So, I'm functionally the Airtable systems architect for a large company in the art space and one of our use cases for Airtable is tracking the credits of each artist, producer, designer, etc. that might work on a particular, fairly granularly scoped project.

Before I was brought on, they were directly linking people to projects in order to establish the credit, but this didn't scale well since, similar to your use case, people might need to be credited for different things on a per project basis.

This is where I implemented the following structure:

Here's what each table looks like:

 

 

The Roles table contains a record for each role that someone might have for a given shoot. They're almost like templates for the assignment records.

Finally, we have the Assignments table:

This allows you to isolate someone's role for an isolated project without requiring that you sacrifice the integrity of any of your other data points in the schema.
It's clean and scales incredibly well.

If you want to play around with it, here's a shared viewing link to the base. Feel free to pop it open, copy it into your workspace, and make it your own if you wish. Also happy to answer any questions you might have about it.

[Max_Goldberg1]

I discovered junction tables right after I posted this question, and your explanation and example are great. I'm actually playing with the list view for the first time and I was totally unaware at how perfect it is for things like this. The only thing I don't love here is that the list of records in Assignments could become quite long in this scenario, and when you go to view a list of all your directors, you'd actually be looking at a list of every time a director was assigned. 

[Ben_Young1]

So, presume we're asking the question: "I want to see every person that has ever held the role of director on a shoot."

The first critical question to ask ourselves here is about what object that information is relevant to.
We already actually spoke the answer. "I want to see every person..." Fundamentally, we're looking for person records.

So, we can create a rollup on the People table that will mark when someone has held a director role in their assignment history.

Here's an example:

 

 

We can take this a step further. If you want simply be able to categorize a flat summary of their role history you could do this:

Now, you can easily filter your people records by the Role History field to narrow down and isolate people who have history in a particular role.
It's clean and will only add new roles to the list if a new unique value is added to the history.

I've added and kept both of these fields in the shared base I linked in my original post for you to experiment with.

 

[Max_Goldberg1]

I don't know who you are or why you're responding to my messages, but you are blowing my mind 🤯

[Shannon_Bradley]

I'm working with film as well and people who fulfil multiple roles on different days. I ended up with 2 tables; 1 for role/department and one for the crew. Each person with multiple roles had multiple records, tagged with the days on set. This new method is pretty cool, and I will see if it might help my setup as well! I would be happy to share my basic setup with you to collaborate if you wanted lol. Cheers!