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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Have a look at the Timesheet model on databaseanswers.org. This covers it nicely
Simply, a table with
The first 3 columns are PK.
Now, do you want to:
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
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
This is simply the