I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user to enter for each day of the week the amount of time he worked for a specific project. What would be, in your opinion, the best way to store these values?


Have a look at the Timesheet model on databaseanswers.org. This covers it nicely

Simply, a table with

  • Date
  • User (FK to User table)
  • Project (FK to Project table)
  • Time worked

The first 3 columns are PK.

Now, do you want to:

  • log activity eg Analysis, Coding, testing separately?
  • log project phases separately?
  • log non-project work eg training, holidays (eg user focus or project focus?)
  • ...
| improve this answer | |
  • Nice model gbn, thank you. I don't need to log project phases or analysis/coding/testing separately, but I do need to log non-project work. – Psyche Apr 3 '11 at 17:55
  • 1
    I've built a few timesheet apps in my time, and let me just add one tweak to this. Use a surrogate identity column for the PK instead of a composite key. In every system I've worked with there is always a need to communicate outside the system about specific time entries and it is easier to just use a reference number than a three part identifier. Also, the time entries tend to get joined a lot (for example to invoices) and you will get better performance joining on a single integer field. – JohnFx Jan 24 '12 at 16:55
  • @JohnFx I completely agree. There is no reason not to simply drop and int identity column at the start of that table. – Nate Jan 24 '12 at 17:52
  • 1
    @JohnFx: the choice of natural or surrogate key is out of scope here. You'd still need a unique index on the natural key too = more space, overhead. An IDENTITY should also have no external meaning. You aren't wrong, but your points are unrelated to my answer... – gbn Jan 24 '12 at 17:58
  • That was a reference to your comment "The first three columns are the PK", that's all. – JohnFx Jan 24 '12 at 19:20

In our time tracking application, we save the start and end times of the work, too. We add a new row in the database for every single task on the specific day and calculate the sum before displaying it. Consider if this information will be useful in the future, it would be very hard to integrate that later. It's not too complicated to group the rows of a day.

| improve this answer | |

I would use a join table that connects user to a project and also stores the additional data

  user   |    project    |    time     |   date
    1            39           360        27/09/2011
    2            4            60         27/09/2011
    6            34           500        27/09/2011
    5            34           320        27/09/2011
    12           37           720        27/09/2011
    7            34           50         27/09/2011

The time is here in minutes, but may also be in seconds (although that might be over-engineered).

Of course, your keys for user and project might be different, but I think you get what I mean.

| improve this answer | |

I created a time tracking application before that allows the administrator only to update the times. You do not want any single user being able to edit their hours since cheating might be a problem. If someone forgets to clock in, the admin can simply edit the hours. If the user has a client, however, and the hours are not their own, they should be able to edit the hours for the client but not themselves as an employee.

I had a user table with the name, email address, last action, etc. of the user and a usertimestamp table with the actual punches.

id int auto_increment
user_id int
action -- whether the user has clocked in, out etc.
clock timestamp -- defaults to the current timestamp
notes text -- allow the user to leave notes if they are late
realtime timestamp -- this is the actual time the punch happened before any editing by an administrator for auditing purposes
ipaddress varchar(255) -- prevent the user from clocking in or out from a place other than designated unless authorized to clock in from anywhere

This is simply the usertimestamp table, but I think that is all you were really seeking. I also have a permitted IPs, company table (because they can enter a list of IPs), clients for a company (separate from employees) and client timestamps to keep track of client times for invoicing for separate companies which you may consider adding based on your needs.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.