4 Replies Latest reply on Aug 21, 2013 9:17 AM by philmodjunk

    Join table connected to more than two other tables (TOs)

    ThomasM_1

      Title

      Join table connected to more than two other tables (TOs)

      Post

           Hi, my head is exploding trying to figure out the best relationship graph for the following situation:

           I have these entities:

           - Clients
           - Instructors
           - Offers
           - Appointments

           with these relationships:

           - Any client can have multiple appointments.
           - Any appointment can include multiple clients ("class").
           - Any instructor can have multiple appointments.
           - Any appointment can include multiple instructors (up to 3).
           - On any appointment one offer will be executed.
           - Any offer can spread offer multiple appointments.

           Therefore there are the following many-to-many relationships which need to be resolved:

           - Clients >---< Appointments
           - Instructors >---< Appointments

           I thought of solving these with an Enrollment join table like this:

           Offers ---< Appointments >--- Enrollment ---< Clients
           and: Enrollment ---< Instructors

           However, the Enrollment table would include the keys of Appointments, Clients and Instructors with each AppointmentID being able to have more than one ClientID and InstructorID. Is this correct? When adding 2 clients and 1 instructor to an appointment, is it correct that two records would be created in the Enrollment table:

           - AppointmentID1 ClientID1 InstructorID1
           - AppointmentID1 ClientID2 InstructorID1

           Thanks for any suggestions,
           Thomas

        • 1. Re: Join table connected to more than two other tables (TOs)
          davidanders

               Are the offers, just a reduced price for all clients in each appointment?

               Or is tracking the success of offers from various locations (newspaper, online ad, etc) part of the data you want to evaluate?

               In inventories it is common to use a lookup for a product and price [ProductsDB] and transfer today's info into a inventory lineitem record [LineItemDB]

               Then, if the product price changes tomorrow, the price charged today does not change.

               The first five links here may be helpful (especially the white paper)
          http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

               JoinTables allow viewing data in a many to many relationship.

               Joining two JoinTables with a JoinTable is not common, but is done.

          • 2. Re: Join table connected to more than two other tables (TOs)
            philmodjunk

                 Hmmm, maybe:

                 Clients----<Enrollment>------appointments>-----InstructorTeam-----<TeamRoster>------Instructors

                 Clients::__pkClientID = Enrollment::_fkClientID
                 Appointments::__pkApptID = Enrollment::_fkApptID
                 InstructorTeam::__pkTeamID = Appointments::_fkTeamID

                 and so forth.


                 Do all clients with a specific oppointment get the same exact offer? Can more than one offer be linked to a given appointment? If the answers are yes, no, then you can also link offers to appointments by a __pkOfferID field.

                 appointments>----Offers

                 Otherwise, a more complex structure will be needed.

            • 3. Re: Join table connected to more than two other tables (TOs)
              ThomasM_1

                   Thanks for your comments.

                   [...]
                    
                   So far I have never seen or read about join tables connecting more than two table occurrences. Hence, I was not sure if this approach would be valid or not. From your comments I conclude that it is NOT valid as joining two join tables (David) is different from one join table including three IDs from three table occurrences. And Phil's design suggestion includes two join tables (Enrollment + TeamRoster) each resolving many-to-many relationships between two TOs.
                    
                   [...]
              • 4. Re: Join table connected to more than two other tables (TOs)
                philmodjunk

                     A join table that links more than two tables is sometimes called a "star join" table. While more of a challenge to work with they are a valid design element in many databasse systems. It just didin't seem like the correct set up for your situation.