Mysql – Database Design – Employee Clocking In and Out System

database-designMySQL

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 ;

Best Answer

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.