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).
assignment be like this
[emp_id, assign_id, level, week]
level indicates to which table (task/phase/project) the generic
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.