Note

I have completely re-written my original post to better explain the issue I am trying to understand. I have tried to generalise the problem as much as possible.

Also, my thanks to the original people who responded. Hopefully this post makes things a little clearer.

Context

In short, I am struggling to understand the best way to design a small scale database to handle (what I perceive to be) multiple many-to-many relationships.

Imagine the following scenario for a company organisational structure:

             Textile Division                    Marketing Division
                    |                                     |
          ----------------------               ----------------------
          |                    |               |                    |
       HR Dept           Finance Dept        HR Dept           Finance Dept
          |                    |               |                    |
      ----------          ----------       ----------           ---------
     |          |         |        |       |        |           |       |
  Payroll     Hiring    Audit     Tax   Payroll   Hiring      Audit  Accounts
     |          |         |        |       |        |           |       |
    Emps      Emps       Emps     Emps    Emps     Emps        Emps    Emps    

NB: Emps denotes a list of employess that work in that area

When I first started with this issue I made four separate tables:

1. Divisions -> Textile, Marketing (PK = DivisionID)
2. Departments -> HR, Finance (PK = DeptID)
3. Functions -> Payroll, Hiring, Audit, Tax, Accounts (PK = FunctionID)
4. Employees -> List of all Employees (PK = EmployeeID)

The problem as I see it is that there are multiple many-to-many relationships i.e. many departments have many divisions and many functions have many departments.

Question

Giving the database structure above, suppose I wanted to do the following:

• Get all employees who work in the Payroll function of the Marketing Division

To do this I need to be able to differentiate between the two Payroll departments but I am not sure how this can be done?

I understand that I could build a 'Link / Junction' table between Departments and Functions so that I can retrieve which Functions are in which Departments. However, I would still need to differentiate the Division they belong to.

Research Effort

As you can see I am an abecedarian when it comes to database deisgn. I have spent the last two days resaerching this issue, traversing nested set models, adjacency models, reading that this issue is known not to be NP complete etc. I am sure there is a simple solution?