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?