Project time tracker

Question

I'm building a project time tracker and need some guidance on how to record weekly hours.

I essentially have two tables: projectTable and weeklyReportTable with a one to many relation.

ProtectTable will keeping a record of all projects being worked on along with other information, such as owner, status, etc...

weeklyReportTable is supposed to keep a track of weekly activity on the project and there can only be one entry per week per project. On a weekly basis users will log number of hours and an activity report of what happened during that week.

The UI to update project status will consist of panel listing all projects by the logged in user along with an input field to log hours and activity. There will be a widget that the user can use to move forward and backward to select the week they want to log activity against.

Question: what would be the best approach to ensure that there is only one entry per week per project in the weeklyReportTable? If a user tried to log activity on a project for a week that already exists I don't want to create a new record but rather update the existing one.

Answer 1

Based on your answer above, that is what I would personally suggest.

If the entry is not yet there -> Create else -> Retrieve it -> Update it

I did something similar at my own stand-alone app, check it at: http://www.prototimer.com

Answer 2

if u have a project start date, I'd assume you want to create it when project gets created, ahead of time, till end date. Otherwise, you can create it when they click on a specific week, but you might want to lock that portion to make sure that in case two users, cannot click it, causing a duplicate value. Or you can write a trigger on the before create to check for a duplicate but still I think you want to lock it since relationship has a different cardinality than expressed through UI. If u have a custom database, specify a unique index on date + project id, as long as you use the same date (monday of the week u r in), you should be good