database design to preserve history

[allboutdB]

Hi there,

I am practicing database design , and i have come across a situation in a practice database that i am designing where i have 3 tables , EMPLOYEES, TEAM_MANAGER, SALES and i have a report for total sales for each team manager for each month. Initially i had one to many relationship between TEAM_MANAGER and EMPLOYEES and one to many between EMPLOYEES AND SALES but lets say an employee's team manager changes down the road and i want to preserve the history of the sales the employee made prior to team change and only reflect the sales of the employee under the new team, after the change. so to reflect this, would the relationship between TEAM_MANAGER AND EMPLOYEE become many to many? which ultimately will be resolved into 2 one to many relationship and the junction table would hold the employee id and team_manager id with a month attribute to identify which team_manager the employee belongs to for a given month, does this mean that even if the employee does not change team for lets say the first 6 months in year you still repeat the employee_id ,team_manager_id and month in the employee_team table, and i am still struggling to write a SQL statement for total sales for a team_manager with this new table in place. Can anyone please help, any help would be appreciated,

please see the attached data model

Thank you

Thank you

 

[HiTechCoach]

... and the junction table would hold the employee id and team_manager id with a month attribute to identify which team_manager the employee belongs to for a given month, ...

I would use two fields, start date and end date, on a single field for month.. This way you would need only a single record.

 

[allboutdB]

Makes sense

Is the model i made ok though or is there any other way to store the data?

Thanks again