How to Store Employees’ Schedules in a Database

Level: Beginner

So many organizations face the common problem of storing employee schedules. No matter what institution: a company, a university or simply an individual, many entities need an application to view schedules. Therefore, I will try to come up with a database model and then, in a future article, we’ll talk about a simple application to store employees’ schedules in a database.

Currently the design looks as follows:

The model is pretty straightforward.

Tip – Natural and Surrogate Key Strategies

1. Use a surrogate primary key whenever values in the natural key could possibly change or when a natural key would be too complex.
2. If a natural candidate key consists of a small number of columns (ideally just one) and its values don’t change – use it as a natural primary key

Learn more about natural and surrogate primary keys.

The employee table contains basic attributes describing each employee, for example: name, surname, address and phone number. Name and surname don’t uniquely identify each record (there could be more than one person with the same name and surname), that’s why a new column was introduced (id), called a surrogate key.

The schedule table contains the days and hours when each employee is at work. The work_date column specifies a day when each employee was working. The start_work_hour and end_work_hour columns identify work hours for that day.

Let’s assume that we have an employee called Peter. He was at work on February 4 and he marks his start and end time in the schedule.

The corresponding row in the schedule table looks like this:

While building such a system we may want to know who worked the most nights or who worked the most weekends/holidays. In order to generate reports that contain such information, columns is_holiday and is_weekend were introduced.

Using this approach you will need to have a separate table for holidays that contains the dates of the holidays for the company. When an employee enters their work time, the columns is_holiday and is_weekend may be updated using a trigger. After inserting the date and time, the trigger will check if the date is a holiday or weekend and insert the correct value into the field.