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

5
  • 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? Nov 24, 2015 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, 2015 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. Nov 24, 2015 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, 2015 at 20:26
  • Wait, what about the roles? Should I have a relationship between projects and employee_role?
    – jason
    Nov 24, 2015 at 20:35

1 Answer 1

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 and acknowledge you have read our privacy policy.

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