Trying to think this through. If you’re a database wiz by trade, would love your input! Many thanks!
So the dilemmas in your structures come down to these questions.
1/Should blocked-off times be included in the assignments table or have a separate table?
For blocked-off times, there might be some further considerations here. If it’s a blocked-off period unrelated to any mobilizations, does it complicate your structure? I guess you can just do some queries to filter them out and it might not be a big issue at all. My take on this is you can do it in the same table.
2/Is it suitable to keep clock-in and clock-out times in the same table as assignments?
Same as above, I think you can keep it here, unless they said people can clock in and clock out multiple times per assignment.
3/Is a separate table for mobilization days necessary?
It could be beneficial, seeing you say that operations lead want to view and manage those assignments day by day. I guess you have to use a series of action here every time the schedule is changed. Say check the start and end date, generate a list of days to be “added”. Check if they are “added” already, if not, add a new row.
Can be complicated when they span across weeks and you need to take into account days when they don’t work, plus the specific start/end times within each day?
Unless I want to keep this data clean? I’m thinking maybe a separate table in case the blocked off time spans multiple days.
Yeah…I’ll need to get clarification here
Hm…true. Maybe that would just be a different mobilization then? eg. 16-day job no weekends would be separate mobilizations that don’t include saturdays and sundays?
The choice of the data source seems really important here. Are you planning on using GBT for this? I would look into having calculated JSON of days in mobilization rows
Hmm…Can the people switch between assignments? Also, is mobilization flexible in terms of start/end or persons assigned?
I’d keep the job and mobilization tables as simple as possible and consider whether it still works well if any of the other variables change. Maybe have the user input the gross number of days for the job and number of days for each mobilization (rather than a fixed start and end date).
As for the rest, my hunch is that separating the employee data into a timesheet table and another table for assignments might be a safer bet than doing too much on one table. I’m just thinking that in the event that an employee picks up multiple assignments in one day, it might be hard to deal with on a single table.
Finally, creating a calendar/planner for predicted or actual date-time input that displays the records visually → highest level is job, then mobilization and the detailed level is employee(s) assigned on the day.
I still struggle with my data but hopefully I was able to help a little.
3 Likes
Hey Bob, I’ll answer the question at the end of your video:
“What would you do if this were your project?”
- I’d considering calling you for help
- I’d turn to your YouTube channel to see if you had videos that might help.
- I’d turn to the community forum and use advanced search.
- I might turn to the expert Slack channel though I tend to avoid it for help.
- I might message directly a few experts I’m in touch with on occasion.
- I’d ask AI to see what it would have to say.
A cocktail of the above.
When the blocking time spans multiple days, maybe yeah, moving it to another table would suit better. How would you integrate the blocking part when doing the assignment? Sounds like it would be the same problem like the “hotel booking” apps.
Yeah, breaking them into ones that don’t cross Saturdays and Sundays would make them easier to manage, plus public holidays as well.
That’s what I was planning, yes. Everything is a GBT except for Employee table.
Agreed. Especially if it’s a long day that needs to be broken up into multiple shifts per day (eg. clocking out for lunch).
1 Like
When the operations manager goes to assign crew to the selected day, we’ll grab the date context and bring it into the employees table. From there we can run a query in the employees table that checks if any of those employees blocked themselves off for that date context and filter them out of whatever component I end up using to select the employees for that date.
2 Likes