I have a timesheet application in Asp.Net MVC, but the question should be general database design:
I have a number of related tables, Customer, Project, Task, Employee, TimeSegment. Now, I want to be able to relate Tasks to Employees as "resources". And for each resource I want to be able to have information about work percentage assigned to an employee for a specific task. But I can't wrap my head around it. As far as I can see it can't simply be a many-to-many relationship between Task and Employee, because where would I put the percentage field then? If I put it on the employee, how would it be related to a specific task?
It seems like it would be a simple problem, but I can't figure it out, and I should mention my database skills are rather rudimentary. So any help would be greatly appreciated!
EDIT: Ok, several of you answered some variant on making a separate table for "Resources" with Employee_Id and Task_Id and WorkPercentage. But should I manually update the Employee_Id and Task_Id each time I add a task then? Because I first understood these answers as a hint that I should add a many-to-many relationship between Employee and Task and then add a field for WorkPercentage in the junction table (EmployeeTask). I tried that, but I immediately ran into problems when trying to add an employee, which demanded a list of EmployeeTasks and complained that there couldn't be a null value for a non-nullable field. But that wasn't how I should do it then? Rather a separate table manually updated with each field value...?
Please explain if possible exactly how to go about handling the solution several of you proposed (i.e. in terms of any relationships). Thanks!
Do you mean 'Track how much of this task has been assigned to Employee A'?
If so, you can create a new table that holds a task_id, an employee_id, and a percentage of the work.
Cleanest way to handle it would be: Each time a new task is created add in a record with 100% assigned to no-one (e.g. employee id of 0). Then whenever someone gets assigned, create a new record for that employee and the % they've just been given and update the original record to take away that amount.
That way you can also easily track any under-resourced tasks.