Help for ERC - employee overtime hourly rate

[spylo]

Hi,
I have to design a database to keep track of the Overtime HourlyRate for employees.

Hourly rate depends on the type of overtime (day, night, holiday day, holiday night) - sorry I don't know the correct english definition for that- and the salary grade.

For example the HolidayNight overtime for the A1 grade has a hourly rate of 10€, but the same type of overtime for the B2 grade has an hourly rate of 8€.

I came up with this solution (not considering Employee for now):
01_TariffeOrarie.1.png

A further step is to consider that HourlyRate may change but we cannot have two hourlyrate with the same stard date for the same overtime type and salary grade.
This is what I thought

01_TariffeOrarie.2.png

Is that correct?

[June7]

Options are:
1. save the rate with each pay record so that changing the rate record will not impact history

2. save record ID of rate record into pay record and create new records when rates change and set the old ones as "inactive" and exclude those from combobox to select rate

[spylo]

Options are:
1. save the rate with each pay record so that changing the rate record will not impact history

2. save record ID of rate record into pay record and create new records when rates change and set the old ones as "inactive" and exclude those from combobox to select rate

Sorry, I’m not sure I understand.

1) save the rate amount so that I don't add a reference to the rate record?

2) Do you mean modify HourlyRate table adding ID and isActive fields ?
No need of StartDate and EndDate?

[spylo]

One important thing to say is that this database is not for a payroll system. We have a legacy application for timesheet but it does not satisfy all of our needs.
What we need is to keep track of these monthly entries :
- number of authorized overtime hours (paid hours or recovery time)
- number of worked overtime hours
- number of paid overtime hours
- number of leave hours
- number of recovered hours
- monthly hours balance (worked overtime - leave hours - recovered hours - paid hours)
- annual hours balance (previuos month annual balance + monthly hours balance)

We should also keep track of activities on which overtime hours are worked and estimate the overtime cost based on the current active rate.

This is what I came up to:
Attachment 898

What do you think?

[June7]

1. Correct, save the actual rate amount instead of or along with record ID linking back to rate table.

2. Guess you could say that is a third approach - create rate records with Start and End dates and instead of saving ID or rate with each pay record, do a lookup to rate table to pull appropriate rate.

Your attachment link does not work - "invalid" error.

[spylo]

I try to post my ER diagram again.

Attachment 899

In the diagram i use a reference to RateID, but I will use the Rate Amount as you suggest.
I just realized that maybe I've posted my question in the wrong section of this forum.
Sorry for this.

What I'm not able to design is this requirement : If an employee get a promotion over the year, this have a retroactive effect on the sum paidhttt for overtime. A promotion can be made official in October, but all overtime hours paid from January to October must be recalculated considering the amount difference between the new Rate and the previous one.
A promotion implies (is equa to) an increase in the pay position (Grade).

I hope you could help me.

[spylo]

If the attachment still doesn't work, this is the link to the diagram

https://ibb.co/VwjCVYn

Thanks

[June7]

Attachment still failed, however, diagram not really helpful.

If you want to modify historical record, either do manual data entry/edit to accomplish or run UPDATE action SQL.

[spylo]

Attachment still failed, however, diagram not really helpful.

If you want to modify historical record, either do manual data entry/edit to accomplish or run UPDATE action SQL.

No, I don't want to update historical data.
I was not clear.

If thera are rate changes during the year (changes due to either promotions or rate increases) must be calculated an adjustment with the difference in rates (multiplied by the number of overtime hours already paid with the previous rate).

An example : if in September is notified a rate increase starting from April. From April to August must be calculated an adjustment for all payed hours (Total payed hours * (newRate-PreviousRate)).

I think that this Adjustment should be stored somehow.
Adjustment shoul be a new Entity?


Regardin the promotions, I don't think it's necessary to store promotions history, but just the last one.
To keep track of the last promotion I think to add LastPromotionDate field in the Employee table.
Am I right?

Thanks

[June7]

So in addition to saving new rate and/or rateID used in calculating, you need to save either the old rate and/or calculated result. Depends how much support you want for the calculation result. Should be able to substantiate the calculation. 6-months or 6-years from now, explain how the calculation was derived. Robust paper trail is important.

Yes, promotion date in Employee table along with new job title, job description number, position number, etc.

[pomosa]

I came up with this solution