Payroll Database - Employee Raises (1 Viewer)

JPA

New member
Local time
Today, 08:06
Joined
Nov 28, 2018
Messages
9
I'm looking for general recommendations for handling pay rate increases for employees. That is, the simplest way to structure my DB, would be to include a pay rate in the same table with my employee's other information. But if I do that, and I change that employee's pay rate, then any historical information (e.g. invoices) that is calculated based on pay rate would be recalculated at the new rate. So all of my old invoices would no longer be correct.

Is there a preferred way to handle this other than creating a pay rate table?

Thanks in advance!
 

plog

Banishment Pending
Local time
Today, 10:06
Joined
May 11, 2011
Messages
11,613
No, since you need to keep track of when a payrate began and ended for an employee you need a whole new table to do that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:06
Joined
Oct 29, 2018
Messages
21,359
Hi,

Sorry, I don’t know of any other way. If you want to maintain historical data, then you’ll have to store them. Otherwise, where else will you get them from?
 

JPA

New member
Local time
Today, 08:06
Joined
Nov 28, 2018
Messages
9
That's what I thought, but I figured it was worth asking since I'm new to this.

Thanks for the help!!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 28, 2001
Messages
27,003
Basic design issue: If you want to know the past, you have to have a place to record the past.
 

Bullschmidt

Freelance DB Developer
Local time
Today, 10:06
Joined
May 9, 2019
Messages
40
Without creating a pay rates table, you could do the following...

- In the employees table have a rate field (which of course is the latest current rate).
- Also in the paycheck table have a rate field (which is the rate for that employee at the time of that paycheck).
- And then when creating a new paycheck record set the paycheck's rate to be that of the employee's rate.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 28, 2001
Messages
27,003
While Bullschmidt's suggestion would work, and I am a known pragmatist, there IS a relatively minor objection. Putting the pay rate in the paycheck is TECHNICALLY a normalization violation because the pay check and the pay rate are two different entities that don't have a two-way dependency on each other. It is a one-way dependency.

The pay rate depends on the employee number and the date whereas other basic employee data would only depend on the employee number. Therefore, the pay rate should not be in the employee table. (Dependency differences mean "not the same class of entity.") But if you put the rate in the paycheck record, the problem is that strictly speaking, the rate doesn't depend on the paycheck number AND one can legitimitely discuss the pay rate in the absence of the paycheck. And the other side of that coin is that more than one paycheck would have the same rate, thus meaning that to identify the pay rate, you would have a non-unique selector because you would have to pick a paycheck having that rate. There is the impurity.

The TECHNICALLY correct structure is a pay-rate table with the employee ID, the starting date, the ending date, and the pay rate for all days between the two dates. Plus, if you needed it, any descriptive info or codes about the cause for the change in pay rates. Like simple merit raise, cost of living raise, raise concomitant with promotion, etc. And for the record with the current pay rate, you don't have an ending date so you FAKE one. For instance, you could make the ending date 31-Dec-9999, which works in an Access date field and is a legal, legit, and consistent value to use. Then the current rate for a given employee is that ray-rate record where the starting date is less than the current date and the ending date is greater than the current date.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:06
Joined
Sep 12, 2006
Messages
15,614
As DOCMan says - it depends whether you want traceability.

If you store the pay rate on the pay slip, and then change it in the employee table, you have no way of checking that the payslip rate WAS recorded correctly. In order to do that you need a historical record of pay rates, which will generally need a payrate table linked to the employee table. I am pretty sure that in practice you will find a lot of real-world systems that will do both. Maintain a pay-rates table AND store the pay rate on the pay slip.

It's similar to invoices - I reckon a lot of systems would store the item selling price and VAT/tax rate in the invoicing tables, as well as in the pricing records themselves.

Thereby breaking strict normalization rules, but gaining benefits of simplicity - which works fine as long as you never want to amend a historical rate.

One reason for duplicating data is the difficulty of finding some records. Given a payslip it's to get all the information about the employee from the employee master record. Even if the address and phone number changed since time you made the payment, the old address probably won't be important. However some records have important history - tax code changes for example, as well as pay rates. It's actually quite hard to read a record from a table based on an non-equi join (I thing that's the term)

ie, find the payrate for March, when the rate changed on 1st January, and then again on 1st April - clearly you want the rate as at 1st January - but it's hard to build that relationship into a query - and it's probably easier to store the pay rate rather than having to look it up each time.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:06
Joined
Feb 28, 2001
Messages
27,003
Good point about traceability, Dave. With the U.S. Navy, I wasn't involved in researching pay issues because that was our "Help Desk" function. I was only involved in access rights and security, plus connectivity (device driver) issues. But the Help Desk got SCADS of calls each month asking about why the paycheck wasn't reflecting a seaman's rate change. We needed the pay history table to be able to answer when a particular change took effect. With us it was more complex because the rate change might be ENTERED on a given date but not APPROVED for another week, and if the pay check was issued in the interim, the old rate still applied. It was always interesting to see the data flow on an organization that massive with so many departments having their fingers in the pie.
 

Mark_

Longboard on the internet
Local time
Today, 08:06
Joined
Sep 12, 2017
Messages
2,111
For myself, the first set of questions are
1) Do you have a pay schedule for like employees (all of grad 7, step 2 get the same wages) OR are you assigning rate individually for each employee?
2) Can their rate change during a pay period?
3) Do you have formula driven overtime/holiday pay that affects all or certain employees?
4) Do you have a mix of hourly and salaried employees?
5) If you do have salaried employees, are they eligible for overtime/holiday pay?
6) Does this need to include sick/holiday/other days off in addition to hours worked?
 

Users who are viewing this thread

Top Bottom