Design to represent employee check-in and check-out

Question

I currently have a table which represents the start and stop work times of an employee:

• id_employee int
• check_in datetime
• check_out datetime

It requires an update on check_out when the employee is finished.

Would it be preferable to have a table as follows ?

• id_employee int
• date_event datetime
• event_type varchar, values can be CHECKIN or CHECKOUT.

To determine if an employee has already checked in all I have to is check if the last record for a given employee has an event_type of CHECKIN. Also, fetching a record and updating it is no longer necessary.

Is the second approach better ? Or do you have other suggestions ?

Answer 1

I know this post is outdated but, this is for someone who's still looking for solution:

Attendance Table Structure

id              | int
employee_code   | varchar
status          | enum('check_in','check_out')
created         | datetime

Data

id  employee_code   status          created

1   EMP0001         check_in        2016-08-20 09:30:30
2   EMP0001         check_out       2016-08-20 18:15:00
3   EMP0002         check_in        2016-08-21 14:52:48
4   EMP0002         check_out       2016-08-21 21:09:18

Query

SELECT 
  A1.employee_code,
  A1.created AS check_in_at,
  A2.created AS check_out_at,
  TIMEDIFF(A2.created, A1.created) AS total_time 
FROM
  tbl_attendances AS A1 
  INNER JOIN tbl_attendances AS A2 
    ON A1.employee_code = A2.employee_code 
    AND DATE(A1.created) = DATE(A2.created) 
WHERE 1 = 1 
  AND A1.status = 'check_in' 
  AND A2.status = 'check_out' 
  AND DATE(A1.created) BETWEEN '2016-08-20' 
  AND '2016-08-21' 
  AND DATE(A2.created) BETWEEN '2016-08-20' 
  AND '2016-08-21' 
ORDER BY A1.created DESC

Results

employee_code   check_in_at            check_out_at           total_time

EMP0002         2016-08-21 14:52:48    2016-08-21 21:09:18    06:16:30
EMP0001         2016-08-20 09:30:30    2016-08-20 18:15:00    08:44:30

For specific employee add AND A1.employee_code = 'EMP0001' in WHERE clause

Answer 2

As usual, "it depends".

Option 1 is easier to build, and simpler to query. Finding out who checked in but didn't check out is a simple query; finding the total hours worked for each employee is also straightforward. This simplicity probably means it will be faster for common queries. The only drawback I see is that it is harder to extend. If you want to capture a different event type for "lunch break", for instance, you have to add extra columns.

Option 2 is more flexible - you can add new event types without changing your schema. However, simple queries - how many hours did employee x work in June - are quite tricky. You pay for the flexibility in significant additional effort.

So, it depends what you mean by "better".