Database schema that manages weekly timesheets
database, database-design, php, sql
Solution
I will share with you a typical model that we use, https://www.dropbox.com/s/3wo9qd2fdpe5yow/timesheet_model.png, and describe each of the tables:
- useraccount - user information, login etc
- project - the projects that a user can work on
- projectassignment - the assignment of a project to a user, so these are the only projects that the user can see and submit time on
- timesheet - summary information on the timesheet, which week its for, comments etc
- timesheetdetails - a row for each day and each project with the hours worked, this table grows very fast. We also include the projectid foreign key in this table to speed up queries
- approval - a record of approvals made on the timesheet, as there may be a rejection before its approved
Hope this helps
The link to the SQL schema is https://www.dropbox.com/s/uadl5689v9j0jcs/timesheet_model_schema.sql (was taking too much time to format to render properly inline)
Problem
I'm writing this post because I was wondering if somebody could help me to figure out how the best database schema for a simple timesheets app will be. I can't figure out how to diagram tables that help me to represent weeks of the year, where all the users will log their work week hours. Cheers, and many thanks for your help! :) Guillermo