时间表应用程序的数据库设计问题
我在 Asp.Net MVC 中有一个时间表应用程序，但问题应该是一般数据库设计：
我有许多相关的表，客户、项目、任务、员工、时间段。现在，我希望能够将任务与员工作为“资源”联系起来。对于每个资源，我希望能够获得有关分配给员工执行特定任务的工作百分比的信息。但我无法理解它。据我所知，任务和员工之间不能简单地是多对多关系，因为那么我要把百分比字段放在哪里呢？如果我把它放在员工身上，它与特定任务有何关系？
看起来这将是一个简单的问题，但我无法解决，而且我应该提到我的数据库技能相当初级。因此，任何帮助将不胜感激！
编辑： 好的，你们中的一些人回答了使用 Employee_Id、Task_Id 和 WorkPercentage 为“资源”制作单独的表的一些变体。但是我应该在每次添加任务时手动更新 Employee_Id 和 Task_Id 吗？因为我首先将这些答案理解为暗示我应该在 Employee 和 Task 之间添加多对多关系，然后在连接表 (EmployeeTask) 中添加 WorkPercentage 字段。我尝试过这样做，但在尝试添加员工时立即遇到了问题，该员工需要 EmployeeTasks 列表，并抱怨不可为空的字段不能有空值。但那不是我应该做的吗？而是使用每个字段值手动更新一个单独的表......？
如果可能的话，请准确解释如何处理你们提出的解决方案（即就任何关系而言）。谢谢！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的意思是“跟踪已分配给员工 A 的任务量”吗？
如果是这样，您可以创建一个新表来保存 task_id、employee_id 和工作百分比。
最简洁的处理方法是：每次创建新任务时，添加一条记录，并将 100% 分配给无人（例如，员工 ID 为 0）。然后，每当有人被分配时，为该员工以及他们刚刚分配的百分比创建一个新记录，并更新原始记录以删除该金额。
这样您还可以轻松跟踪任何资源不足的任务。
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.
也许是一个名为“资源”的新表，其中包含 id_employee、id_task 和 work_percentage 列？
对于要建模的每个事物，您可以使用多个表。
Maybe a new table called "Resources" with columns for id_employee, id_task, and work_percentage?
You can use more than one table per thing you are trying to model.
您可能需要一个表（例如 ProjectResources）来记录哪些员工是每个项目的资源。然后可能是一个项目任务表，您可以在其中记录项目密钥、任务密钥、资源密钥以及您需要的任何其他详细信息。
就我个人而言，我发现按时间而不是百分比来估计和跟踪任务更有用。最后，我想知道“任务 'n' 已完成，花了 3 个小时”，而不仅仅是“任务 'n' 已完成”。
You might need a table, say ProjectResources, to just record which employees are resources for each project. Then maybe a table of ProjectTasks, where you'd record the project key, task key, resource key, and whatever other details you need.
Personally, I find it more useful to estimate and track tasks by time rather than by percentages. At the end, I like to know "Task 'n' is finished and it took 3 hours" rather than just "Task 'n' is finished."