I am making a program that tracks whether an employee has checked in on a software app that day. The employee can check in at any time, so long as they check in before their shift ends. If they don't check in, then a notification gets sent to their supervisor (where they might get in some trouble :) ). My question is how to best implement this considering that user shifts may change and some days they may be absent, so notifications shouldn't be sent.
Right now I'm thinking about making a Shift table and assigning each user their own shift. In other words, each user has one shift, and each shift has one user. I could include it in the user table, but it's getting way too crowded. That way it can be edited easily. Maybe a second table where supervisors can save and load presets. But in this case, what type of data structure can I use to store excused absences and how can I store the days they work (Mon, Tues, Wed...)? Does their need to be another table just for approved absences? Can I just store the days in an array. I'm using postgresql, so this is possible.
Anyway, those are my ideas. Do they seem reasonable to you, or can you think of a better way to do this?