I am having trouble designing a schema in which there are two models an employee and projects. I am storing employees details in a table structure as:
id | employee | report_to
wherein employee table store the employee id and report_to store the id of the employee to whom the employee reports.
I have another table in which we store diff projects created by employees.
Id | project_name | employee_id
Now if I need to get projects done by employees who report to me I can easily get the list and also get the projects.
But the concern is eventually over time the employee reporting manager changes and hence along with that all the project that falls under let's say project manager 'a' also changes.
How to handle this kind of a problem in database. can this be designed in such a manner so that the projects which were initiated during that time remains under that particular manager only?