Re: Assign multiple linked people to roles and to a project

BKrynicki · ‎Dec 09, 2021

I’m a casting director. I have a table of Artists with their info and I have a table of Shows. To each Show I can assign multiple artists (they are linked). But I also need to assign a specific role to each artist involved. The kinds of roles are different for every Show, each Artist may be part of multiple shows. It is easiest for me, conceptually, to enter info per Show: Title, Date, (Venue and 6 more fields of info), what artists are involved and what roles they play. I don’t want to enter Title, Date, etc. repeatedly for each Artist in the Show.

Jonathan_Lutz · ‎Dec 09, 2021

For the most flexibility, this is a scenario that would benefit from the use of a third table that is used as a “junction” table between Artists & Shows. Let’s call it Roles. This table would have 3 fields (at the least): Show (linked record field to Shows), Role (name of role), & Artist (linked record field to Artists). Both linked record fields would be restricted to a single linked record.

So the relationships between tables would be: A Show can have many Roles, but each Role would be linked to only one Show. Similarly, an Artist can have many Roles, but each Role would be linked to a single Artist.

This junction table is needed anytime you need to store metadata about a relationship. In this case, that extra “metadata” needed for the relationship between Shows & Artists is the role name. Without it, you end up having the problem you ran into where you can only link Artists to Shows & vice versa but without a way of identifying anything else about that relationship (in this case, what role the artist is playing).

Modeling the data in this way admittedly makes data entry a bit more complex, but with the use of some scripting/automation, you can have the records in the Roles table created automatically if desired. This is the most flexible way to model the data, however, & so you’re typically better off going this route despite the data entry challenges. This is where the use of Airtable as a backend database & user-facing front end collides.

Hope this helps!

See Solution in Thread

Jonathan_Lutz · ‎Dec 09, 2021

For the most flexibility, this is a scenario that would benefit from the use of a third table that is used as a “junction” table between Artists & Shows. Let’s call it Roles. This table would have 3 fields (at the least): Show (linked record field to Shows), Role (name of role), & Artist (linked record field to Artists). Both linked record fields would be restricted to a single linked record.

So the relationships between tables would be: A Show can have many Roles, but each Role would be linked to only one Show. Similarly, an Artist can have many Roles, but each Role would be linked to a single Artist.

This junction table is needed anytime you need to store metadata about a relationship. In this case, that extra “metadata” needed for the relationship between Shows & Artists is the role name. Without it, you end up having the problem you ran into where you can only link Artists to Shows & vice versa but without a way of identifying anything else about that relationship (in this case, what role the artist is playing).

Modeling the data in this way admittedly makes data entry a bit more complex, but with the use of some scripting/automation, you can have the records in the Roles table created automatically if desired. This is the most flexible way to model the data, however, & so you’re typically better off going this route despite the data entry challenges. This is where the use of Airtable as a backend database & user-facing front end collides.

Hope this helps!

BKrynicki · ‎Dec 09, 2021

Thank you! I had played with the 3rd table (Roles) idea but got stuck, your explanation helps. I guess I will study the automation features more to help as you suggest.
Much appreciated.