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

The following code is taken from the ]project-open[ open-source system in PostgreSQL syntax and edited.

Please note the "conf_objects" table with approval/confirmation information. When the user "submits" a time sheet, all submitted hours are assigned to a new conf_object. It's the job of the supervisor to set the status of the conf_object to "approved". Both the supervisor or the user may delete the conf_object at any time, which will will mark the hours as "not submitted" (hour.conf_object_id = NULL) again, so that these hours will end up on the "Hours Not Submitted" report.

CREATE TABLE projects (
    project_id                      integer constraint projects_pk primary key,
    project_name                    text not null,
    parent_id                       integer constraint projects_parent_fk references projects,
[...]
    project_type_id                 integer not null constraint projects_prj_type_fk references categories,
    project_status_id               integer not null constraint projects_prj_status_fk references categories,
    description                     text,
    start_date                      timestamptz,
    end_date                        timestamptz
);


CREATE TABLE users (
    user_id integer constraint users_pk primary key,
    first_names text,
    last_name text
[...]
);

-- Confirmation (=approval) objects
CREATE TABLE conf_objects (
    conf_id         integer constraint conf_id_pk primary key,
    conf_status_id  integer constraint conf_status_nn not null
);


CREATE TABLE hours (
    user_id                 integer constraint hours_user_id_nn not null constraint hours_user_id_fk references users,
    project_id              integer constraint hours_project_id_nn not null constraint hours_project_id_fk references projects,
    day                     date constraint hours_day_nn not null,
    hours                   numeric(5,2) not null,
[...]
    note                    text,
    conf_object_id          integer constraint hours_conf_object_fk references conf_objects
);

The original ]po[ SQL statements are included in the ~/packages/intranet-*/sql/postgresql/intranet-*-create.sql creation scripts.

Answer 2

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.

Answer 3

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 4

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 5

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