4

I'm working on an app that is partly an employee time clock. It's not too complex but I want to make sure I head in the right direction the first time. I currently have this table structure:

id - int
employee_id - int (fk)
timestamp - mysql timestamp
event_code - int (1 for clock in, 0 for clock out)

I've got everything working where if their last event was a "clock in" they only see the "clock out" button and visa-versa.

My problem is that we will need to run a report that shows how many hours an employee has worked in a month and also total hours during the current fiscal year (Since June 1 of the current year).

Seems like I could store clock in and outs in the same record and maybe even calculate minutes worked between the two events and store that in a column called "worked". Then I would just need to get the sum of all that column for that employee to know how much time total.

Should I keep the structure I have, move to all on one row per pair of clock in and out events, or is there a better way that I'm totally missing?

I know human error is also a big issue for time clocks since people often forget to clock in or out and I'm not sure which structure can handle that easier.

Is MySQL Timestamp a good option or should I use UNIX Timestamp?

Thanks for any advise/direction.

Rich

Rich Coy
  • 545
  • 1
  • 8
  • 24

1 Answers1

2

I would go with two tables:

  1. One table should be simple log of what events occurred, like your existing design.

  2. The second table contains the calculated working hours. There are columns for the logged in and logged out times and perhaps also a third column with the time difference between them precalculated.

The point is that the calculation of how many hours an employee has worked is complicated, as you mention. Employees may complain that they worked longer hours than your program reports. In this case you want to have access to the original log of all events with no information loss so that you can see and debug exactly what happened. But this raw format is slow and difficult to work with in SQL so for reporting purposes you also want the second table so that you can quickly generate reports with weekly, monthly or yearly sums.

Is MySQL Timestamp a good option or should I use UNIX Timestamp?

Timestamp is good because there are lots of MySQL functions that work well with timestamp. You might also want to consider using datetime which is very similar to timestamp.

Related

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452