Database Design – Create a Separate Table for Employees’ Work Hours?

database-design

I have a table with employees information:

id, active, first_name, last_name, gender, id_number, address, picture, password, comment

Every employee has an entrance time and an exit time for each day of the week except Saturday. It is possible that an employee has two entrance times and two exit times (when he chooses to take a break).

So, for example, an employee could have the following working time scheme:

         | Sunday  |  Monday  |  Tuesday  |  Wednesday  |  Thursday  |  Friday
---------+-------------------------------------------------------------------
Entrance | 08:00      08:00       07:30        07:30        07:30       07:00
Exit     | 16:00      13:00       12:30        15:30        12:30       15:00
Entrance |   -        13:30       13:00          -          13:00         -
Exit     |   -        16:30       16:00          -          16:00         -

I thought to add this information to the Employee table, so the columns would be like so:

id, active, first_name, last_name, gender, id_number, address, picture, password, comment, 1st_entrance_sunday, 1st_exit_sunday, 1st_entrance_monday, 1st_exit_monday, 1st_entrance_tuesday, 1st_exit_tuesday, 1st_entrance_wednesday, 1st_exit_wednesday, 1st_entrance_thursday, 1st_exit_thursday, 1st_entrance_friday, 1st_exit_friday, 2st_entrance_sunday, 2st_exit_sunday, 2st_entrance_monday, 2st_exit_monday, 2st_entrance_tuesday, 2st_exit_tuesday, 2st_entrance_wednesday, 2st_exit_wednesday, 2st_entrance_thursday, 2st_exit_thursday, 2st_entrance_friday, 2st_exit_friday.

But it just seems… wrong.
So I thought to create another table WorkHours for that, with this columns:

employee_id, day, entrance, exit

Which is way cleaner and simpler, but is it logic? I mean, this is information about the employee, so shouldn't it be inside the Employee table? Do you have a suggestion for a completely different approach?

Best Answer

Database Normalization

First Normal Form (1NF)

  • Eliminate duplicate columns
  • Create separate tables for each group of related data

You should create a table employee_access and each time a employee enters or quits the building record the date and time:

id, employee_id, date_time, operation

operation field should contain IN or OUT

You may ask why not two fields (in_time and out_time) ?

  • speed
  • simplicity
  • versatility

If your company is like mine, people use to log several OUTs or INs and not exactly in pairs (sometimes they log IN when they're going OUT, but that's called Managers) ;)

Related Topic