SQL Server 2005 database design - many-to-many relationships with hierarchy
- by Remnant
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:
Divisions - Textile, Marketing (PK = DivisionID)
Departments - HR, Finance (PK = DeptID)
Functions - Payroll, Hiring, Audit, Tax, Accounts (PK = FunctionID)
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?
