Database Design - Employee Input and Output System

Question

I want to develop a system of arrival and departure of employees (website).

I am interested in two things:

• If employees forgot “Clock Out” from yesterday, and today they have “Clock In,” he should tell the manager.

• The staff can work over time, for example: Hours Monday from 11:00 to Tuesday 01:30 (after midnight). I do not want the system to think that state employees forget the clock.

  • Staff can run In and Clock Out several times a day.

How to solve this problem and what can be improved when developing the database?

staff table:

+-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | password | varchar(50) | NO | | NULL | | | hourly_rate | decimal(6,2) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+ 

synchronization table:

 +----------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | staff_id | int(11) | NO | | NULL | | | clock_in_date | datetime | NO | | NULL | | | clock_out_date | datetime | NO | | NULL | | +----------------+----------+------+-----+---------+----------------+ 

Answer 1

What a massive worm of worms that you open here, mat. After working for a company that deals only with synchronization systems, I will never want to do it again!

I understand that my answer is very conceptual and touches on several questions outside the normal scale of the question, but this is in order to present the database design and structural concepts in this type of application. This information comes from the recent development of a specialist that I did in this area, so it is not only hypothetical, but also proven in practice.

When you look at this type of system, it is best to use indicators as flags, this is usually the number of strokes compared to each record. To compare each entry for 1000 employees, this is not the best thing!

For example, if a user has 8 strokes within 24 hours (start of day, morning break, morning break, lunch, lunch, start of day break, day break and end of day), you may be unlikely to have missed strokes during this period, and overtime took place, however, if within 24 hours there were 7 strokes (beginning of the day, beginning of the morning break, morning break, beginning of lunch, lunch, day break beginning and end of the day), you know that there is no breakdown, and the person forgot stand for a day. Please note that there is no day to end.

However, this is not a complete proof and provides only an indicative link; you will need to compare the switching schedule with the punches to make sure that nothing was missed. Because it is possible that both sides of the lunch and lunch break were skipped, leaving 6 strokes, you can set your code to mark everything that is not the X-number of strokes for this employee. When you are tagged, you then do the actual comparison of that employee over this period to find out what actually happened and what was missed.

This does not mean that you are not comparing all the records, but, nevertheless, depending on the number of employees, this can cause some big problems, making an exact comparison for each record, exactly where it is usually better to use 2 servers for more employees , 1 to collect data and only an interface, the second to perform comparison processes.

As I said, for this type of application and for working with different schedules, you also need to look at the shift shift graphs for comparison. You will want to make a record for each employee, which will give equal time before the start of the shift and after the end of the shift. This will mean that the likelihood of overtime exceeding 1 period of time will be minimal. The basic form is very simple: the number of hours per shift - 24 hours / 2 = laying time

Now you put the time filling before and after the switching schedule for this user. But you still need to handle a shift shift, change, or more than a 24-hour shift. Everything becomes complicated here. You will want to look at a table of overlapping times (indentation, shift, and strokes), which can then be consistent with any future schedule adjustments, because after changes in facts are not uncommon when visiting time.

Now you also want to create a table of absentee periods for sick leave and leave, this data should then be marked as a rule in the punch-in / punch-out table, as this is what you are comparing with. When the flag is set for a certain period, the record can be skipped in comparison, and the note remains in the report without the need to start additional operations to check it.

Almost at the end, pay attention to how you need to forget about your normal time and time conventions to measure this for a number of variables? Not to mention working efficiently and effectively for several dates? Because of this, you are usually better off working only with start and end times with dd / mm / YYYY hh: mm: ss, UNIX time is used for measurements to calculate how much time has passed. Saying that for auditing, most systems need a “date stamp” record, so processing the server side of this may be necessary when issuing reports.

Finally, I would also recommend conducting a results table, this will give reporting results for each user, so when you want to provide historical records that you can dynamically generate documents, however, you do not need to waste computational power when comparing is recorded every time it is requested report.

Hope this helps!

Answer 2

This is not entirely correct in terms of the domain.

For audit or troubleshooting purposes, hours or exits / bumps are required. So, you need to keep a history of individual hits for all employees.

The synchronization table in your case may be derived data.

The synchronization table should be

 +----------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | staff_id | int(11) | NO | | NULL | | | clock_type | int(1) | NO | | 0 | 0-in, 1-out | | clock | datetime | NO | | NULL | | +----------------+----------+------+-----+---------+----------------+ 

Answer 3

I do not see a problem with your design, as far as I see a problem with insufficiently substantiated requirements. You need to better determine when the manager receives the changes in order to take into account the shifts that take place after a few days. Perhaps the best rule would be that if someone has not worked 24 hours a day or if he tries to run the clock a second time, the supervisor will receive a notification. You may also need a trigger in the database that will not allow you to synchronize the clock if there is a missing clock. Or you can enable synchronization and send time that is absent for the supervisor for the action.

One thing that you especially need is auditing (a separate table) so you can see who changed the records when. You can find out who changed the record and what the old value was in case of questions about someone else’s time.

A timeout (and I assume that they will receive money based on the clock) is an activity that requires serious internal controls (not at the database level! Internal control should never be at the application level if you are trying to prevent fraud .) No one should have direct access to the table. They should be able to modify only the stored procedure, which determines exactly what they can do.

You might want to have specific rules for supervisor oversight. As a rule, everything related to time should be allowed to be changed only by the person’s leader after the fact. I would have a trigger that would provide this, the person on the schedule should be allowed to change the clock in the data, and then only if it is null. All other changes must be made by the head of this person. Thus, you may need a table to store the structure of the organization, or at least a field that shows who the staff leader is.

If people are going to play based on watches and wholesale, then you can check that your account people and their auditors are not completing the business rules. The internal contradictions for these kinds of things can be very complex and complex for the right.

One design problem that I see is the hourly column, it changes over time, and you want to say that (especially if the rate changed in the middle of the payment period for some reason), so I would break it to a related table. Again, the rights to this table must be strictly respected. No one excludes that an HR person should be able to enter data in an hourly field.

Answer 4

The design of your database seems beautiful. He should record only time and time. Elsewhere in your application, you must program your business rules. You can read this wikipedia article on sharing issues.

Answer 5

You will need to enable NULL on clock_out_date . Thus, you can write a check at the time it appears and simply leave clock_out_date NULL to indicate that the user has not yet verified.

If you plan to support multiple I / O checks for each event / day, you will also need an event / day identifier column in the synchronization table so that you can group checks by event / day. As you noted, this date cannot be used to group checks that span midnight. We use event_id, but maybe you need payroll_date.

I use the same sync table structure for a similar system. He was in production for a year, and I would not change anything. We save all the time in UTC.

Please note that we DO NOT use this system for payroll, and there may be significant audit requirements for the payroll system.