4 Replies Latest reply on Feb 8, 2011 6:28 PM by eibcga

    Complex Relationships

    eibcga

      Title

      Complex Relationships

      Post

      Let's say I'd like to track that a member could know many software tools, and a software tool may be used by many members.  Also, a member could have recommended many books, and a book could be recommend by many members.  Finally, what book a member recommends depends on what software tools the member knows.  Software could have many books recommended about it, and a book could deal with more than one software tool (I guess this is similar to the common agents-brands-products example, where an agent can represent many brands and many products, and each brand and each product represented by many agents, but each product depends on what brand, etc.).

      I have three parent tables (grey) and four child tables (green) created in the attached relationships graph.  Essentially, there's two many-to-many relationships from the same table (MEMBER) of which these two relationships, based on business rules, are dependent on each other (Book and Software are not independent), thus creating a third many-to-many relationship between SOFTWARE and BOOK.

      But, when I create the relationships in FileMaker from the ERD I had drawn out on paper, I noticed it's a "circular" or ambiguous relationship as far as FileMaker was concerned.  FileMaker resolves the ambiguity by having two aliases of the SOFTWARE_BOOK source table on the relationships graph (two Table Occurrences) to get SOFTWARE_BOOK and SOFTWARE_BOOK 2.

      Am I correct in saying that these relationships will still work despite the fact there are two occurrences of the SOFTWARE_BOOK source table?  I guess the relationships graph does not have to look exactly like the ERD I had on paper, but the end result is the same?  Are these table occurrences also in what they would say in the data normalization world to be in fifth normal form (5NF)?

      Thank you in advance!

      Screen_shot_2011-02-06_at_10.28.39_AM.png

        • 1. Re: Complex Relationships
          philmodjunk

          It will depend on the needs of your layout design. You may find that a second table occurrence of Software may be needed so that you can relate it to Software_Book instead of Software_Book 2. You can create such additional table occurrences, should they be needed, by clicking an existing table occurrence, then clicking the button with two green plus signs.

          • 2. Re: Complex Relationships
            eibcga

            Ok, I didn't think adding a second table occurrence of SOFTWARE linking to SOFTWARE_BOOK would be necessary since it would be redundant?  In addition to the above relationships on the relationship graph, I have added a second table occurrence or alias of the SOFTWARE source table to get the relationship:

            SOFTWARE 2::kp_softwareid —> SOFTWARE_BOOK::_kf_softwareid.

            Now that these relationships have been set up, let's say I wanted to do Finds from the SOFTWARE_BOOK Layout (which has its fields already placed on the Layout) of records contained in both SOFTWARE_BOOK and SOFTWARE_BOOK 2 table occurrences, then I should add related fields from SOFTWARE_BOOK 2 table occurrence and add them to the SOFTWARE_BOOK Layout?  This way I can ask complex questions based on the information stored in these two table occurrences?  I was not sure how making two table occurrences of, for example, the SOFTWARE_BOOK source table would effect doing Finds or Subsummary reports.

            I'm beginning to see how FM works and how one can set up complex relationships by being creative with table occurrences and layouts with related fields.

            Thanks very much Phil.

            • 3. Re: Complex Relationships
              philmodjunk

              I would not place fields from Software_Book 2 on a layout that is based on Software_Book. Since both table occurrences (TO's) both refer to the same data source table, I fail to see the purpose to that. In fact, FileMaker would have to trace the relationship from Software_Book to Book to Member_Book to Member to Member_Software to Software to Software_Book 2 in order to determine what record would be used to supply the values seen in those fields.

              Adding a 2nd TO of Software and linking it to Software_book would not be redundant. This would provide a direct  link between these two tables that does not have to be traced back around through all the intervening tables and their records. It may or may not be useful to you, it depends of the design of your layout and scripts.

              Here are a pair of threads on TO's you may find interesting reading: (The second link takes a pretty advanced look at TO's and FileMaker functionality.)

              Tutorial: What are Table Occurrences?

              Table vs. Table Occurrence (Tutorial)

              • 4. Re: Complex Relationships
                eibcga

                Ok great, thanks again Phil.  I'm learning lots.