database schema for timesheet

Question

Could someone help me with a rough database schema for a timesheet application where the i would be able to

1. 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

2. 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

EDIT: 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

Answer 1

A table for people(1)

A table for projects(2)

A table for bookings(3) - who did the work (FK into 1), what project did they work on (FK into 2), when did they do the work, how much work did they do.

Select sum(time_booked) from (3) where person equals (some id from 1) and project = (some ID from 2)

or

Select sum(time_booked) from (3) where person equals (some id from 1)

etc...

Answer 2

Sounds a bit like homework, but I'd probably start with something like this:

People 
  - PersonID (PK)
  - PersonName
  - Other fields

Projects
  - ProjectID (PK)
  - ProjectName
  - Other fields

WorkTime
  - TimeID (PK)
  - ProjectID (FK)
  - PersonID (FK)
  - StartTime
  - EndTime

Answer 3

Here is a rough sketch that will give you a good start:

Project
-------
ProjectId  PK
ProjectName varchar(200)

Employee
---------
EmployeeId  PK
EmployeeName (or first name/last name etc..)
// .. other employee attributes


ProjectTimesheet
----------------
ProjectTimesheetId PK
ProjectId          FK -> Project.ProjectId
EmployeeId         FK -> Employee.EmployeeId
StartTime          DATETIME
EndTime            DATETIME
Approved           bit

EDIT: As an alternative to the approved flag in each ProjectTimesheet row, you could instead separate out the approved status to a separate table. For example, to allow approval for an employee's timesheet over a given period, a manager would add an approval entry to the Approval table:

Approval
--------
ApprovalID    PK
EmployeeId    FK -> Employee.EmployeeId
StartTime     DATETIME
EndTime       DATETIME
ApprovedBy    FK -> Employee.EmployeeId (e.g. the manager)
ApprovedDate  timestamp  // date the approval was registered

Answer 4

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)

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.

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)

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 row brings the TimeSheetSegment and WorkSegment rows together. The EmployeeID, PayrollCycleID index is unique.

Approval
- TimeSheetID (PK)
- PayrollCycleID (FK)
- SubmittedTimestamp
- ApproverID (FK)
- ApprovedTimestamp

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

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.