"Many to many" relationships are done all the time in FileMaker, but they usually are implemented with an extra "join" table between the two "many" tables that you want to link in this fashion.
Films---<Production--<Production_Sites>--Sites---Content (---< means one to many and I'm assuming that a 'shoot' is specific to one site here.)
Production_Sites would be a join table that enables many different sites records to link to many different production records.
Production::ProductionID = Production_Sites::ProductionID
Sites::SiteID = Production_Sites::SiteID
In similar fashion, you can set up these many to many relationships:
Films---<Roles>---Talent //Roles is the join table
Production---<Production_Talent>---ProdTalent //ProdTalent is a table occurrence of Talent
You may want to examine this demo file that links "Companies" to "Contracts" in a many to many relationship: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html
If "Table Occurrence" is an unfamiliar term: Tutorial: What are Table Occurrences?
Thank you for all of that feedback, I really appreciate it.. I figured I would need that middle join table, which was why I was a little worried setting it up..
So I have the following setup:
Production-----<Prduction_Sites>-----Sites-----<Sites_Content>-----Content-----<Production_Type>----(loops to Production)
Is there any need to have Talent linking to Sites? Even though multiple actors will be on multiple sites? Also when I have a single day of production, I could be shooting a single piece of content for distribution on multiple websites. Would I need Production_Content to save my butt? Is it better to be safe than sorry to setup all of these just incase?
So additional join fields:
I don't think you need that many join tables as not all of your relationships appear to be many to many.
A given content record only refers to a single site does it not?
You won't be able to "loop to production" as Filemaker won't allow that, but you can define a second table occurrence of Production if you need it.
These aren't join fields by the way, they are join tables.
"Is there any need to have Talent linking to Sites? Even though multiple actors will be on multiple sites?"
You can add such a relationship if you need to. It's up to you. If you need to see a list of all talent for a given site, then add that relationship in.
"I could be shooting a single piece of content for distribution on multiple websites."
That describes a one to many link. One content record to multiple web sites so there doesn't appear to be a need for a join table.
A few suggestions:
Keep clear in your mind whether you have a one to one, one to many or many to many relationship. Join tables are only needed for many to many and using them where you don't have such a relationship needlessly complicates your design.
Make lots of backups clearly labled so that you can tell which are oldest. Save a minimum of one back up copy a day so that you can refer back if you make major design changes and then change your mind.
Break this project down into smaller sub projects and get one part working to your satisfaction at a time. That first sub project will likely teach you things that will help you with the next key part.
If something is unfamiliar, look it up in FileMaker help, then search the knowledge base or post a question here if you still can't figure it out.
Get FileMaker Pro Advanced if you haven't already. It's more expensive, but the Script Debugger alone will keep you sane when a script doesn't work the way you thought it would. Throw in database design reports, custom functions and custom menus and you'll soon wonder why anyone would waste their time developing with FileMaker Pro instead of Advanced.
Read this thread to learn about a method (Anchor Buoy) for keeping your table occurrences organized in a way that clearly corresponds to the design of your layouts: