CODEWITHSUNDEEP
ms-accessdatabase-design
Jason Bayldon
Jason Bayldon

Reputation: 1296

Database design for weekly time tracking

I am planning on creating a database to track user's time in/time out M-F. Every week should begin on monday and run through sunday.

I have a table filled with my entire user population, so I know which users I need to create entries for and where they belong to. I have proposed so far, a table consisting of the following fields to track the entries (along with example data to fill the fields):

Field Name in table (Example of possible data)
---------------------
Employee (John Smith) 'String
Unit (Quality Assurance) 'String
WeekOf (9/9/13) 'date

InMonday (6:30) 'string, validate either a time in/out or N/A if holiday/vacation
OutMonday (3:15) '^^
HoursWorkedMonday (8.00) 'total hours worked
VacationMonday (0.00) 'if N/A for time, should have hours here
OvertimeMonday (0.00) 'any additional work hours should go here

For this instance, I would have to create In/Out for each day of the week (and perhaps track the date that each day is for). Is this extraneous or is there a seemingly better organization to tracking weekly time measurements? Should I use one table with a unit indicator or multiple tables for each unit?

Upvotes: 1

Views: 6838

Answers (2)

PowerUser
PowerUser

Reputation: 11791

While this project is technically feasible, I have to question the value of making it yourself in Access.

  1. The main issue is with security:

    • As a desktop program, this can be very easy to hack without precautions. Keep in mind that with Access, the user interface and the designer interface are by default the same thing.
      • If this is going to be a simple, straightforward db, a motivated user just needs to open the navigation panel and they can add/edit/delete all the timesheets.
      • If you hide the navigation panel, the user can just do a quick google search and learn to hit F11 (or find it by accident, either way)
      • You can try regularly (daily? hourly?) transferring the data from the publicly accessible back-end to an archive db that is not accessible to the general users. This can work, but still gives them a window to edit records. And if you don't do the transfer right, they can still add old records.
    • As a webform on a SharePoint, this can be fairly secure. I'd recommend this if you have Sharepoint.

  2. You should also consider your development time. This is a very common business task across many industries, from restaurants to factories to schools. As such, there's a huge number of cheap web-based options already out there that you can start using today. I'll even assume some of these include summary reviews breaking out numbers by departments as well.

    I've never researched these myself, but a quick google search found this interesting page: http://en.wikipedia.org/wiki/Comparison_of_time_tracking_software

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

Usually it's one table with a Date field, an In field and an Out field. That's pretty much standard timesheet data. Take a look at how this guy has it set up.

Make sure you're using an Employee ID in the timesheet, and then you would have a corresponding Employee table with all relevant info (ID, Name, Address, whatever else you store on him/her).

Upvotes: 2

Related Questions