Creating views with joins in PostgreSQL

Question

For a small shift management project I decided to make I'm trying to make a weekly schedule of shifts for our employees, based on a 3 shifts per day schedule, where 1 shift can hold more than one employee.

I've created an employee table and a work_day table that holds the date of the shift and 3 join tables for each shift of the day.

CREATE TABLE employee(
    id SERIAL PRIMARY KEY,
    "name" VARCHAR(128) NOT NULL,
    archived BOOLEAN DEFAULT FALSE
);

CREATE TABLE work_day(
    id SERIAL PRIMARY KEY,
    "date" DATE NOT NULL UNIQUE
);

CREATE TABLE morning_shift(
  employee_id INTEGER FOREIGN KEY REFERENCES employee(id),
  shift_id INTEGER FOREIGN KEY REFERENCES work_day(id),
  PRIMARY KEY(employee_id, shift_id)  
);


CREATE TABLE evening_shift(
  employee_id INTEGER FOREIGN KEY REFERENCES employee(id),
  shift_id INTEGER FOREIGN KEY REFERENCES work_day(id),
  PRIMARY KEY(employee_id, shift_id)  
);


CREATE TABLE night_shift(
  employee_id INTEGER FOREIGN KEY REFERENCES employee(id),
  shift_id INTEGER FOREIGN KEY REFERENCES work_day(id),
  PRIMARY KEY(employee_id, shift_id)  
);

The plan I had in mind is to create a view that would materialize a presentation of a work day:

Date

Morning Shift(name1, name2)

Evening Shift(name3, name4)

Night Shift(name5)

That way I can query whole work days as objects in my projects. The issue is I come with very little experience in databases and it has been proved way more difficult that I had even imagined. I've been trying for the last couple of days and finally gave up on my ego and now I seek your humble help, how do you create a view like that. There are many confusing joins to it I can't wrap my head around it.

Thank you very much in advance.

@Polygorial There is no and would be no such requirement you can be rest assured. — Daniel
– Daniel, Commented May 15, 2021 at 10:34
Why three tables when you only need one? Use a column with the shift and you're done. Making thing complicated, makes it brittle by design to guarantee bugs and other problems. — Frank Heikens
– Frank Heikens, Commented May 15, 2021 at 13:25
@FrankHeikens Your idea makes a lot of sense. I like it a lot thank you! — Daniel
– Daniel, Commented May 17, 2021 at 10:57

Dimitris Kirtsios · Accepted Answer · 2021-05-16 08:59:03Z

1

As others mentioned in the comments, there is room for improvement regarding the DB design. However, this is how to create a view, just join all the tables where you need data from and select the fields you want:

CREATE VIEW shifts AS
    SELECT *
    FROM work_day inner join morning_shift on work_day.id = morning_shift.shift_id
    inner join evening_shift on work_day.id = evening_shift.shift_id
    ... (more joins)

Take a look at this Postgres tutorial page on joins

answered May 16, 2021 at 8:59

Dimitris Kirtsios

3172 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Over a year ago

Hi Dimitry, thank you kindly for your response. I've combined both your and Frank's response and something really good came out of it. Since I started DB first I didn't even have to modify any code to manage it. Thank you very much!

Collectives™ on Stack Overflow

Creating views with joins in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related