I am building my first Database and I am struggling with how best to design my tables and relationships and to achieve my goals.
I am designing a payroll database and I need to know, what is the conventional design/structure for records that are subject to change but still need to retain historical accuracy?
For example: I have an employee who earns $12.00 and hour and I pay them at that rate for for a whole year, then I give them a raise to $13.00 and hour. If I change the record to reflect their raise than I will ruin the historical accuracy of my database because the $12.00 p/hr rate will been overwritten the new $13.00 p/hr rate. If I add a new pay rate every time an employee gets a raise then I create a messy situation where one employee may end up having several different pay rates linked them over the span of their employment and it may not be clear what their current rate is. I could create a note that displays the employee's current hourly rate, but then I would have to manually type in the current rate every day for every employee instead of having it auto-fill like it currently does.
I assume there is a simple solution to saving historical accuracy while updating information but I am new to database design and I am struggling to find an efficient way to structure my tables and relationships to accomplish this. Any advice, or resources that will point me in the right direction would be greatly appreciated.
Edit: The simplest idea I have is to add a yes/no column in my table, tblEmployeePay, that designates if the pay rate is current (yes) or old (no). Then I will have to add in a new pay rate whenever there is a change, mark it as 'yes' and mark the previous pay rate as 'no'. I'll also have to figure out how to make sure my pay rates marked 'no' do not show up on any of my data entry forms but that seems like that should be pretty easy with a IIF condition...is there a better way?