designing database where users can have multiple positions 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.

thanks
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.