title: MySQL Database Structure For Employee Timeclock
body: <p>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:</p>
  
  <pre><code>id - int
  employee_id - int (fk)
  timestamp - mysql timestamp
  event_code - int (1 for clock in, 0 for clock out)
  </code></pre>
  
  <p>I've got everything working where if their last event was a "clock in" they only see the "clock out" button and visa-versa.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>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? </p>
  
  <p>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.</p>
  
  <p>Is MySQL Timestamp a good option or should I use UNIX Timestamp?</p>
  
  <p>Thanks for any advise/direction.</p>
  
  <p>Rich</p>