Thoughts on this ER-diagram?
Hi everyone, I'm working on a workforce scheduling system as part of my bachelor's thesis project, and I'd love some feedback on the ER diagram I designed. The system is designed to automate and optimize employee scheduling in workplaces where employees have different roles, work percentages (e.g., 40%, 60%, 100%), and preferences for shifts.
The goal is to allow managers to efficiently allocate shifts while considering constraints like availability, location, and shift types. Employees should also be able to express scheduling preferences (e.g., preferred working hours or locations).
ER Diagram Breakdown: - EmployeeRole: Defines different roles employees can have (e.g., Nurse, Caregiver).
- Employee: Stores employee details, including their work percentage and preferences.
- Shift: Represents a scheduled work shift with start and end times, type, and status.
- Place: Indicates different locations where shifts can be assigned.
- ShiftAssignment: Links employees to shifts and tracks where they will work.
Integration with Timefold To optimize scheduling, we plan to integrate Timefold, an open-source AI constraint solver that helps solve complex scheduling problems. Timefold will handle constraints like employee availability, shift preferences, and workload balancing while ensuring efficient resource allocation. Since we're also working with Quarkus, we want to ensure that our database structure aligns well with these technologies.
Questions: 1. Does this ER diagram provide a solid foundation for a backend that works with Timefold and Quarkus?
2. Are there any missing relationships or tables that might be essential for workforce scheduling?
3. Would you suggest any changes to improve performance, especially when handling large datasets?
Looking forward to your thoughts! 😊
The goal is to allow managers to efficiently allocate shifts while considering constraints like availability, location, and shift types. Employees should also be able to express scheduling preferences (e.g., preferred working hours or locations).
ER Diagram Breakdown: - EmployeeRole: Defines different roles employees can have (e.g., Nurse, Caregiver).
- Employee: Stores employee details, including their work percentage and preferences.
- Shift: Represents a scheduled work shift with start and end times, type, and status.
- Place: Indicates different locations where shifts can be assigned.
- ShiftAssignment: Links employees to shifts and tracks where they will work.
Integration with Timefold To optimize scheduling, we plan to integrate Timefold, an open-source AI constraint solver that helps solve complex scheduling problems. Timefold will handle constraints like employee availability, shift preferences, and workload balancing while ensuring efficient resource allocation. Since we're also working with Quarkus, we want to ensure that our database structure aligns well with these technologies.
Questions: 1. Does this ER diagram provide a solid foundation for a backend that works with Timefold and Quarkus?
2. Are there any missing relationships or tables that might be essential for workforce scheduling?
3. Would you suggest any changes to improve performance, especially when handling large datasets?
Looking forward to your thoughts! 😊
9 Replies
⌛ This post has been reserved for your question.
Hey @dghf! Please useTIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here./closeor theClose Postbutton above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically marked as dormant after 300 minutes of inactivity.
Why is WorkPercentages plural? What exactly is stored there?
Does ShiftAssignment need a ShiftAssignmentId? Couldn't you use the EmployeeId and ShiftId as primary keys?
Employee has a column availability that starts with a lowercase letter - you probably want to be consistent
Using Quarkus and Timefold shouldn't make much of a difference for the DB for the most part (though idk Timefold)
If you want to use the ER diagram in your thesis, there are more things to consider:
- There are standardized ways of writing down primary keys and foreign keys. Use these instead of not marking primary keys and using (FK) for foreign keys
- use actually standardized arrow types
- don't overlap arrows/relations if possible
💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.@dan1st | Daniel
"Why is WorkPercentages plural?
What exactly is stored there?"
You're right—it shouldn't be plural. The idea was to store the percentage an employee works (e.g., 40%, 60%, 100%).
"Does ShiftAssignment need a ShiftAssignmentId? Couldn't you use the EmployeeId and ShiftId as primary keys?"
Yes, the ShiftAssignmentId is needed because a shift can have multiple assignments. We need to track the location (e.g., building, floor, or specific patient assignment). If we used only EmployeeId and ShiftId as a composite key, we'd lose the ability to uniquely identify multiple assignments of the same shift across different locations.
"Employee has a column availability that starts with a lowercase letter - you probably want to be consistent."
Good catch! It should be "Availability" to maintain consistency with other column names.
"Using Quarkus and Timefold shouldn't make much of a difference for the DB for the most part (though idk Timefold)."
Yes, Timefold mainly affects how we structure and query constraints for scheduling. The DB structure itself won't be significantly impacted, but we need to ensure clear constraints for employee availability, shift preferences, and work limits so Timefold can generate valid schedules.
Yes, the ShiftAssignmentId is needed because a shift can have multiple assignments.It can have multiple assignments but the combination of assignment and employee is unique, right? also you could use the combination of assignment ID, shift ID and employee ID
💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.@dan1st | Daniel Someone told me the ShiftAssignment table is unnecessary, as it only acts as a connector and increases the joins required, and that the EmployeeRollId in that table feels repetitive, and possibly not 3NF, with the uncertainty of whether it's needed if an employee can work in a different role than they are assigned.
I think the ShiftAssignment table is necessary but it would often not be included in ER diagrams (many-to-many relations in 3NF (actually even in the the first normal form) need its own table
and yes, the EmployeeRole would be unnecessary if the name is unique and you store no other information
but it's actually the other way round: If there are other columns in EmployeeRole, removing that table (and putting the fields in Employee) would violate 3NF
💤 Post marked as dormant
This post has been inactive for over 300 minutes, thus, it has been archived.
If your question was not answered yet, feel free to re-open this post or create a new one.
In case your post is not getting any attention, you can try to use /help ping.
Warning: abusing this will result in moderative actions taken against you.