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?

  • Don't put something in a table "just because the other table is too crowded". Put it in a table where it is most useful to be. You don't want to end up having to write a bunch of code to keep the Shift and User tables in sync for no reason. – immibis Feb 11 at 4:34
  • 6
    How is working time recorded in the company? Isn't there already a system in place which knows about users, their planned shifts and excused absences, holidays, regular work days? If the answer is "yes", then don't reinvent the wheel. Instead, try to interface with that system. If the answer is "no", you have a much larger task to solve than just sending notifications on missed check-ins. – Doc Brown Feb 11 at 7:00
  • Some clients have a system to track working days, while others do not. – Marc Perry Feb 12 at 5:33
  • So, right now, I'm thinking about making a work schedule table that contains every date and time that a user is supposed to work, populated by some bada** front-end logic (or maybe some sort of external import) and a couple more columns for notes and codes like absent or excused. I'm going to have to shard the heck out of that table, though. At least if everything goes right. I'm still thinking really hard about the answers, but right now I'm having a hard time applying it to my situation. – Marc Perry Feb 12 at 6:00

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).

  • Thank you. You gave me a lot to think about. I'm using C# so I have all the data classes that I need. My issue is that users don't always stick to their shifts. There are some industries where employees schedules can shift a lot depending on work demands. I remember working at target and every week it was a new schedule. Would your solution be applicable in that type of environment? – Marc Perry Feb 12 at 5:54
  • Yes I think so. Like a user right system it is practical to work with groups i.o. individual users/employees. Just like one would do with paper administration. In fact borrowing from any paper/Excel administration could be a first step (if that applies). – Joop Eggen Feb 12 at 9:05

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.

  • Fair point, my eyes must be filling in words. I'll update my response. @DocBrown – Kain0_0 Feb 11 at 7:14
  • Where and how would you recommend the journal be stored? – Marc Perry Feb 12 at 5:46
  • The same way anyone stores a record, as a row/document in a table/collection. eg: UserActivityJournal(journalID, userID, date, wasActive, activityExpected, markedIssue, explainedby, explanationKind, explanationComments) as a rough stab. markedIssue would be true if this represents a current problem, the explain... columns are filled in only if the problem is explained. You will likely need to modify this, perhaps you need to version the record (to provide audit history), or break the explanation apart from the journal. – Kain0_0 Feb 12 at 5:59

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!

  • Thank you for your comment. The thing is, many of the users are on shift rotation or on call. In fact, the current system acts almost the way you describe it. But it's been a headache for the customers with it's limitations. That's why I asked you guys ;) . – Marc Perry Feb 12 at 5:44

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.