I have a problem I've been trying to tackle but to no avail.
An employee can be transferred to a different supervisor in the middle of the month.
5/1 - 5/15
5/16 - 5/31
In the table above, Employees A,B,C were assigned to Supervisor A from 5/1 - 5/15. All their stats should be retained under Supervisor A. From 5/16 - 5/31, they were assigned to Supervisor B. Likewise, all their stats from then on should fall under Supervisor B.
I can't seem to find the solution to this. Right now, I have the relationships set up like so:
The way I have it set up right now does not take into account supervisor changes within the month. I've looked into many-to-many relationships using a bridge table but still can't get it to work.
As you can see, a person can be moved to a different supervisor within the month. With the way I have it set up right now, if a person moves to another team within the month, his/her score will transfer as well. We don't want that. The score under Supervisor A should stay under that supervisor.
I would appreciate if you can guide me as to how to design the data model for this. Right now I have two lookup tables (Employee and Date) and one data table (survey data).