Could you help me with this ERD for human resources?

preno 60 Reputation points
2023-02-06T09:54:10.7666667+00:00

I have to design a databse for our human resources office.

They need a database to keep track of employee overtime hours.

Employees are assigned to different services and can work overtime for each of them (in the same month).

Overtime hourly rate depends on the employee's position.

Over the year, the hourly rates could be updated (rate increase) and the position itself could change (promotion).

When one, or both, of these events occurs overtime worked form the beginning of the year must be recalculated with the new rates.

Better, a new amount must be calculated with the rate difference (current - previous) and the overtime hours.

I started with E-R diagram below, but I don't like it .

EROvertime.drawio

I don't really need to keep track all all employee position, but the current and the previous one in order to be able to find the current and the last hourly rate.

For the same reason I don't need to keep all hourly rate changes over the years. For a position I need only the new and the previous horly rate in order to be able to recalculate overtime amount.

I'd like to highlight that only when a change in employee position or hourly rate accours, a new amount must be calculated.

The schema above is not good.

Could you help me?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
41,878 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.