Accommodating hierarchical data in SQL Server 2005 database design
- by Remnant
Context
I am fairly new to database design (=know the basics) and am grappling with how best to design my database for a project I am currently working on.
In short, my database will keep a log of which employees have attended certain health and safety courses throughout the year. There are multiple types of course e.g. moving objects, fire safety, hygiene etc.
In terms of my database design I need to accommodate the following:
Each location can have multiple
divisions
Each division can have multiple
departments
Each department can have multiple
functions
Each function can have multiple job
roles
Each job role can have different
course requirements
Also note that the structure at each location may not be the same e.g. the departments within divisions are not the same across locations and the functions within departments may also differ.
Edit - updated to better articulate problem
Let's assume I am just looking at Location, Division and Department and I have my database as follows:
LocationTable DivisionTable DepartmentTable
LocationID(PK) DivisionID(PK) DepartmentID(PK)
LocationName DivisionName DepartmentName
There is a many-to-many relationship between Locations and Divisions and also between Departments and Divisions.
Suppose I set up a 'Junction Table' as follows:
Location_Division
LocationID(FK)
DivisionID(FK)
Using Location_Division I could easily pull back the Divisions for any Location.
However, suppose I want to pull back all departments for a given Division in a given Location.
If I set up another 'Junction Table' for Division and Department then I can't see how I would differentiate Division by Location?
Division_Department
DivisionID(FK)
DepartmentID(FK)
Location_Division Division_Department
LocationID DivisionID DivisionID DepartmentID
1 1 1 1
1 2 1 2
2 1 2 1
2 2 2 2
Do I need to expand the number of columns in my 'Junction Table' e.g.
Location_Division_Department
LocationID(FK)
DivisionID(FK)
DepartmentID(FK)
Location_Division_Department
LocationID DivisionID DepartmentID
1 1 1
1 1 2
1 1 3
2 1 1
2 1 2
2 1 3
