How to best design a software feature when work shifts are considered

Question

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?

Answer 1

An employee (or shift crew) works in a periodic shift, maybe an extra day outside the shift, and maybe some day not during the shift. Also holidays can be planned in advance.

The planning should be as comfortable as possible.

• An employee group system (shift, better department) seems in order - a hierarchy.
• Official days off - a manual List.
• Shift times (your preset shift times) - a recipy like mon-fri from jan 2 alternately 06:00 - 15:00 or 14:00 - 23:00.

Then planned exception per employee:

• Annual holidays
• Leaves
• Extra shifts - when they are to be reported

From these data one may dynamically derive a day-by-day pattern. This seems better maintainable than storing the day-by-day pattern.

Probably one also has to register the exceptions to the employees presences.

This is a bit more work.

One of the first things to do:

• A final overview per employee
• Overviews of the groups, shift times etc.

Of course all is a bit harder on the data definition side: a detailed concept must be created in advance (important). However data maintenance:

• is repeatedly done
• is best be done using schemes (shift times, holidays, ..., crew groups)
• can have precalculated "dumb" data always added later
• has database entities, quite limited in quantity (2000 employees is nothing for a DBMS)

Should the implementation be in java, use the "new" java time classes (ZonedDateTime).

Personal data is a sensitive spot, transparency important. Sending a message to the employee on his absence being reported, might be a proactive positive thing (or not).

Answer 2

Firstly, the database tools for observing raw tables are not geared toward data maintenance, and quite likely there are other constraints that the business might have such as maintaining an audit history which will definitely not be supported by these tools. Place the data in the most appropriate part of your data model, then draft an API (network service/Library/or other) for manipulating that data, and place a UI in front of that. There are many ways to achieve this, take your pick.

Secondly, in these sorts of applications it make sense to reify the expectations for all users, and store the reified records as a form of journal. This way the exact outcome as determined by the software is stored, be it: not applicable, all good, missing, etc... From here you can expose a confirmation/explanation API/UI which managers can confirm/explain/etc the journal placing it in its final state.

Answer 3

I'll tell you how I'd tackle this. Don't bother with the shifts. Keep track only of whether or not a particular user has worked that day, and based on this, at the end of the day, if a user has not checked in a particular project, to send a notification. Otherwise you have to know which shifts they normally take, if there are any variations that day, and whether or not they decided to take a different shift that day. All this information is superfluous to your goal unless you want to send a notification e-mail immediately following their designated shift end.

Trust me, it will be complicated enough keeping track of whether or not each user is working on which days. For this, I would recommend having a designated work calendar whereby you define what would be the normal everyday work schedule. By this I mean, save generic information about the days of the week which are considered normal work days. If some users work monday through friday and others from saturday to thursday, then logically you should have two of these said work calendars for each type. In addition to this, you should have a list of exceptions to this work week where there is holiday such as on christmas day or the new year. In this way, you're not persisting each individual work day. You're persisting the exceptions and generic work day information.

From here, associate this work schedule to each user, and then allow a user-personalized list of exceptions when they can choose holidays which extend the normal work calendar but affect only that particular user. So this is similar to what you had before pertaining to company days off, except it is associated directly with a user.

If for any given day, a user doesn't have a holiday, and the associated work calendar isn't marked as a holiday, and the work calendar designates it as a normal work day, then you should be evaluating whether or not that user has checked in to his or her project that day.

This is the basic approach. There are further complications which come from time zones and shifts which cross from one day to the next (yes, this does happen), but if this isn't applicable to you, I wouldn't try to take this into consideration.

As you've probably noticed, this isn't a simple solution. You can imagine how much more complicated this would be if you were taking shifts into consideration. :)

Good luck!