Database Design - Employee Clocking In and Out System

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

SQL:

CREATE TABLE IF NOT EXISTS `clocking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `clock_in_date` datetime NOT NULL,
  `clock_out_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `hourly_rate` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer 1

One thing you might want to consider changing is making clocking.clock_out_date nullable. Until the employee clocks out there isn't a reasonable value to use here, so NULL is the best choice.

You can tell the difference between someone who is still working (theoretically) and someone who forgot to clock out by checking their previous clocking record when they clock in. If the previous record has a NULL clock_out_date then they obviously forgot to clock out.

You could also create a report that checks for employees who haven't clocked out for more than (some configurable number of hours) since they last clocked in. You should be able to get a reasonable number for the longest expected shift. It may be once in a while that you report an employee to management for not clocking out when they are actually still working, but that should be rare and the manager will know it - or find it out quickly enough, so the few false positives you might generate aren't serious.

One other thing you might want to think about is not keeping the hourly rate right on your staff table. It might be better to keep this in a child table which includes date ranges since you want to be able to let a wage go up or down over time and it would be good to (a) have a history and (b) be able to enter future-dated changes to relieve time pressure for data maintenance.

Answer 2

The design looks ok (although I agree with Joel Brown's answer wrt the nullable clock_out_date). You could do this all with triggers, but I really think this kind of logic is better handled in code (either in your application or in stored procedures). It will be much more flexible and if you need to change the logic in the future, it will be much easier than if you try to embed the complex logic in the underlying data structures.