Small Database Conundrum

I’m at the thinking stage for a database and can’t quite visualise the best way to structure it so far.

It is HR related, and will be used to store the hours and shifts that employees work in the company. It has to include holidays, sickness, any other time off, as well as any overtime that the employees may work.

The number of personnel is in the range of 1200 - 1500; they are split into 4 teams and each team has a working shift pattern which typically spans an 8 day period. The first 2 days they will work 09:00 to 19:00, the second 2 days they will work 19:00 to midnight, the third 2 days they will work midnight to 09:00 and the final 2 days are days off.

If a person works overtime, it could be working with any of the teams, typically for a single shift, so that a minimum number of staff are working all the time.

A person could also volunteer to work a single shift with another team instead of their own regular shift to help balance up numbers between shifts.

The database needs to record every shift for every person throughout the entire year. The team and employee unique ID’s are all available using a table lookup, and can also be combined into a single record if needed. It also needs to keep historical data for the previous 12 months.

The work is currently done using a very large excel spreadsheet, with many pages and formulas; it has been in use for a couple of years now and is creaking under the shear amount of calculations it has to do every time a page is refreshed. This current solution is no longer fit for purpose and is barely even considered accurate since sometimes it miscounts the summary figures it produces which then have to be checked manually.

As you can probably guess, I’m struggling to think the best way to set this up. If anybody has worked on similar databases before or has experience of HR databases in general, I sure would appreciate any input you could offer.

My first thought was one row per employee, and one column for each day, or, one row for each day and one column per employee, but I dont think either of those would be efficient since the column count would be unrealistic. The next option would be to split them into chunks of dates in different tables, maybe one month per table. Since we need to record the actual hours as well, this adds another dimension which is making my head spin just thinking about it.

The only good thing right now is that none of the existing data has to be imported so its a chance to start with a new clean database.

Thanks in advance.

Increasing column count is a bad idea.
You need to increase a row count instead.

My suggestion:

People as a table
Teams as a Table
Shift Type as a table (records holding ‘work’,‘overtime’,‘sick’,‘Awol’,‘holiday’…)
Shift Period as a table. (Maybe records of 2 to 7, 7 to midnight, midnight to 9am, and possibly ‘no work expected’)
Shifts as a table containing a date, a shift type, shift period, person, and team

This approach also allows people to do two shifts per day, and with differing teams.

Summing by person, team, date are easy with this model.

(You may choose to drop the ‘Period’ table, and instead have the start and end time in the Shifts table if you want more flexibilty in terms of times, especially if a shift can actually span calendar dates, when you would have a start datetime and end datetime )

Thanks for the reply Jeff.

I definitely think a start datetime and end datetime would prove the most useful. The summing areas you mention are also very accurate.

My original thinking was around using the required lookup tables but trying to cram the entire working patterns for the year into a single table for every person (person(rows) x date(columns)), but that just isnt possible to do in an efficient way.

Also, by storing the start and end datetime in the shifts table as you describe, it can account for someone who has to start a bit later or finish a bit earlier due to family friendly policies and the like.

I like your approach, very useful to get started with, thankyou.


I’d think twice about writing a time and attendance system. good ones are available for not a great deal of cash.
they get so complex so quickly that you can easily find yourself tied up in knots.

T&A is a total minefield and unless you are a masochist, id steer clear.

otherwise Good luck!

the best ones i’ve worked with boil everything down to clockins-clock outs on a calendar. then overlay holidays and what the employee was ‘supposed’ to have worked and give you an exception report. there are usually parameters per employee for how to handle the exceptions (early start, late finish) then a person will go through the exceptions that the computer cant fix and decide what to do.

Look into a system called KRONOS (its not free, but it is what the last company I worked for used, and it was quite flexible) [we had about 12,000 people on it]

But I agree, Time & Attendance systems are tricky. you can spend a ton of time in design and implementation, and adding “just one more thing” becomes a nightmare.

They are nasty
Been there done that don’t want to do it again