Whats the best way to represent timesheets in database

Question

I'm trying to model timesheets, billable hours, projects, users

Whats the best way to represent these in a database

Elaborating more on the question: My biggest predicament is how to keep date, hours, project together

One possible solution

User (PK id, name)

Project (PK id, name, FK user_id)

BillableHour (PK id, FK project_id, hours, date)

Thanks

Answer 1

It's worth learning about Normal Forms if you're not familiar with them. Saves you storing redundant data in your database (amongst other benefits).

http://databases.about.com/od/specificproducts/a/firstnormalform.htm

Answer 2

I can tell you from a very high level how I've done it for a very simple time keeping app. Although, this isn't necessarily the best way.

I don't have a handy ER diagram to share at the moment, but this is just a very basic starting point for a set of tables anyway.

User ID PK

UserRole UserID FK RoleID FK

Role ID PK Name

UserProject ID PK UserID FK ProjectID FK HoursAvailable

Customer ID PK Name

Project ID PK CustomerID FK

EntryType ID PK Name (i.e. billable, non-billable, etc.)

Entry ID PK UserID FK ProjectID FK EntryTypeID FK Hours Date TaskName (this could be further normalized, esp. if you want to have a bucket of tasks) Description

I could go on, but you get the idea.

Answer 3

Start with the reports you want and work backwards.

The minimal input unit is Activity, Duration, but if you use that minimum, you wind up with activities such as sri.attends.scrum.for.parisian.branch.of.client50.via.scype.from.home.to.discuss.installation.routine.of.zoom22, and your report code will have a lot of switch statements.

Presumably you will have at least one report with a name, an activity description, and a total time for a given time-period. If that's your only report, you can implement it as a four-field list: Worker (string), Task (string), Start (time), Duration (integer). If there's a report asking for all the activities ever allocated to a particular project, or one for all the time spent on activity-type X, or a report that allocates activities to particular clients, your design will benefit from more fields.

Perhaps you have a report that distinguishes installing.server.updates at the home office and installing.server.updates under active enemy gunfire in a warzone. If you have a "while.a.target" checkbox on your time-logging screen, that could be a real time-saver for your users.

Answer 4

Here's a brief (and incomplete) summary of some of the tables my old accounting system used:

• user: the person using the system; name, password, etc
• entity: a company or business; name, government IDs, etc
• project: a project that can be billed; name, supplier entity, client entity
• timesheet: a timesheet entry: user, project, start time/date, number of hours, description of work