I am trying to determine the best model for the following case:
- There are multiple offices
- Each office has multiple staff, each of which have a role (chief, deputy chief, etc)
- Each office also has many projects
- Each project is managed by one or more staff (project manager, assistant project manager, deputy project manager, etc)
so, for example, an office has an office head, deputy head, etc. They also have project managers (which could be the same as office head) and sub managers that run projects.
I want to have a design where we can tag office employees to an office as well as tag employees that are project managers to a project. Here is what I have:
I have an office table I have a project table I have a office_project table
I have an employee table I have a role table
I have an office_employee table I have an employee_role table
The question is, what is the best way to tie the project table to the employee table? I assume I should have one employee table, or does it make sense to have a table to employees and a table for project managers, each with their own roles table? It doesn't seem that way. And, the way it is now, I can tie an office to a project, and see all the project employees, but not all office project managers would be associated with a given project.
If you need an ERD design of what I have, please let me know.