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.