How to manage many-to-many relationships when there is also a hierarchy among the many?

Question

I have these entities – employee, task, phase, project – and these relationships:

• project 1:m phase
• phase 1:m task
• employee m:m task, phase, project

The first two relationships form a simple part-of hierarchy.

The semantics of the third relationship are slightly complex. An employee, in any given week, can be assigned to any or all of the following:

• zero or more tasks
• zero or more phases
• zero or more projects

(Being assigned to nothing at all, though, is not recorded!) In other words, the level of detail of an employee assignment can vary. Semantically, at least, it is impossible to be assigned to a "part" entity without also being assigned to the greater entity. However, being assigned to a "whole" does not imply assignment to any of the "parts".

My current table design is to have a table for each entity and a junction table for the m:m relationship:

employee   [emp_id,   etc]
task       [task_id,  phase_id, etc]
phase      [phase_id, proj_id,  etc]
project    [proj_id,  etc]
assignment [emp_id,   task_id,  phase_id, proj_id, week]

However, given that a task always belongs to a phase and a phase always belongs to a project, how should the foreign keys in assignment be valued? Do I choose one of these?

• Only the id corresponding to the lowest known entity in the project-phase-task hierarchy gets a value, the higher entity ids get nulls. Problem: Those nulls don’t seem right.
• All the ids the corresponding to the lowest known entity and up get values. Problem: The non-null ids seem redundant (non-normalized).

Or should assignment be like this

[emp_id, assign_id, level, week]

where level indicates to which table (task/phase/project) the generic assign_id refers? Problem: Theoretically, assign_id might need to be of three different types. (Though, actually, they're all long integers, in my situation.)

Is this a “design pattern”? That is, is it an example of semantics that the relational DB model doesn’t readily handle? Does it have a name? What is the best practice for handling the situation?

I'm using MS Access but I'm interested to know how solutions might vary across different relational DBMSs.

Answer 1

Martin,

If I am understanding correctly, the employee can be associated with one or more tasks, phases, and projects. This implies a many to many relationship between an employee and a task, phase, or project. If this is the case, throw out the idea of the assignment table completely. Instead, create three different junction tables that can be used to establish the many to many relationships: EmployeeTask, EmployeePhase, and EmployeeProject.

The Task, Phase, and Project tables will allow for relationships to be established as you have defined in your post. The three junction tables will enable an employee to be included in the hierarchy at any of the three levels.

Your SELECT statements will require LEFT OUTER JOINS with the junction tables to pull back the appropriate employee information, which is fine. Adding the junction tables will provide a much more normalized database design.