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.