Database Design - Employee Clocking In and Out System [closed]

Question

I want to develop Employee Clocking In and Out System (website).

I am concerned two things:

• If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the manager.

• Staff may work over time, eg: Clock In Monday 11:00 AM To Tuesday 01:30 AM (after Midnight). I dont want system think thay staff have forgot to clock Out..

  • Staff may clock In and Clock Out multiple times a day.

How to solve this issue and what can be improved on Database Design?

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    |                |
+-------------+--------------+------+-----+---------+----------------+

clocking 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 can of worms you're opening here mate. After working for a company that only does clocking systems it is something I will never want to do again!

I realize my reply is greatly conceptual and addresses a few matters outside the normal scope of the question but this is in order to outline database design and structural concepts in this type of application. This information comes from recent specialist development I have done in this area also so it is not solely hypothetical but proven in practice.

When you're looking at this type of system you're best to initially use indicators as flags, this is generally the quantity of punches versus comparing every record. To compare every record for 1,000 employees is not the best thing!

For example, if the user has in a 24 hour period 8 punches (start of day, morning break start, morning break end, lunch start, lunch end, afternoon break start, afternoon break end and end of day) you can determine that it was unlikely there was no missed punches in the period and overtime has occurred, however if there were 7 punches in the 24 hour period (start of day, morning break start, morning break end, lunch start, lunch end, afternoon break start and end of day) you know that a punch is missing and the person forgot to clock out for the day. Notice how afternoon break end is not there.

However this is not full-proof and only provides an indicative reference, you will need to compare a shift schedule to punches to make sure nothing was actually missed. Because it is possible that both lunch end and afternoon break end were both missed leaving 6 punches you can set up your code to flag anything that is not X number of punches for that employee. When flagged you then run your actual comparison on that employee for that period to work out what has actually happened and what was missed.

This doesn't mean you don't compare all records, the thing is however depending on the number of employees it could cause some big problems doing exact comparison on every record, this is where it is generally better for larger numbers of employees to use 2 servers, 1 for data collection and interface only, the second for running comparison processes.

As I mentioned, for this type of application and to work with varying schedules, you also need to look at holding shift schedules to compare against. You will want to hold a padding record for each employee that will give equal time before a shift start and after a shift end. This will mean the likelihoods of overtime extending past 1 time period will be minimal. The basic formular is really simple: number of hours in the shift - 24 hours / 2 = time padding

You now place the time padding before and after the shift schedule for that user. But you still need to handle a shift swap, change or more than a 24-hour shift. This is where it really gets tricky. You will want to look at holding a table of overlapping times (padding, shift and punches) that can then be reconciled against any future adjustments to the schedules as after the fact changes are not uncommon in time attendance.

Now you will also want to hold a table of absentee periods for sick leave and holidays, this data will then need to be flagged generally within the punch-in/punch-out table as this is what you compare with. When a flag is set for a period the record can be skipped in comparison and a note left on the reporting without needing to run additional operations to check it.

Almost at th end, notice how you need to forget about your normal date/time conventions to measure this for a range of variables? Not to mention to work effectively and efficiently over multiple dates? Because of this you are generally best to only work with start and end times by dd/mm/YYYY hh:mm:ss, for the measurements use UNIX time to calculate how much time has elapsed. In saying that to be audit able most systems do need a "date stamp" record so server side processing of this is likely to be required when putting out reports.

Finally, I would also recommend holding a results table, this would give the reporting results for each user, this way when you want to provide historical records you can dynamically generate documents however you don't have to waste processing power on comparing records every time a report is requested.

I hope this helps!