I Need Help Fixing My Small Time Sheet Table - Relational DB - SQL Server

Question

I have a TimeSheet table as:

CREATE TABLE TimeSheet
(
    timeSheetID 
    employeeID 
    setDate 
    timeIn 
    outToLunch 
    returnFromLunch
    timeOut
);

Employee will set his/her time sheet daily, i want to ensure that he/she doesn't cheat. What should i do?

Should i create a column that gets date/time of the system when insertion/update happens to the table and then compare the created date/time with the time employee's specified - If so in this case i will have to create date/time column for timeIn, outToLunch, returnFromLunch and timeOut. I don't know, what do you suggest?

Note: i'm concerned about tracking these 4 columns timeIn, outToLunch, returnFromLunch and timeOut

Answer 1

If you're that concerned about employee dishonesty about their working hours, then install a manual punch card clock in/clock out system and treat them like factory shop floor workers.

Failing that, a trigger that archives off the changed record with a date-time stamp against it will allow you to see at what time every change to a timesheet was made, and a case for fraud could be made. So you'd need something like a TimeSheetHistory table, with the additional columns for time of change and user making the change (populated using GETDATE() or similar, and SUSER_SNAME() or similar if you're using Windows authentication).

Answer 2

If they are going into an application to record their times, then recording when they do that won't be of much use as they could update their records any time of the day.

As David mentioned, if your concerned about recording the actual times then some form of punch card, or ID badge which gets scanned when they enter/leave the building may be required. Maybe its a bit too much Big Brother, but many companies use badges as a way to get into parts of the building.

If you still wish to create a date/time to record when the actual entry was made then I would suggest having a separate audit table, which gets populated when an update is detected on that table (via TRIGGER).

In the Audit table you could have columns like...

ID (PK)
TableName (this is included in case you need to monitor other tables)
ColumnName (the column where the change occured)
User (User who did the Insertion/Update)
DateofUpdate (Date/Time of change)

Answer 3

Of course you are concerned about this, that is one of the basic requirements for most time sheet applications! No one should be able to change their own time sheet once submitted without a supervisor override. This is to prevent time-card fraud and thus is a legal issue and should not be subverted. Employees who get apid overtime could submit a correct timesheet for approval by the supervisor, then change it to add hours just before payroll is run and then change it back otherwise. This is critical feature that any timesheet application must have.

First, you need to have a history table to store a record of all the changes and who made them.

Next you need an update trigger that prevents updates unless a timesheet has been reopened.

Third you need a field for timesheet status. A insert/update trigger will ensure that only people in the management group can change a submitted status to a reutrned status and that no one can return his own timesheet to without a differentperson approving it. In the terms I learned when working for an audit agency, this is an internal control becasue it is known that it is far less likely that two people will join together to commit fraud than one person.

Answer 4

The single table design only allows an employee one break (I'm guessing that lunch is not paid). And it would be difficult to detect fraud short of auditing every record change. I'm thinking something like a two table approach would be more flexible and more secure.

Start by creating a TimeSheetDetail record for every event. i.e. Shift Start, Break Start, Break Stop, Shift End. Allow the employee to record whatever date and time in the Entered column. There may be legitimate cases where an employee forget to clock in or out.

It would be very easy to detect fraud by comparing the Entered value to the AddedOn value before Payroll or any other time an audit is needed. You could even detect small fraud where an employee constantly rounds up or down in their favor every day. Ten minutes every day over the course of a year adds up to extra week.

This design can be furthered secured by not allowing record updates or deletes.

CREATE TABLE TimeSheet
(
    TimeSheetId
    EmployeeId
    AddedOn      //populate using GETDATE()
    AddedBy      //populate using SUSER_SNAME()
);

CREATE TABLE TimeSheetDetail
(
    TimeSheetDetailId  
    TimeSheetId        
    Type               //Shift Start, Shift End, Break Start, Break End
    Entered           
    AddedOn            //populate using GETDATE()
    AddedBy            //populate using SUSER_SNAME()
);