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?
Best Answer
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.