14 Replies Latest reply on Sep 2, 2013 6:01 PM by MSNThomas

    Many many to many's

    MSNThomas

      I have a database that has a lot of many to many relationships.

       

      They almost all consist of ID1, ID1, and then maybe one text field describing the relationship (not counting the uniqueID of the connection table, creation date, name, etc.)

       

      What I'm wondering is if I should use different TOs of one connection table to make all (or most) of my many to many relationships or if I really should make seperate connection tables for each pair of tables I want to join.

       

      For example, A meeting can have many organizers, and an organizer can organize many meetings. Meetings also have Associates in my company who are dealing with the meeting, a each associate can be dealing with many meetings. Both are many to many:

       

      Meeting>-----<Organizer

      Meeting>-----<Associate

       

       

      While I've been designing a new database I've found at least 4 many to many relationships that would use identical tables, and expect that more will pop up.

       

      the IDs for my records should be unique throughout all tables, so in my mind using the same connection table for both joins should work, but I wondered if anyone had any experience with this. Besides the risk of my messing up my IDs and two records from two different tables sharing the same ID, are there any other risks I might be running? Any other gotchas? Best Practices?

        • 1. Re: Many many to many's
          DanielShanahan

          The organizer and the associate are both people who have a particular role for a particular meeting.  I would model it like this:

           

          MEETING-<ROLE>-PERSON

           

          Where the ROLE table would have the following fields:

          ID

          meetingID

          personID

          role

           

          (plus the usual housekeeping fields: created, modified, etc.)

          1 of 1 people found this helpful
          • 2. Re: Many many to many's
            Oliver_Reid

            Daniel is absolutely correct and that is the wideley accepted approach for this situation

             

            Among its many advantages is that, by searching the Role table, you can easily display the meeting list/calendar for any person, or the staffing for any meeting.

            • 3. Re: Many many to many's
              Stephen Huston

              Regarding the specific issue of storing different types of join records in the same table:

              • This can put an extra load on the server for identifying which records from a much larger group to return for a specific portal (depending on the potential number of records for all uses).
              • If you ever need to do reporting directly out of the join table or use the join table as a layout-base-table, having more than one type of join record in the table could complicate your work.

              That said, I have used the join table for more than one type of join record in a very few instances where there were additional fields used by only one group of records in the table, so those matches relied on a key/ID which was missing from the other set of records, and this made them simple to isolate for both portals and separate reports.

              Tables aren't that costly in file overhead to make it worth complicating your work down the road.

              • 4. Re: Many many to many's
                MSNThomas

                Thank you for your reply!

                 

                The Organizer table is actually a table of Organizations, and the Associate table is a table with employee IDs for the Convention Bureau that will use the database.

                 

                For each Organziation related to the meeting (actually, a bid process for a meeting), I also want to keep track of the staff involved with each organization.  I am thinking It will be the Meeting (Bid) TO, with a connection TO (ConnectionTO1) that connects to the organizationTO.  Then another connection TO that connects to ConnectionTO1 and a Staff TO.

                 

                I've what is at present my basic idea for a TOG.  It doesn't include any TOs that would be involved with pickers yet.Meeting TOGs - YuuchiTog.png

                • 5. Re: Many many to many's
                  tcswco

                  What application did you use to draw the diagram included in your message?

                   

                  Thank you.

                  • 6. Re: Many many to many's
                    MSNThomas

                    www.lucidchart.com

                    • 7. Re: Many many to many's
                      MSNThomas

                      Thank you for your reply!

                       

                      When you use a match table where you have seperate fields for the IDs used in different kinds of relationships, does that help with the server load issue you mentioned above?

                       

                       

                      As for creating a layout based on the match table, I can't see doing that.  That may just be a lack of foresight on my part, though.

                       

                      Does anyone see any issues with have a match table to a match table, like I have in the .... I'm not sure what you would call what I have there... TOGRD maybe?

                      • 8. Re: Many many to many's
                        DanielShanahan

                        ccbthomas, it is obvious you have done a great deal of thinking on this.

                         

                        Just a few symantic suggestions to help clarify your question:

                        1. The diagram you provided might be referred to as an ERD or Entity Relationship Diagram (you did a nice job, by the way).

                        2. TOGs are Table Occurrence Groups and refer specifically to the Relationship Graph in FileMaker Pro (at least, I've not encountered that term outside of the Relationship Graph).

                        3. The table between between a many-to-many relationship is often called a "Join" table.  I've also heard it called a "Junction" table.  There are probably a few other terms for it as well.  I've not seen it called a "Match" table, but that doesn't mean it isn't common and you are consistent in your use; the term is just not familiar to me, for whatever that is worth.

                         

                        Now in the ERD with you provided, you have a three of entities marked as Position and three marked as Person.  When doing data modeling there should only be one entity per thing, so it might help to clarify your relationships if you made another copy (always keep your original work) and made one entity per thing.  Unlike the Relationship Graph, a data model can have cyclical relationships so don't hesitate to draw lines back and forth between the entities.

                        • 9. Re: Many many to many's
                          Stephen Huston

                          ccbthomas wrote:

                           

                          When you use a match table where you have seperate fields for the IDs used in different kinds of relationships, does that help with the server load issue you mentioned above?

                          Yes, somewhat: separate foreign key fields for each  separate table allows the server to  only have to deal with the index of records having values in the appropriate field(s), as opposed to the larger indices for shared fields where every record has an indexed value. The index size of the jey field(s) being used to determine which records to return for caching.

                          • 10. Re: Many many to many's
                            MSNThomas

                            Thank you, Daniel

                             

                            It is actually supposed to model the TOG that I would create in Filemaker, so everything is meant to be a Table Occurance, and not an entity. I used the ERD arrow system because it is more informative than just having a connecting line like the Relationship Graph has.

                             

                            The question I'm wrestling with is whether StaffConnect, OrganziationConnect, and FacilityConnect should be different TOs of one Table, or completely seperate tables.

                            • 11. Re: Many many to many's
                              MSNThomas

                              Thank you.

                               

                              So do you think one connection table with different fields for the different kinds of field would work for this solution?

                               

                              Also, with different fields for the different kinds of keys, could you use one TO for the different types of joins?

                               

                              Something like this:

                              Yuuchi - YuuchiTog v2.png

                              • 12. Re: Many many to many's
                                DanielShanahan

                                In my opinion they should be the same table.  One of the purposes of a relational database is to reduce duplication.  We usually think of that from the data entry perspective.  However, it can be true for the structure perspective as well.  The duplication in this case are the fields.  You have the same fields in different tables.  That can be reduced.

                                 

                                I think modeling it in an ERD manner would be helpful.

                                 

                                My 2¢.

                                • 13. Re: Many many to many's
                                  Stephen Huston

                                  You could, but, again, small tables don't each add much overhead to a file, so why would you want to do that instead of having different tables for different types of join data?

                                   

                                  I would think that stacking the various data pieces into a single table would only tend to complicate the swarm of spider legs around the relationship graph for that join table, or require so many separate TOs for that same base table as to offset any other structural savings of the method.

                                  • 14. Re: Many many to many's
                                    MSNThomas

                                    The one thing that having the Multijoin table with the ContactConnect join table attached to it would let me do is switch between having the contacts filtered by organization or having every contact related to the meeting all in one portal.  Whether that is worth it is something will have to think about with use cases.