3 Replies Latest reply on Apr 12, 2011 2:54 PM by philmodjunk

    Table Relationship Help

    KoryAlden

      Title

      Table Relationship Help

      Post

      Hey everyone,

      I'm new to filemaker and I recently joined the Lynda.com community, learning about Filemaker Pro 11 hands on. 

      I'm working on my first database system to handle my short films and indie films, and I'm stuck with trying to determine my 'one to many' relationships because I guess I think all of my tables are 'many to many'.  Hopefully someone can help me with this before I move forward.  The video states that you really shouldn't have 'many to many' and it will cause more problems, which added to my stress of making sure this is done right.

      Here are the tables I'm setting up:

      Sites- These are my sites that have various short films I've created.  Multiple sites can have multiple films.

      Content- This is my videos and photos associate with each shoot I do.  I can shoot multiple videos and photos for numerous sites I'm trying to promote, and I even shoot more than one shoots for a single day of production.

      Production- A single day of production can produce multiple content updates for multiple sites, with multiple people involved on multiple sites.

      Talent- Actors involved in the shoots.  Multiple actors on multiple sites, productions, and content thats produced.

      Again, I appreciate everyones feedback and help.

      Take care

      Kory

        • 1. Re: Table Relationship Help
          philmodjunk

          "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?

          • 2. Re: Table Relationship Help
            KoryAlden

            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)

            Talent----<Prod_Talent>-----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:

            Production_Content

            Talent_Sites

            Talent_Content

            Thanks again!

            Kory

            • 3. Re: Table Relationship Help
              philmodjunk

              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:

              http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/