Bridge table help

Hi folks

I work in Higher Education and I'm modelling a snapshot fact for academic research awards (grants essentially).

Now an award can have multiple award holders (employees) and award holders can hold multiple awards so I was going to create a bridge between the award fact and my employee dimension to model this. However, the business also wants to view the facts against the employee's department and job (which exist as separate dimensions as employees can have multiple jobs/departments, although we're only dealing with their primary ones in this case).

What's the best way to approach this? Do i create separate bridge tables linking the award fact to each dimension: employee, department, and job?

Thanks for any help

That is the cleanest approach. If you try to consolidate to a single bridge (one with three dimensional references) you run the risk of a M:M relationship if multiple employees are in the same department or job for a given award. You may consider adding an employee count (total and #for the job or department) to the job and department bridges for allocation/contribution calculations.

Great thanks for that; I appreciate the tip about adding the employee count on the job/dept bridges.