database schema for timesheet -


Does anyone could help me with a rough database schema for Taimsed app where I

  1. Store hours per day for a different time period (2 weeks) for different projects, person can spend 3 hours for A project A and 4 hours for ProjectBable on the same day.

  2. Make it so that it is easy to get reports on the total hours for the project. , Or to get total hours on all projects by a certain person

EDIT: Another requirement would be for every person for every particular title sheet. should be an area that indicates that the person has Taimsshit submitted and the other saying that it has been approved

Lending from Eric Petrolege & MDMA: 

  employee - EmployeeID (AP) - EmployeeName - Other_fields project - ProjectID (AP) - ProjectName - Other_fields WorkSegment - WorkSegmentID (AP) - ProjectID (IX1) - EmployeeID (IX2) - Date (IX1, IX2) - Starttime - Endtime - Payrollkick (FK)

The first index of the workspace, the project ID, is the date. Second index of workgroup is employee, date These indexes are not unique, that is because a person can work on one project more than once in a day. Allow indexed project or report on hours made by the person.

Each workspace line is for a segment of time, one day, one project. It is necessary to describe your payroll cycle as many WorkSegment rows as each employee. 

  TimeSheetSegment - TimeSheetSegmentID (P) - ProjectId (FK) - EmployeeID (FK) - PayrollCycleID (FK)

There is a unique index on ProjectID, EmployeeID and PayrollCycleID. There is a TimeSheetSegment line for each project that an employee works for during a payroll cycle 

  timesheet -. TimeSheetID (AP) - EmployeeID (IX) - PayrollCycleID (IX)

Time Taimset segment sheet line and Vrksagment lines get together. EmployeeID, PayrollCycleID Index is unique 

  acceptance - TimeSheetID (P) - PayrollCycleID (FK) - SubmittedTimestamp - ApproverID (FK) - ApprovedTimestamp

The acceptance line is created when the time sheet is submitted. These fields can be part of Taimshit table I they broke out with a fourth-order general approval because the table is likely to be different database access permissions than timesheet table 

  PayrollCycle -. PayrollCycleID (AP) - PayrollCycleYear - PayrollCycleNumber - StartDate - EndDate - DirectDepositDate - CheckDate - Other_fields

PayrollCycle general some field time table, and an integer key easy WorkSegment together TimeSheetSegment provides to create a consistent time for drawing lines that the sheet provides.


Comments

Post a Comment