Help with schema design [duplicate]

Question

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?

Answer 1

If your interest is explicitly to keep track of the direct manager of the employee, then the simplest solution may be to add manager_id to the project table, and populate that from employee.report_to when a project row is created. It's not worth setting up a full-fledged history mechanism just to track this specific relation.

If, on the other hand, this is intended to allow anyone to track down the projects they or their subordinates (at any level) started while they were their subordinates, then you'll need a full history mechanism. Check out the link mustaccio noted in the comments, to the question Best way to capture historical changes between levels in a dimensional hierarchy.