I'm very new to FileMaker, but I've been making my way through the Lynda tutorials and I'm completely stuck at trying to resolve a few many-to-many relationships. The tutorial uses the example of Actors and Movies to explain the join table (with the join table in that example being Roles). I think I understand the concept of the join table, but I cannot figure out what data to build my join tables around.

I am an actor and am trying to build a database that will organize information about auditions, workshops, industry contacts, productions, and gigs. At this point, I imagine 5 tables:

Workshops

Auditions

Gigs

People

Productions

The many-to-many relationships that I cannot figure a way around are:

Auditions - People

At any given audition, there is often a casting director and associate casting director present. There can also be some combination of director/writer/producers/clients in the room, depending on what the audition is for. So each audition will have many people associated with it, and each person will be associated with many different auditions. I thought a possible join table would be something like "Job Title" or "Function" - essentially specifying the "role" that person played in that specific audition room. I think I'd get into trouble, though, when there are multiple people in the room with the same job title (as is often the case with producers or casting directors).

Gigs - People

Similar to the audition dilemma, and I get to the same point in my idea for solving it where I would make a join table of "roles" (director, producer, writer, PA, actor). Again, though, don't I have a problem when I have multiple listings in the join table with the same "role" (as would be the case for actors, producers, etc.)?

Productions - People

Same thing.

Hopefully there's some easy solution to this that I'm just not seeing (or that I didn't understand from the tutorials). Any help would be greatly appreciated!

I think I'd get into trouble, though, when there are multiple people in the room with the same job title (as is often the case with producers or casting directors).

If there are two producers at an audition, you will create two join records with different PersonID and identical roles. That's what needed in order to reflect what happened in real life, and you will get into trouble if you do anything else.

Thanks for the response, Comment! I think what I wasn't quite getting was that the records of that join table will all be unique because they will reflect the combination of person-project-function. So while multiple people might be serving the same function, they are different people so the records will be different. Not sure why I was having such trouble seeing that, but thanks for helping me get there!