Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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.
–
JohnFxJan 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.
–
NateJan 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...
–
gbnJan 24 '12 at 17:58

That was a reference to your comment "The first three columns are the PK", that's all.
–
JohnFxJan 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.

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.