2 Replies Latest reply on Jun 11, 2015 2:52 PM by MattSelchow

    Many to many to some? Join table questions



      Many to many to some? Join table questions


      I'm having an issue how to setup this part of my solution and would appreciate some advice. 

      I have a Clients table and Workers table. I need to track data between them both one to one and one to many.

      For example, Clients have one of 4 different statuses: New, Pending, Cleared and Unqualified. Every new client is set to status New until they go through an approval process (Pending) and then Cleared. This happens before they see any Worker. At the moment the status field is in the Client record which allows all Workers to see that that Client is cleared.

      Later, a cleared Client makes an appointment with a Worker. After the appointment the Worker decides that the Client isn't Qualified. That Worker wants to set the Client's status to Unqualified - but just for themselves. The same client should still be Cleared for everyone else. 

      So I need to make a join table, take the ID fields from The Client and Worker as keys and make a status field. But now I'm tracking Client status in two places, with a new join table record for every Worker that decides to change Client status. I had a fleeting thought of creating join table records from the start so when there's a new client I loop through and make a record for every Worker in the system to match them up. That seems a little unwieldy to me but maybe that's the way to go.

      Any thoughts? Did I make myself clear? Thanks, I appreciate any input.



        • 1. Re: Many to many to some? Join table questions

          Sounds like two status fields are the way to go. The status field in the clients table is not recording the same exact data as that found in the join table record. When a record in the join table is created it can look up (auto-enter option to copy over) the current value of the client status field and then the worker can change the join table record as needed.

          The only complication will be if a client status can later be changed to another value.

          You might also simply keep the two fields completely separate, assigning values of new, pending, cleared to the client status field and use values of <empty> and "unqualified" in the client_worker join table field. Note that if you add "unqualified" to your list of value for the client status, you are recording something quite different than when a work updates the status in the join table. The worker's entry indicates "this client is unqualified for me". The same value in the client table would indicate: "this client is not qualified for any worker."

          • 2. Re: Many to many to some? Join table questions

            Thanks for the reply Phil. Everything you mentioned is exactly right. I'll use the client record as a global starting point where the status is as stated: New, Pending, Cleared and Unqualified (not qualified for any worker). When that client contacts an individual worker I'll make a record in the join table for that specific relationship and track status through that record going forward. 

            Thanks for helping me think it through.