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 ;