I need help setting up a small time sheet table - relational DB - SQL Server
I have a TimeSheet like:
CREATE TABLE TimeSheet
(
timeSheetID
employeeID
setDate
timeIn
outToLunch
returnFromLunch
timeOut
);
The employee sets his / her time sheet daily, I want to make sure he / she is not cheating. What should I do?
Should I create a column that gets the system date / time when an insert / update happens to the table and then compares the generated date / time to the employee specified time. If in this case I have to create a date / time column for timeIn, outToLunch, returnFromLunch and timeOut. I don't know what you are suggesting?
Note: I am concerned about keeping track of these 4 columns timeIn, outToLunch, returnFromLunch and timeOut
a source to share
The single table design only allows one employee (I assume lunch is not paid). And it would be difficult to detect a scam that did not pass the validation of every record change. I think that something like a two-table approach would be more flexible and safer.
Start by creating a TimeSheetDetail entry for each event. that is, start shift, break stop, stop stop, end shift. Let the employee write any date and time in the "Enter" column. There may be legitimate cases when an employee forgets to make hours in or out of them.
It would be very easy to detect fraud by comparing the "Input" value with the added value before payroll or any other time an audit is needed. You may even find a petty scam where an employee is constantly spinning up or down in their favor every day. Ten minutes every day throughout the year are added up to an extra week.
This construct can be protected if updating or deleting records is not allowed.
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()
);
a source to share
If you are worried about the dishonesty of the employees regarding their working hours, then install a manual clock with punch cards in the system / clock and treat them like factory workers.
Otherwise, a trigger that archives the modified entry with a date-time stamp against it will allow you to see at what time all schedule changes were made and fraud can be made. Thus, you will need something like a table TimeSheetHistory
, with additional columns for the time of the change, and the user will make the change (populated with GETDATE()
or similar, and SUSER_SNAME()
or similar if you are using Windows authentication).
a source to share
Of course you are concerned about this, which is one of the basic requirements for most time-sensitive applications! No one should be able to change their own post-submission time sheet without overriding the supervisor. This prevents temporary card fraud and therefore is a legal issue and should not be misrepresented. Employees who receive overtime work can submit the correct timetable for supervisor approval and then modify it to add hours just before the payroll starts and then modify it otherwise. This is a critical feature that any scheduling application should have.
You first need to have a history table to keep a record of all changes and who made them.
Then you need an update trigger that prevents the update, unless the schedule has been reopened.
Third, you need a timetable field. The insert / update trigger ensures that only people in the management group can change the status of the submitted status again and that no one can return their own schedule without someone else approving it. In terms that I learned while working at an audit agency, this is internal control, because it is known that it is much less likely that two people will team up to commit fraud than one person.
a source to share