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?

  • 1
    A separate table is the way to go, containing a set of check-in and check-out time. In your original design, what if the employee takes 2 breaks, and hence have 3 check-ins and -outs on a day? You will not hit that limitation with your second design - you can support unlimited amount of check-ins and check-outs and unlimited breaks. Personally I would probably ditch the "day" column, and make "entrance" and "exit" a datetime-field instead, to support night work, but I do not know your business, domain and the rest of the data-model. – Niklas H May 31 '16 at 10:55
  • Please tell me the password field is shorthand for "a proper authentication system" and not a plain-text password. – whatsisname Jun 1 '16 at 7:00
  • @whatsisname - What you said. – Sipo Jun 1 '16 at 8:00

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) ;)

  • I chose this answer over @kevincline 's one because it answers my question in a clearer way, even though Kevin Cline's answer is great. – Sipo Jun 1 '16 at 6:55

It should not be in the employee table because you will have columns named "entrance_Sunday", "exit_Sunday", "entrance_Monday", etc.

This is a very bad design. Suppose you have another table with a column containing a "day-of-the-week" value. You won't be able to join these tables without some very ugly SQL:

  CASE calendar.day_of_week 
     WHEN 'Sunday' THEN entrance_Sunday
     WHEN 'Monday' THEN entrance_Monday

FROM holiday_calendar
INNER JOIN employees 

With a separate schedule table it's simple:

SELECT ... FROM calendar
INNER JOIN schedule ON schedule.day_of_week = calendar.day_of_week

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.