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