3

I'm creating a simple dabase design with three entities and have a question about the relationships between my Employee and Task tables.

The thing is:

  • A task has an owner, which is represented by the "createdBy" attribute, which is a FK of empID;
  • A task also has an assignee, which is represented by the "assignedTo" attribute, which is also a FK of empID;

An employee can have multiple tasks, even if there was only one field using empID as a FK. But, you could say a task can have multiple employees, eventhough it can only have two. This would make it a many to many relationship, thus require an additional table.

However, because the number of employees that a task can have is fixed, because it has two designated fields, I feel like this is not the proper solution. Still, it feels strange to have multiple one to many relationships.

Could someone tell me whether or not it is possible to have the relationships the way they are now? Or would I need to treat it as a many to many relationship anyway?

ERD

Improve this question
2
  • Is the task always assigned to someone? What about when it's finished? Presently you indicate that each task has a between 1 and 1 assignee.
    – mendosi
    Commented Nov 7, 2016 at 2:34
  • Yes, a task always has a creator and an assignee; upon creation of a task, by default, it is assigned to the employee that created it.
    – JDL
    Commented Nov 7, 2016 at 20:27

2 Answers 2

Reset to default
3

If a task can have multiple assignees, then you may want a many-to-many table between employee and tasks. However, the current model works well if the task can only be assigned to one employee at a time and you don't want to record assignment history.

It is not uncommon to have multiple one-to-many relationships between two tables. You have correctly modeled the case you have described. However, I generally separate created-by and owners relations. This permits someone other than the owner to enter proposals and tasks. A created-by fields is typically used as an audit field which should not be modified, while the owner field usually would be modifiable.

You do have a many-to-many relationship between employees and proposals. This resolved by the tasks table.

Improve this answer
3
  • A task can only have one assignee, tracking changes is not required. Most important is the ability to see who eventually finished a task. After a task is finished, the assignee can't be changed anymore. As to separating created by and owner: it's most important to be able to see what department a proposal belongs to, which can be done through the department attribute in the employee table.
    – JDL
    Commented Nov 7, 2016 at 20:32
  • @JDL If it is important to know which department a proposal belongs to, you may want to add department to the proposal. That way the proposals don't change departments when an employee is moved to a new department. It you don't need assignment history and a task can have only one assignee, then your model is good in that respect.
    – BillThor
    Commented Nov 10, 2016 at 21:20
  • @JDL Post clarifications and additional info as edits to your Question, not as comments.
    – Basil Bourque
    Commented Sep 14, 2018 at 4:52
1

Having multiple fields that reference another table doesn't make it a many-to-many relationship. You simply have multiple one-to-many relationships between the tables. Your diagram is correct.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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