Question 226492 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Proper way to store all days of every month in database

I want to store such fields in the table as: the ID of the employee, his last name and first name, and how many hours he worked on each day of the month. For example, March 1 – 6 hours, March 2 – 4 hours, March 3 – 4 hours. The example I gave in the table below is how I imagine it, is there a way to store this data in a more convenient way to do it in PostgreSQL? And how to handle the situation when there are 30, 31, 28, or 29 days in the month?

ID Person`s Full name 1.03.2023 2.03.2023 3.03.23
1 Bill Gates 6 7 6
2 Steve Jobs 5 7 6
Total worked hours
19
19
Tags:

2

Answers


  1. 0

    You’d use two tables: one for employees, and another to store work hours (and that table references its master):

    create table employee
  (emp_id    number primary key,
   full_name varchar(30)
  );

create table work
  (work_id   number primary key,
   emp_id    number references employee (emp_id),
   c_date    date,
   hours     number
  );

    Doing so, you’d enter as many rows into WORK table as necessary. For example, for dates when someone doesn’t work (weekends, holidays, vacations), you wouldn’t enter anything at all.

    Certainly, apply any other constraints you want (valid number of hours, split full name into first and last name, etc.), but that’s the general idea of how to do it.

    Login or Signup to reply.
  2. 0

    All you need is:

    CREATE TABLE PersonsWorkedHours (
    PersonId int  NOT NULL,
    Date     date NOT NULL,
    Hours    real NOT NULL,
    
    CONSTRAINT PK_PersonsWorkedHours PRIMARY KEY ( PersonId, Date ), /* This composite PK ensures that PersonId+Date is always unique, so the same Person cannot have 2 or more rows with the same Date value */
    CONSTRAINT FK_Hours_to_People    FOREIGN KEY ( PersonId ) REFERENCES People ( PersonId ),
    CONSTRAINT UK_PersonsWorkedHours UNIQUE KEY  ( Date, PersonId ), /* this is a secondary-key for the benefit of Date-first queries, instead of PersonId-first queries. */

    CONSTRAINT CK_HoursPerDay CHECK ( Hours >= 0 AND Hours < 24 ),
    
    INDEX FX_People ( PersonId )
)

    And how to handle the situation when there are 30, 31, 28, or 29 days in the month?

    You don’t need to handle it: the date type handles this for you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search