Database design for a time tracking application

Question

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?

Answer 1

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?)
• ...

Answer 2

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.

Answer 3

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.

Answer 4

I created a time tracking application 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.