Schema database schema

Question

Can someone help me with a rough database schema for a schedule application where I could

• Keep hours a day for a period of time (2 weeks) for different projects. Ex person A can deliver 3 hours for projectA and 4 hours for projectB on the same day

• Make it easy to get reports on the total number of hours set for the project, or to get the total number of hours for all projects by a specific person

EDIT: Another requirement is that each schedule for a specific period of time for each person must have a field indicating that the person has sent the schedule and once again said that it is approved

Answer 1

Borrowing from Eric Petroel and 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 the WorkSegment is ProjectID, Date. The second WorkSegment index is EmployeeID, Date. These indexes are not unique. This means that a person can work on a project more than once in one day. Indexes allow you to report hours of work from a project or person.

Each WorkSegment line is intended for one time segment, one day, one project. Each employee has as many WorkSegment lines as needed to describe their payroll cycle.

TimeSheetSegment
- TimeSheetSegmentID (PK)
- ProjectId (FK)
- EmployeeId (FK)
- PayrollCycleID (FK)

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

TimeSheet
- TimeSheetID (PK)
- EmployeeID (IX)
- PayrollCycleID (IX)

TimeSheet TimeSheetSegment WorkSegment. EmployeeID, PayrollCycleID .

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

"" , . TimeSheet. , , , , TimeSheet.

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

PayrollCycle , WorkSegment TimeSheetSegment .

Answer 2

, :

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: ProjectTimesheet . , , :

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 3

(1)

(2)

(3) - (FK 1), (FK 2), , .

(time_booked) (3), ( 1) project = ( 2)

(time_booked) (3), ( 1)

....

Answer 4

, , , - :

People 
  - PersonID (PK)
  - PersonName
  - Other fields

Projects
  - ProjectID (PK)
  - ProjectName
  - Other fields

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

Answer 5

] project-open [ PostgreSQL .

"conf_objects" /. "" , conf_object. conf_object "". , conf_object , " " (hour.conf_object_id = NULL), " ".

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
);

] po [ SQL ~/packages/intranet - */sql/postgresql/intranet - * - create.sql.