How to model team performance with changing managers
I can't figure out a good design for this, and I'm hoping one of you can suggest something.
I need to display employee team performance metrics on a web interface. In my database I have the following tables:
- activities (I'm abstracting this to avoid getting into the business details)
What I need to do is be able to associate teams with managers (each manager will be represented by an employee ID in the employees table, along with non-management employees), in such a way that when a team's manager changes, it does not change the _history_ of that team. I'll explain...
Currently, I store the team manager IDs in the teams table. But every time I change a team's manager, from the point of view of the web reports the "new" manager has always been the team's manager.
I had thought of including a begin date and end date for each team, but figured this would make querying more difficult.