Back to articles list
- 2 minutes read

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:

id employee_id work_date start_work_hour end_work_hour is_holiday is_weekend
1 1 2015-02-04 10 16 0 0

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.

go to top

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy.