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