Data model for timesheet to task and/or timesheet to project?
- by John
Let's say I want to make a simple project tracking system. A manager can create a project. Then he can create tasks for that project. Team members can record the hours they work for each task or for the project as a whole.
Is the following design for the t_timesheet table a good idea?
timesheet_id - primary key, autoincrement
project_id - not null, foreign key constraint to t_project
task_id - nullable, foreign key constraint to t_task
user_id - not null, foreign key constraint to t_user
hours - decimal
Or should I do something like this:
timesheet_id - primary key, autoincrement
task_id - not null, foreign key constraint to t_task
user_id - not null, foreign key constraint to t_user
hours - decimal
In the second option, I intend to always have a record in t_task labelled "miscellaneous items" with a foreign key to the relevant t_project record. Then I'll be able to track all hours for a project that aren't for any particular task.
Are any of the ideas above good? What would be better?
