0 votes
in Programming by (580 points)

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 shift(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 shift(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 shift(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.

Please log in or register to answer this question.

...