7 Replies Latest reply on Nov 9, 2010 12:49 PM by philmodjunk

    many to many self join?



      many to many self join?


      Help!  I've looked at this for hours and can't figure it out.   I am rewriting a FMP3 database that manages employees' shifts.

      Each employee will bid a shift that is based on a start time and desk, each desk has two start times in a day - example desk 1 has a shift for 1 person to bid that starts at 0430-1230 and desk 1 has a shift for 1 person to bid that starts at 1230-2030(this is a 24-hour operation that requires a body at every desk for at least 16 hours of the day).  After all employees have bid the shift they want to work for a 3 month period, I have a script that builds a record in a seperate database(called Exceptions) for each shift, each day for the duration of the 3 month period -- all good up to here.  All records have a Serial ID.   For the duration of the shift bid employees can change their shifts with other employees -- they can change part of their shift --- John works Sally's full shift or they work part of their shift - John works his shift from 0430-0830 and Sally works the second half of the shift 0830-1230.  There can be as many as 3 people working 1 shift.  1 person can work 'many' shifts in a day and 1 shift can have 'many' employees working in a day.  The employees need to be able to make these changes themselves so it has to be easy for them to manuever.  

      I have:

      Employee  table - employee's personal information

      Shift bid table - 60 shifts available for employees to select the shift they want

      Exceptions table - A record created for each employee, each day with the shift they have bid - ExcSerialID is the Primary Key

      I currently have a self join in Exceptions with ExcSerialID to FExcSerial ID.  When an employee selects a person to work for, a new record is created with ExcSerialID and I set the FExcSerialID to the parent's ID, so I know the parent/child records.  If I set the match fields up for name = gname, date = gdate then the employee can change gname and go to another persons record without a problem(via portal).  If I set a match with the ID's then and try to use the gname/gate to select what I want I get the first match to that name -- not the name/date match. 

      In the past I have worked with the concatenated matches with ease have been able to make just about anything work but I am trying to do this 'the right way' with the SerialID's and I can't get my brain to think like this.  I know I am missing some structural piece and I think it is a join table, but I have not worked with a join table and serialID's before.  The FMP3 databases used a join table but not with SerialID's --- it set things as it needed to for viewing.

      Thanks for any guidance you can give.


        • 1. Re: many to many self join?

          A many to many self join with a join table would look like this in your relationship graph:

          TableA----<JoinTable>--2nd table occurence of tableA

          TableA::PrimaryKey = JoinTable::ForeignKey1
          TableA 2::PrimaryKey = JoinTable::ForeignKey2

          Typically, you'd manage this system by placing a portal to JoinTable on a layout for TableA and/or TableA 2. If you place a portal to the join table on a layout for TableA, you can add fields from TableA 2 to the portal to provide more information about the linked records.

          • 2. Re: many to many self join?

            In order for the Join Table to have the Foreign Key, I would have to have duplicate records in the Join table?  

            • 3. Re: many to many self join?

              The join table need not have any fields other than the two key fields, that I gave the generic names "ForeignKey1" and "ForeignKey2", so I'm not sure what you mean by "duplicate records".

              I had trouble following your original post, so I responded with a generalized description of how you implement a many to many self join relationship with a join table in hopes that would help you out. I'm not totally certain you actually have a many to many relationship described there.

              You might want to describe in more detail how you want to use the Exceptions table and how it relates to the Shift Bid table. I'm sure that this is something that's very clear to you as it's your design, but I find I can't quite put it all together from what I've read so far.

              • 4. Re: many to many self join?

                Exceptions has a record for each employee for each day of the month with the desk and hours they are scheduled are to work.  the records will look like this:

                exc001  - 10/1/2010 -  penny - desk 1 - 0430-1230

                exc002 -  10/2/2010 - penny - desk 1 - 0430-1230

                When I create a self-join relationship using date/name/desk for the portal to view and use a global date field to move through different dates  I see what I would expect - the record for the date selected in the global date field.

                If I use the SerialID (exc001) as the match field and try to use a global date to view a different date, the relationship doesn't see the match for the new date.  

                Maybe my question is:  if you are using a SerialID to match records, how do you create the relationship so that the user can select records based on other fields within the record? 




                • 5. Re: many to many self join?

                  You create additional table occurrences to the same table so that you can define different relationships between them.

                  Tutorial: What are Table Occurrences?

                  • 6. Re: many to many self join?

                    Got that. BUT.....

                    If 1 contact works out of several offices and has several fax nmbers then he would have several Contact records, each record with the fax number at each office.

                    record C1 = Joe's philadelphia phone numbers

                    record c2 = Joe's New York phone numbers

                    When I create a relationship with contactID - contactID I only get the record that has the one match in the portal.   I want to be able to see Joe's fax number at each office in one portal.  I also want to be able to see everyone that has the same fax number at 1 office.


                    • 7. Re: many to many self join?

                      I'd split that contact data into two tables: Contacts, PhoneNumbers

                      Contacts::ContactID = PhoneNumbers::ContactID

                      Now you have just one Contact ID to link to, but you can get to all the phone numbers. With a bit of thought as to which fields to define in this table, you can use this PhoneNumbers table to list all phone numbers, emails, and social networking URLs you need and this can now be a very flexible number of records.