Design for presentation of registration and registration of employees

Question

Currently, I have a table that represents the start and end times of an employee:

• id_employee int
• check_in datetime
• check_out datetime

It requires updating check_out when the employee is completed.

It would 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 been checked in all cases, I check if the last record for this employee has event event_type CHECKIN. In addition, receiving a record and updating it is no longer needed.

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

Answer 1

As usual, "it depends."

Option 1 is easier to build and easier to request. To find out who registered, but did not check, this is a simple request; finding the total number of hours worked for each employee is also simple. This simplicity probably means that it will be faster for regular queries. The only drawback that I see is that it is harder to expand. For example, if you want to record a different type of lunch break event, you need to add additional columns.

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

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

Answer 2

Format No. 2 is better because:

• This table is only a perforation record entry. Even if she has anomalies, it does not matter.
• In the future, this table will expand, for example, you can enter two more events INTERVAL_OUT, INTERVAL_IN. The second format will remain simple.

• If possible, use event_type_id instead of event_type and another event_type table or just a constant array, for example.

  array_event_name = array (1 => CHECKIN, 2 => CHECKOUT, 3 => INTERVAL_IN, 4 => INTERVAL_OUT)

Answer 3

I know this post is outdated, but this is for those still looking for a 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 a specific employee, add AND A1.employee_code = 'EMP0001' in the WHERE

Answer 4

I would go with the second.

however, the basic questions and business rules will be the same and answer any approach.

Answer 5

Option number 1

In the first case, the database itself can better protect itself from some anomalies ¹ . Some anomalies are still possible ² but this is the beginning.

InnoDB tables, on the other hand, are clustered, and secondary indexes in cluster tables can be expensive (see “Disadvantages of Clustering” in this article ), which must be considered if you need to request a check_out .

Option number 2

With the second option, you rely on imperative code even for anomalies that can be prohibited purely declaratively using database design.

On the plus side, you least need secondary indexes.

The choice

So, briefly move on to the first option, if you do not need a secondary index. If you need a secondary index, depending on what kind of coverage index you want to achieve, you can go with any of them.

---

¹ Such as unloading without prior registration.

² For example, when you turn on again, without first checking, overlapping "stints", etc.

Answer 6

I would go with the first option here. Putting both time stamps in one row will increase the search time and simplify your calculations.

Suppose you want to calculate employee hours for one day. Your search will stop in the first line, in which it matches, and you will have all the necessary data. You do not have to dig deeper, which does not correspond to option 2. Option 1 also reduces the size of your table, using only 1 row per registration / exit.

Option 2 has one advantage. When checking, your database will need to search to update the data for option 1. For option 2, just write it.

Given the fact that you will search for data more than once, you can opt out of the direct advantage of inserting to get a better structure and faster search. Although the final choice is up to you.

Good luck