0

I am trying to determine the best model for the following case:

  1. There are multiple offices
  2. Each office has multiple staff, each of which have a role (chief, deputy chief, etc)
  3. Each office also has many projects
  4. 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.

thanks

  • Is there a reason you wouldn't want to just have a relationship between the project and employees, letting the employee office hierarchy take care of the project-office relationship? – Thomas Cleberg Nov 24 '15 at 20:14
  • Do you mean remove the relationship between office and employee and make one between employee and project? I'm not sure I follow how that would keep the employee hierarchy. Can you explain more? – jason Nov 24 '15 at 20:18
  • Sure- instead of having an explicit project-office relationship, you just have an office-employee relationship and an employee-project relationship. Then, in order to find projects related to an office, you just go for projects related to employees related to an office. – Thomas Cleberg Nov 24 '15 at 20:20
  • I think that might work! Thanks. If you add this as a response, I'll mark it as correct. – jason Nov 24 '15 at 20:26
  • Wait, what about the roles? Should I have a relationship between projects and employee_role? – jason Nov 24 '15 at 20:35
2

I would suggest that instead of having an explicit office-project relationship that you set up an employee-project relationship and allow the existing office-employee relationship to take care of this problem naturally.

From your question, it seems as though many employees can be associated with a single project and a single employee can be associated with many projects. In this case, you'll want a linking table of the form:

    employee_project
    -------------------
    employee_id | project_id

In the event that the relationship is more complex- say, that an employee participates in a project via one of their roles, you may want to move the relationship to refer to that point, for example:

    project_employee_role
    -------------------
    employee_role_id | project_id

if there is a surrogate employee_role_id

or

   project_employee_role
   -------------------
   employee_id | role_id | project_id

If not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.