1

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.

  • So with the project | phase | task hierarchy, is it the case that assignment to (eg) a phase automatically connotes assignment to all the contained tasks? Or could an employee be assigned to a phase and just one of the contained tasks? – AakashM Apr 7 '15 at 8:48
  • ... if the former, you could use any of the standard approaches for storing trees in SQL, and the assignment would be against the tree node – AakashM Apr 7 '15 at 14:48
  • @AakashM - I just added this to the Q: (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". – Martin F Apr 7 '15 at 15:35
  • an employee can be assigned to a phase and zero or more of the contained tasks – Martin F Apr 7 '15 at 15:40
  • Is mine one of "the standard approaches for storing trees" -- a part references its parent? Should i know of the others, in this context? – Martin F Apr 7 '15 at 15:42
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.

  • That still leaves the problem of when an employee is assigned to a task, there is nothing to stop same employee from also being assigned to the task's phase, or the phase's project. I think whatever solution is chosen, careful coding will be required to preserve the semantics. In other words, it is a solution that, like mine, still has a choice as to how to manage presence/absence of values. – Martin F Apr 7 '15 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.