9 Replies Latest reply on Mar 16, 2013 12:28 PM by RayCologon

    ERD: Many-to-Many or Parent-Child-Grandchild?

    Guy_Smith

      Thanks to the efforts of LaRetta and Michael, I now understand Join Tables a bit better and thought I solved a Many-to-Many Relationship with one, but now I'm even more 'confusedered' than when I started: I have people (Scouts) attending Events (Campouts, etc.) that are assigned to small groups of 8-10 (Patrols). Each patrol is then charged for costs they incur, which is different for each patrol and each event.

       

      I originally though " OK, I have many people attending many events and many events can have many people attending them: An obvious many-to-many relationship, so I created three TO's: One for People, One for Events, and the Join Table (Patrols)". This setup works super for picking people to attend events, but that's about it.

       

      Then I realized that my Join Table is wonky in that one direction is just fine: Events to Patrols is a One to Many: Each Event can have Many Patrols but each patrol can only belong to each event. The Problem is on the other side: Each Patrol can have many Scouts, but each scout can only be assigned to one patrol.

       

      So...Do I actually have a many-to-many with People (?) or Patrols (?) as the Join Table or do I have a Parent-Child-Grandchild with Events having many patrols which in turn have many people?

       

      Thanks much in advance for any help!

        • 1. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
          kidpub.press

          The Patrol entity can be looked up, so really all you need is a many-to-many with Event and Scout...the Patrol isn't attending, it's Scouts who happen to be in a Patrol. To calculate the cost, just group the Scouts at the Event by Patrol.

           

          Perry

          • 2. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
            Guy_Smith

            Kidpub:  Thanks for the response - I'd never thought of that, but you're absolutely right about the Patrol being separate from the other two, but I'm still not sure where to put the patrol-specific fields:  Would they go into the join table along with the two foreign keys or would they be better off being in a separate child table from the event TO?  I guess I'm not as far along in my understanding of join tables after all! 

             

            I sure appreciate your time and effort!

             

            Best Regards,

             

            Guy

            • 3. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
              RayCologon

              Hi Guy,

               

              The scenario you described is not a conventonal join table (where, by definition, every join has a separate record in the join table). With a patrol table having 8-10 associated scouts, the intermediate records between Scout and Event are not (primarily) join records.

               

              However if Perry is suggesting that patrol could be an attribute of either the scout or event, then I don't agree - I'd suggest that Patrol is an entity in its own right.

               

              One way to manage the relationships would be to create a conventional join table (that has a record to signify each scout's attendance at a given event, then provide a separate Patrol table, and record the PatrolID into the join record as an attribute of an individual scout's attendance at a specific event. That would look something like the following:

               

              Scout --- ScoutEventJN --- Event

              |

              Patrol

               

              Alternatively, you could choose to place the join table between scouts and patrols and add a many-to-one relationship from patrols to events, along the lines of:

               

              Scout --- ScoutPatrolJN --- Patrol --- Event

               

              Both methods may be considered valid, and depending on how you want the structure to support reporting you may prefer one method over the other. For example, the latter may enable you to run reports by patrol (eg from a layout based on a Patrol TO) with reduced reliance on aggregation of related data. If you have other requirements to consider, they may inform your choice - however, either method may be suitable given the requirements you've outlined so far.

               

              In either case, patrol-specific attributes would be accommodated appropriately in the Patrol table, and the association between scout and patrol would be via the join table. Note that in the latter case, it would not be possible for a scout to attend an event without being assigned to a patrol (though you could make a similar provision in the first example by applying valudation to make the PatrolID a required attribute in the ScoutEventJN table).

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              Email: cologon@nightwingenterprises.com

              http://www.nightwingenterprises.com

              ------------------------------------------------

              • 4. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
                kidpub.press

                Excellent analysis, Ray. My take on the use case is that Patrol is more or less irreleveant in terms of the Event. It isn't a Patrol that attends an event, it's one or more Scouts. Each Scout beongs to one Patrol, so if you need to see what the Patrol representation is at an Event later on, it can be calculated. I think the join is just on Event and Scout, and that Patrol is just an attribute of Scout.

                 

                Perry

                • 5. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
                  RayCologon

                  Hi Perry,

                   

                  Interesting comments, and I can see that we've interpreted Guy's requirements a little differently. As I see it, patrol could only be an attribute of the Scout entity if each scout is always in the same patrol for all events. However I'm guessing from Guy's description that patrols are (or may be) event-specific and a scout may be assigned to a different patrol for each different event.

                   

                  That being case, I'd reason that patrol is not an attribute of the Scout or Event entities, but a characteristic of the join between (a subset of) scouts and a specific event (which is represented by the first of the models I suggested) or operates as a containing entity within/before the event (as in the second model).

                   

                  It hinges in part on the question of whether patrols and patrol membership are formed on a per-event basis, or stand unchanging from event to event with fixed membership. Ie if there's any chance that a scout will be in different patrols for different events, storing patrol as an attribute of Scout will make it difficult to determine (when there are multiple patrols for a given scout) which patrol the scout belonged to for which event.

                   

                  But irrespective of that, perhaps the strongest argument for the Patrol as an entity in its own right is Guy's mention of patrols (as opposed to scouts) being charged for costs incurred. Ie, it's a far simpler matter to bill (and summarise charges for) an entity than for an attribute. ;)

                   

                  Regards,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  Email: cologon@nightwingenterprises.com

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 6. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
                    Guy_Smith

                    Dr. Cologon and Kidpub:

                     

                    My fault for not making my original post clear enough:  You are spot-on, Dr. Cologon:  The scouts are assigned to random/different Patrols for each event, and each Patrol has different charges from other Patrols at the same event and different charges from event to event:  The only consistent thing is our inconsisitency!

                     

                    And to further Dr. Cologon's last paragraph, the two main purposes for this portion of the DB solution are to (a) provide summary reports to our Governing Committee and, (b) most importantly to me as the Treasurer, allow for devising a script to charge the individual scouts' accounts the appropriate amount automatically instead of my current practice of manually entering the charges one at a time.

                     

                    I'm going to get hot on implementing these ideas and I'll post my results.

                     

                    Thanks so much for all of your guidance - you've given me a superb opportunity to learn!

                     

                    Best Regards,

                     

                    Guy

                     

                    p.s. for Dr. Cologon - I've been fortunate enough to have visited Melbourne - Great city with FANTASTIC people.  I've been to more than 20 countries and countless cities, but never felt more welcome than there.

                    • 7. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
                      kidpub.press

                      This is why I never made it past Cub Scouts :^)  I say just give each kid twenty bucks and avoid all the accounting hassles!

                       

                      Great discussion...I learned something, too!

                       

                      Perry

                      • 8. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
                        Guy_Smith

                        Dr. Cologon:  Sorry to take so long to update this post, but I had to go out of town for work.  My day job sure takes a lot of valuable time away from my FM play time!  In your response, you had given me a couple of choices:

                         

                                                                                                                                          "That would look something like the following:

                         

                        Scout --- ScoutEventJN --- Event

                        |

                        Patrol

                         

                        Alternatively, you could choose to place the join table between scouts and patrols and add a many-to-one relationship from patrols to events, along the lines of:

                         

                        Scout --- ScoutPatrolJN --- Patrol --- Event"

                         

                         

                        My remaining Question:  In these cases, would the Join Table only contain the Foreign keys for the two "joined" tables (Scout and Event TO's or Scout and Patrol TO's)?

                         

                        I also read your outstanding Tech Brief on Data Modeling, File Architectures and the Relationships Graph and was all excited that I had, indeed, produced a Hybrid model.  Unfortunately, it was a hybrid between amorphous and chaotic.

                         

                         

                        Thanks again - having the Godfather of FM answer your posts is a treat and an honor!

                         

                        Best Regards,

                         

                        Guy

                        • 9. Re: ERD: Many-to-Many or Parent-Child-Grandchild?
                          RayCologon

                          Hi Guy,

                           

                          At a bare minimum, a join table contains the foreign keys for the two joined tables, but it may legitimately hold other fields that pertain to the association rather than the entities on either side.

                           

                          So, for example, in the options I outlined above, the join table between Scout and Event (the first scenario I described) would also include a foreign key for Patrol - to record that for attendance at a particular event the scout was in a particular patrol. Any other information that is particular to a scout's attendance at that particular event could also be stored in the join record - such as the date of the scout's confirmation of attendance, whether required forms/documentation for attendance has been received, the method of a scout's travel to the event and so on.

                           

                          Meanwhile, I'm pleased you found the Solution Architectures Tech Brief helpful - and glad I was able to provide you with some options to work with in relation to your ERD question!

                           

                          All the best,

                          Ray

                          ------------------------------------------------

                          R J Cologon, Ph.D.

                          FileMaker Certified Developer

                          Author, FileMaker Pro 10 Bible

                          NightWing Enterprises, Melbourne, Australia

                          Email: cologon@nightwingenterprises.com

                          http://www.nightwingenterprises.com

                          ------------------------------------------------