Results 1 to 11 of 11

Thread: Help for ERC - employee overtime hourly rate

  1. #1
    Join Date
    Feb 2023
    Posts
    7

    Help for ERD- employee overtime hourly rate

    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?
    Last edited by spylo; 02-12-2023 at 12:16 PM.

  2. #2
    Join Date
    Nov 2020
    Posts
    35
    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

  3. #3
    Join Date
    Feb 2023
    Posts
    7
    Quote Originally Posted by June7 View Post
    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?

  4. #4
    Join Date
    Feb 2023
    Posts
    7
    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?

  5. #5
    Join Date
    Nov 2020
    Posts
    35
    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.

  6. #6
    Join Date
    Feb 2023
    Posts
    7
    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.
    Last edited by spylo; 02-14-2023 at 10:25 AM.

  7. #7
    Join Date
    Feb 2023
    Posts
    7
    If the attachment still doesn't work, this is the link to the diagram

    https://ibb.co/VwjCVYn

    Thanks

  8. #8
    Join Date
    Nov 2020
    Posts
    35
    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.

  9. #9
    Join Date
    Feb 2023
    Posts
    7
    Quote Originally Posted by June7 View Post
    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

  10. #10
    Join Date
    Nov 2020
    Posts
    35
    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.
    Last edited by June7; 02-15-2023 at 04:24 PM.

  11. #11
    Join Date
    Aug 2023
    Posts
    5
    I came up with this solution

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •