时间表的数据库架构 [英] database schema for timesheet
问题描述
有人可以帮我提供一个时间表应用程序的粗略数据库架构吗？
Could someone help me with a rough database schema for a timesheet application where the i would be able to
-
存储时间每天（一段时间（2周））用于不同的项目。前人A可以在同一天为projectA放置3个小时，为projectB放置4个小时
Store hours per day for a time period ( 2 weeks ) for different projects. Ex person A can put 3 hours for projectA and 4 hours for projectB on the same day
制作它，这样很容易获得总工时的报告放置一个项目，或获得某个人在所有项目上的总工时
Make it so that its is easy to get a reports on total hours put for a project, or to get total hours on all projects by a certain person
编辑：另一个要求是每个人在特定时间段内的每个时间表都需要有一个字段，指示该人已提交该时间表，而另一个说法是已批准该时间表。
Another requirement would be that each timesheet for a particular time period for every person needs to have a field indicating that the person has submitted the timesheet and another saying that it has been approved
推荐答案
从Eric Petroelje&借用mdma：
Borrowing from Eric Petroelje & mdma:
Employee
- EmployeeID (PK)
- EmployeeName
- Other_fields
Project
- ProjectID (PK)
- ProjectName
- Other_fields
WorkSegment
- WorkSegmentID (PK)
- ProjectID (IX1)
- EmployeeID (IX2)
- Date (IX1, IX2)
- StartTime
- EndTime
- PayrollCycleID (FK)
WorkSegment的第一个索引是ProjectID，即Date。 WorkSegment的第二个索引是EmployeeID，日期。这些索引不是唯一的。这样一来，一个人一天可以做一个以上的项目。索引允许报告项目或人员的工作时间。
The first index of WorkSegment is ProjectID, Date. The second index of WorkSegment is EmployeeID, Date. These indexes are not unique. This is so a person can work on a project more than once in one day. The indexes allow for reporting on hours worked by project or by person.
每个WorkSegment行是一段时间，一天，一个项目。每个员工都有描述他的工资周期所需的WorkSegment行。
Each WorkSegment row is for one segment of time, one day, one project. Each employee has as many WorkSegment rows as is needed to describe his payroll cycle.
TimeSheetSegment
- TimeSheetSegmentID (PK)
- ProjectId (FK)
- EmployeeId (FK)
- PayrollCycleID (FK)
在ProjectID，EmployeeID和PayrollCycleID上具有唯一索引。员工在工资发放周期中为其工作的每个项目都有一个TimeSheetSegment行。
There is a unique index on ProjectID, EmployeeID, and PayrollCycleID. There is one TimeSheetSegment row for each project that an employee works for during a payroll cycle.
TimeSheet
- TimeSheetID (PK)
- EmployeeID (IX)
- PayrollCycleID (IX)
The TimeSheet行将TimeSheetSegment和WorkSegment行组合在一起。 EmployeeID，PayrollCycleID索引是唯一的。
The TimeSheet row brings the TimeSheetSegment and WorkSegment rows together. The EmployeeID, PayrollCycleID index is unique.
Approval
- TimeSheetID (PK)
- PayrollCycleID (FK)
- SubmittedTimestamp
- ApproverID (FK)
- ApprovedTimestamp
提交时间表后，将创建批准行。这些字段可以是TimeSheet表的一部分。我用四阶规范化将它们划分出来，因为批准表可能具有与TimeSheet表不同的数据库访问权限。
The Approval row is created when the time sheet is submitted. These fields could be part of the TimeSheet table. I broke them out with a fourth-order normalization because the Approval table is likely to have different database access permissions than the TimeSheet table.
PayrollCycle
- PayrollCycleID (PK)
- PayrollCycleYear
- PayrollCycleNumber
- StartDate
- EndDate
- DirectDepositDate
- CheckDate
- Other_fields
PayrollCycle表将某些日期字段归一化，并提供一个使它成为整数的整数键更轻松地将WorkSegment和TimeSheetSegment行放在一起以创建一致的时间表。
The PayrollCycle table normalizes some of the date fields, and provides an integer key that makes it easier to pull together the WorkSegment and TimeSheetSegment rows to make a coherent time sheet.
这篇关于时间表的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
