Employee Overtime Design
-
Hi everyone,
I have to design a small database to keep track of employees overtime.
An employee can work overtime on different activities in the same month. We also need to keep track of paid hours (not all overtime can be paid) and the hourly rate.
There are only three possible overtime types (Sorry I don't know the right english terms for them) : workday overtime, holiday or nigh overtime, holiday night overtime.
Hourly rates vary by type of overtime.
I would like to draw the final diagram proceeding step by step, so for now I’ll ignore hourly rates and paid hours.
I came up with the initial diagarm below just to keep track of worked overtime :
First question : Because there are only three type of overtime, is it a good solution to store all three values in the Overtime table?
I know that an alternative is to add an OvertimeType table with 1-to-many relationship with the Overtime table and have only Hours and OTypeID fields in it.
I choose this solution also to reduce table writes, but i know it could lead to many zero values.
What do you think?
-
This was removed by the editor as SPAM
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I think you just need one table with
Overtime
ID (primary key)
EmployeeId (foreign key referencing the Employee table)
OvertimeDate
OTypeID ("D", "FoN", or "FN")
OvertimeHours
-
I'm kinda curious why anyone thinks that a separate overtime table is needed at all.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
I'm kinda curious why anyone thinks that a separate overtime table is needed at all.
Well it can't go in the Employee table.
- Jonathan AC Roberts wrote:Jeff Moden wrote:
I'm kinda curious why anyone thinks that a separate overtime table is needed at all.
Well it can't go in the Employee table.
Heh... Ya think? 😀
Why is there a need for a separate overtime table? Why not just a normal time/attendance table with a column to mark the type of time it is? You're doing roughly the same thing with your OTypeID column.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you created a running total of hours using a windowing function, then you should be able to tell when an employee is into overtime hours. So if you had (EmployeeID, StartDateTime, EndDateTime, ActivityID), you'd know when you were into overtime, right?
Something like this would work. (Note, the line between OT and regular time is not quite worked out yet.)
use tempdb;
GO
CREATE TABLE #WorkData (
EmployeeID INT NOT NULL,
StartDateTime DATETIME NOT NULL,
EndDateTime DATETIME NOT NULL,
WeekNo INT NOT NULL,
ActivityID INT NOT NULL);
GO
INSERT INTO #WorkData VALUES
(1,'1/3/2023 13:00','1/3/2023 23:00',1,1),
(1,'1/4/2023 8:00','1/4/2023 23:00',1,2),
(1,'1/5/2023 7:30','1/5/2023 23:00',1,2),
(1,'1/6/2023 8:00','1/6/2023 22:00',1,1);
SELECT wd.EmployeeID
, wd.MinsWorked
, wd.RTMinsWorked
, IsOT = IIF(wd.RTMinsWorked<=40*60,'R','OT')
FROM
(SELECT EmployeeID,
MinsWorked = DATEDIFF(minute,StartDateTime,EndDateTime),
RTMinsWorked = SUM(DATEDIFF(minute,StartDateTime,EndDateTime))
OVER (PARTITION BY EmployeeID, WeekNo
ORDER BY StartDateTime
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW)
FROM #WorkData) wd - Jeff Moden wrote:Jonathan AC Roberts wrote:Jeff Moden wrote:
I'm kinda curious why anyone thinks that a separate overtime table is needed at all.
Well it can't go in the Employee table.
Heh... Ya think? 😀
Why is there a need for a separate overtime table? Why not just a normal time/attendance table with a column to mark the type of time it is? You're doing roughly the same thing with your OTypeID column.
There was no mention of any normal time/attendance table. I took it as if it was a new database.
-
I know there was no mention of a normal time/attendance table.. that's why I'm mentioning it now. 😉 The overall process should keep track of all time by project/rate and I don't believe it needs a separate overtime table to do so.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And, just to be sure, my initial post didn't cite your response. It was meant more for the op. I'll explicitly state that next time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi, thank you for your replies.
We already have a legacy software to keep track of time/attendance. But it has some limitations.
It can monitor employee hours, absence , overtime, etc.
The software to be developed must work in addition to the existing one to perform what the latter does not manage.
For example, our legacy timesheet tool doesn't keep track of activities on which employess works on.
Our HR office knows that an employee worked 10 overtime hours this month, but need to keep track that he worked 4 hours on activity A and 6 hours on activity B.
The office staff knows how to divide the hours between the various activities.
My diagram above referes to this requirement.
- spilo wrote:
Hi, thank you for your replies.
We already have a legacy software to keep track of time/attendance. But it has some limitations.
It can monitor employee hours, absence , overtime, etc.
The software to be developed must work in addition to the existing one to perform what the latter does not manage.
For example, our legacy timesheet tool doesn't keep track of activities on which employess works on.
Our HR office knows that an employee worked 10 overtime hours this month, but need to keep track that he worked 4 hours on activity A and 6 hours on activity B.
The office staff knows how to divide the hours between the various activities.
My diagram above referes to this requirement.
To be honest, I'd recommend that you get a better time tracking system that does all of the things you want it to. There's a really good possibility that it'll save you a pot-wad of money instead of trying spin your own up.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
Thanks, but that’s not an option.
I'm sorry for my bad English which certainly does not help in understanding what I need.
My initial question was : is it a good choice to store the three overtime values together instead of having an OvertimeType table with three rows (one for each type of overtime)?
But let's move on. Ignore what has been said so far.
My context is very similar to this:
I want to keep track of the hours worked monthly by an employee on a project.
The initial ER diagram is in the image below .
Second step : each employee has a salary bracket (or salary level, I don't know the right word in English). Each salary bracket is assigned a different hourly rate of pay.
For example:
salary level -> associated hourly rates
L1 -> 10 euros/hour
L2 -> 15 euros/hour
L3 -> 20 euros/hour
L4 -> 25 euros/hour
I think that this could be the ER diagram:
Third step:
We need to keep track of how many hours have been payed monthly for each employee.
fourth step:
During the year an employee could get a promotion (he moves to a higher salary bracket) or hourly rates could be raised.
When this happens the economic effect is usually retroactive and we must be able to calculate the amounts due for the hours already paid considering the amount difference between the new and the previuos hourly rate. No need to store this amount.
For example, referring to the values above :
My salary level is L1. The corresponding hourly rate is 10 euros\hour.
In March I am notified a promotion to L2 that starts from January.
This means that I have to calculate the amounts due to compensation between the new hourly rate (15 euros/hour) and the old one (10 euros/hour) for the hours paid from January and February.
January - paid hours : 10 - hourly rate : 10 euro
February - paid hours : 5 - hourly rate : 10 euro
Tha amounts already paid was : 10 hours*10 + 5hours*10 = 150 euros
With the new rates the amount (difference) due is:
10 hours * (15-10) + 5 hours * (15-10) = 50 + 25 = 75 euros
I don't need to store Employee's promotion history, just the last one and the effective start date.
Putting all togheter this should be the base model:
In the PaidHours table I would store the current Amount from HourlyRate table (or the RateID).
What do you think of this solution?
Actually, I don't even need to store (for my pourpose) the employee salary level table. I use it just to find out the proper HourlyRate.
What do you think if I put the SalaryLevel as field in the HourlyRate table , removing the SalaryLevel table ?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply