I'm trying to create a database for theatre performances. So I have:
a table for data about each play itself;
a table for characters in the play (a many to one relationship to the play);
a table for each run of the play (e.g. city, theatre, date) (many to one);
a table for the actors in each run (many to one).
My problem is how do I link the characters in the play to the actors, as the actors are already linked to the performances and the characters are already linked to the play?
Ideally, I want to query the database for things like: all the performances/roles in an actor's career; all the plays in one city in one year; all the actors who played a particular role.
Filemaker is offering to create another instance of the Actors table. Is that the way forward?
I'm on Filemaker Pro 11, on a Mac (El Cap).