I'm looking for some advice on how to set up some tables for an application.
The application is for managing the attendance of substitute teachers.
Here what I have at this point:
tblSubstitute:
SubID - AutoNum, PK
LastName - Text
FirstName - Text
tblPayRate:
PayRateID - AutoNum, PK
SubID - Number, FK
Amount - Number
tblSubEvents:
EventID - AutoNum, PK
SubID - Number, FK
EventDate - Date
Here's the problem - the rate of pay does not stay the same. i.e a sub will work at an introductory rate and after a certain number of subbing days will be raised to another value. Some subs are payed yet another rate.
If a sub works for payrate1 from September through November, then gets bumped to payrate2 for the rest of the school year. How do I get the end-of-the-year report ,on that sub, to show each event at the correct rate. I guess I could have the payrate entered as part of the event, into tblSubEvents, but that doesn't seem efficient.
Or I could put a date field in the tblPayRate table; then I have to do some sort of calculation on the date of the event and which rate to use.
Any ideas are appreciated.