Trying to use Quickbase as a payroll admin database. Two table relationship. Parent table holds persons details, name, address, sin, dob, etc etc etc, as well as job description (may have more than one depending on working in more than one area of our business) and may also have multiple rates of pay depending on the job they are doing on a particular day (in Ontario liquor servers who receive tips are paid a smaller hourly wage than say food and beverage people working in the kitchen). So in a persons profile, we include four job descriptions (Pro-Shop, Food Service with Gratuities, Food Service No Gratuities, Course Maint), with each job description have a regular rate of pay and an overtime rate of pay.
My real dilemma though is say someone is a Pro-Shop Clerk and they are paid $11.00 an hour, but on July 1st, their Pro-Shop rate is increased to $11.50 an hour. How do I retain the historical earnings up to July 1st? In my current configuration, as soon as I change their Pro-Shop rate from $11 to $11.50, all of the historical data is recalculated at the new rate.
If I tied the calculated rate to an effective date, if the person had more than one increase in a year, how would it know the effective date?