postgresql – Database Design – Can’t figure out entity relationships for web application backend

Background

I’m working on a personal project to replace a scheduling system at work. This is outside my regular duties and I’m hoping to be able to present a replacement web application to my supervisors that will help me transition to a development role. The current schedule is generated using a Microsoft Access database and a lot of custom code modules. The main issue outside of being Access and crashing a lot, is that it’s not flexible. Anytime it needs to be updated (at least twice a year), it requires someone with programming knowledge to get into and make many changes to the underlying modules. My goal is create something that anyone can keep updated through a web interface.

I’m using .NET Core for the application and PostgreSQL as the backend because it seems to work well with Entity Framework Core. However, this is the first database I’ve ever designed myself, and I don’t know the best way to set it up.

Original Design

Here is my first attempt at an ER diagram. I actually have a working application using this, but a lot of the schedule fill logic is convoluted and as I continue to add features it has become difficult to work with. One of the major problems is the code to iterate through the work stations assigned to each work center and the fact that crews don’t have a parent relationship which I’ve realized was a design oversight.

Original Entity Relationship Diagram

Additionally, I was forcing the schedule into an easy to display table like this (also including the rest of the days):

SchedID WorkCenter WorkStation Year Week MondayEmployeeId Tuesday Wed Thurs
1 Line 1 Crew Lead 2020 35 null 9999 9999 null

Current Working Design

Here is my current working entity relationship diagram.

Current working Entity Relationship Diagram

For this one, notice I have changed the schedule to create a separate entry for each date and work assignment. This gives me all the position information I need and the 1st/2nd half employee entries allow me to easily swap out employees for vacation and sick days and add replacements, but I will eventually need to put it back into the easy to read format. I’m thinking a query will work for that. We have a few hundred employee positions (work assignments) that will have to be reorganized for each work week. I’m a little worried about how fast the table will fill up, figure 300 rows per day more or less or roughly 110000 per year. Those numbers would scare me with Access but PostgreSQL should be fine.

The middle section is pretty straightforward. The top level is Division, divisions have plants, plants have departments, departments have work centers and work centers have work stations (i.e. the machines or areas that workers are performing their duties). Work stations are mostly static, only changing if a station breaks and needs to be completely replaced.

Then things get confusing. Crews exist (A, B, C, etc.), but I’m not sure where they belong in the relationship. We refer to them by department typically (Shipping A crew, Support C crew, etc.) and that is the level they’re assigned supervisors.

Work centers are manufacturing lines (1-10). Each line has several crews, but may not operate on every crew. Crew A may have lines 1-6, Crew B lines 1 and 3-8, and Crew C lines 5-10. It depends on current staffing levels and the amount of work. Work center/crew combos change regularly.

For each work center/crew combo, there is an employee assigned to each work station. These employees are also shuffled around all the time.

The rotation is the “current schedule”, and refers to how long crews are on day shift then night shift. This is the schedule change that typically happens twice a year and causes all kinds of problems that I referred to above.

Work weeks are how I can tell what days each work center is working in a given week. For instance, our most common schedule is 6 weeks of day shift followed by 3 weeks of night shift. So I can calculate a number based on the week of the year and the crew and use that to check each day whether they’re working or not. The actual work week table has true/false fields for each day of the week, I just left them off the diagram to conserve space.

Each work center can have it’s own rotation. Line 3 might be on 2 weeks days 2 weeks nights while the rest of the lines are on the 6/3 rotation. This further confuses the crew assignments because a 2/2 rotation would have 4 crews while the 6/3 rotation would have 3. There are also rotations with only 2 crews. This makes me thing that maybe crew should be a child of work center even though when I try to visualize how the application works seems like a lot of duplication.

I’ve also considered making a Department has a Crew has a Work Center (with a many-to-many Crew<>WC relation). This seems like it would allow me to get rid of the work assignment table, but I’m not sure I understand that correctly and I’m worried it won’t work. I think I would need a one-to-many relationship between crew and work center and have to duplicate work centers for it to actually be correct.

Entity relatinship diagram with Department to Crew to Work Center relationships

Fill Logic

To actually generate the schedule, I am iterating through each level starting with department. I need both the rotation and the Crew to caculate the variable for the working week. Something like:

foreach (department){
    foreach (workcenter in department) {
        foreach(workStation in workCenter) {
            foreach(workAssignment in workStation) {
                foreach (dateInWeek) {
                    if (isWorkingDay(workAssignment.workStation.workCenter.Rotation, Crew) {
                        Create new Schedule(date, workAssignment)
                    }
                }
            }
        }
    }
}

Actually, that can probably be simplified to the following with my current working ER diagram.

foreach(workAssignment) {
    foreach (dateInWeek) {
        if (isWorkingDay(workAssignment.workStation.workCenter.Rotation, Crew) {
            Create new Schedule(date, workAssignment)
        }
    }
}

In any case, the fill logic for my new Schedule table idea is so much simpler than the original that I believe I’m on the right track. I will still have to figure out how to turn the Schedule data into a displayable table, but that seems doable at least.

Conclusion

I spent more than a month working on my original application in my free time. While it works, it’s not as easy to use and update as I need to present to my supervisors. Since I’ve decided to scrap the backend and start over, I’ve decided to get some other opinions from more experienced eyes to help.

I’m open to any suggestions. Hopefully my new ER design is workable, but if not I definitely want to know where I went wrong and how to fix it.

Thank you all and if you made it this far I apologize for making this so long.