11 Replies Latest reply on Oct 15, 2014 2:52 PM by ezellkendrick

    Many To Many Relationships, Advice Needed!

    ezellkendrick

      Title

      Many To Many Relationships, Advice Needed!

      Post

      How does Filemaker know when to make a relationship Many To Many? Does it compare the field names in one table to the other? I am trying to avoid this but don't know how it makes that determination.

      I am finding that in some of my data comparisons I can't get away from Many To Many relationships. In these instances, I am comparing data in one table to another table, and I do not want a join table because I would then need to enter data to create records for comparison. Unless there is another way since it seems Many to Many Relationships in Database design are frowned upon. 

      Please Advise.

       

       

       

        • 1. Re: Many To Many Relationships, Advice Needed!
          BruceRobertson

          You are the frowner; not FileMaker.

          Join tables are standard for many to many relationships.

          What makes you think there is a problem?

          I think you're going to have to explain a lot more about what you are trying to do, and it will be helpful to use very specific examples.

          Relationships are generally defined because you the developer KNOW and understand and want to control what the relationship is (Invoices, invoice items, products) for example.

          • 2. Re: Many To Many Relationships, Advice Needed!
            ezellkendrick

            Mr. Robertson,

            I haven't designed a database in many years, which was before Filemaker created the relational model. So, I am learning a new concept. Not sure what the comment about me being the frowner means (I assume you thought I was criticizing filemaker and that couldn't be further from the truth.) I was just stating that I had been reading that its best not to have Many To Many relationship and a join table is one of the solutions to solve that issue. Never said there was a problem...just seeking clarification and understanding from those who are far more skilled than I am. I like have some understanding of why so as to build upon the knowledge I am assimilating.

            I was saying that, I am not sure that the join table is necessary for what I am doing because it would create another step of data entry on the join table records to link these two tables together. That is if my understanding of how a join table works is correct.

            For Genealogical research reasons I am comparing many surnames, in the Surname table (through a portal), which are related to one individual in the Contacts table. These same surnames are also relatable to other individuals in the Contact table, but I am not comparing to those individuals. I am not creating new records from these comparisons just seeing which surnames apply to a specific individual through the portal.

            I want to create a database that is built correctly. Hence my concern about having many to many relationships and how to determine what triggers filemaker to make that determination when creating a relationship between two tables. I was trying to set up the comparison of these two tables in a one to many fashion, but when I join the tables together it automatically creates the many to many depending on what fields I use. Just seeking clarity on what is making the many to many relationship occur, and if any of you, who are far more skilled than I am, have a better solution.

            • 3. Re: Many To Many Relationships, Advice Needed!
              BruceRobertson

              YOU said many to many relationships are frowned upon. YOU are the one making that claim. Nobody else is frowning on them or advising against them. 

              So I suggest you move on and explain what you actually want to do. You have said something about surnames and genealogy and comparison. What do you want to do? If you are on the record for John Smith , you wish to see all the other records with surname Smith? And then what? Sometimes it is best not to use any database terminology. You are looking at John Smith's record. What else do you want to know, where else do you want to go?

              • 4. Re: Many To Many Relationships, Advice Needed!
                philmodjunk

                Many to many relationships are a common and necessary part of relational database design. Far more relational databases have a many to many tucked away somewhere than do not.

                The standard Invoices set up is one example and you'll find that the starter solutions released with Filemaker use a Join table in a many to many relationship:

                Invoices----<InvoiceData>-----Products

                where InvoiceData is the join table for the many to many relationship.

                There are two ways to implement a many to many relationship: With a join table or with a return separated list of Match field values--where the return separated list replicates the functionality of a join table (to a limited degree). The join table is usually the better method for implementing a many to many relationship.

                But perhaps what you are thinking of as a "frowned on" relationship is the case where FileMaker's relationship graph links two related tables with a relationship line that shows two "crows feet" at both ends. This will happen if neither of the match fields has "unique values" or "auto-entered serial number" specified as a field option. This also can be no big deal as other aspects of your database design may use that relationship as either a one to many or many to one relationship, but, if possible, it would be better to use a field option that makes it more clear what the real relationship is when you or another developer later examine the relationship graph.

                • 5. Re: Many To Many Relationships, Advice Needed!
                  davidanders

                  A genealogy database that deals with anything more than a simple list / spreadsheet database will have many tables, many join tables, and many table occurrences.

                  I would highly suggest the White Paper for FM Novices for a beginning.

                  http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

                  https://www.google.com/search?q=genealogy+filemaker
                  http://home.earthlink.net/~allan.hunter/FmPro/FamHx/FamHx.html

                  • 6. Re: Many To Many Relationships, Advice Needed!
                    BruceRobertson

                    "For Genealogical research reasons I am comparing many surnames, in the Surname table (through a portal), which are related to one individual in the Contacts table."

                    It would be helpful to know what you mean by that. If looking at Smith, you want to see all other Smiths? You want to see Smallman and Smyth and Smothers? How exactly are the "others" related to Smith? 

                    • 7. Re: Many To Many Relationships, Advice Needed!
                      ezellkendrick

                      Mr. Robertson,

                      Thanks fro your questions and attempt to understand what I am trying to communicate.

                      However, let me clarify my statement about Many to Many Relationships. You stated; "Nobody else is frowning on them or advising against them." I was merely trying to make sense of the statements I found in Filemaker Help which states; “In relational database design, a many-to-many relationship is not allowed.” & “To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships.” Based on Phil's answer to my question, I understand why the Many to Many relationship was occurring and the double crow's feet that I was encountering when setting up relationships.

                      To answer your question; "It would be helpful to know what you mean by that. If looking at Smith, you want to see all other Smiths? You want to see Smallman and Smyth and Smothers? How exactly are the "others" related to Smith? "

                      I am comparing one person to many surnames based on genealogical research through DNA testing. When a person matches another person through DNA, I am attempting to discover the common ancestor which could be several generations back and several surnames removed. Individuals exchange surname lists for the ancestral lines they know and often time, based on this exchange, there is unknown ancestry which includes a new surname(s) in a persons lineage. Consequently, I am comparing lists of surnames to discover commonalities between dozens of DNA matches and each of these dozens of matches may have multiple surnames sometimes fifty plus each. Therefore, If there are person(s) Jane, John, Bob, and Sue that have all provided 20 to 50 surnames each and the only common link is the Surname Woods and / or Brown then the surname list, through a portal, would show the shared surname between the several profiles. This helps direct the research to the yet undiscovered common ancestor.

                      This is just one aspect of the research. I will also create tables and relationships to compare thousands chromosome matches and family trees.

                      I hope this makes sense. Your input is appreciated!

                      • 8. Re: Many To Many Relationships, Advice Needed!
                        ezellkendrick

                        Thanks Phil, your assessment and explanation was right on point. 

                        Regards!

                        • 9. Re: Many To Many Relationships, Advice Needed!
                          ezellkendrick

                          Thanks David,

                          Helpful resources!

                          • 10. Re: Many To Many Relationships, Advice Needed!
                            BruceRobertson

                            An additional resource I think you will probably benefit from understanding is the FilterValues function.

                            http://www.filemaker.com/help/13/fmp/en/html/func_ref3.33.53.html#1030153

                            It allows you to directly compare lists and find common elements.

                            Also, it will probably be worthwhile to create much-simplified examples to help others help you.

                            Simplified example of FilterValues function:

                            Let( [

                            bruceSurnames = "Smith¶Robertson¶Jones";

                            marySurnames = "Johnson¶Adams¶Jones"  ;

                            result = filterValues( bruceSurnames; marySurnames) ] ;

                            result

                            )

                            "Jones"

                            • 11. Re: Many To Many Relationships, Advice Needed!
                              ezellkendrick

                              Thanks for FilterValues function idea. I am sure it will be prove useful.