I would like to build a database to track changes to employees' status including compensation and the departments they are assigned to. I was hoping to build this by using the Starter Solution, "Personnel Records" and adding a "Transaction" table linked by EmployeeID to track these changes.
Based on the date values in the Transaction table, I would like to report compensation by department showing the people assigned and each person's compensation for the time period in the report.
For example: Assume Steve Smith was transferred from Dept A to Dept B on Feb 1, 2015. Also assume, Steve Smith's compensation increased on Feb 1, 2015. My report, "Payroll by Department" is reporting on the 1st Quarter of 2015.
The report should include all employees who were "Active" during the report time range and their compensation at that time. In this case, Steve Smith would be included in two departments at two different rates. (e.g). 1 month in Dept A at lower compensation and 2 months in Dept B at the higher rate.
Generally - am I thinking about this correctly? Should these changes be tracked by using a "Transactions" table linked by EmployeeID?
Should I use 1 Transaction table for all types of changes (as shown below) or separate tables for each type of change?
Assuming one Transaction table will work, how would I define the relationship between Employees and Transactions to show the current data for the reporting period?
Transaction Table (assuming one table for all change types)
- Transaction Effective Date
- Transaction Type (New Hire, Compensation Change, Status Change, Type Change, Job Title Change, Department Change,etc.)
- Change_Status Change (Active, Inactive)
- Change_Type (Full Time, Part Time, SubContractor)
- Change_Job Title
- Change:Compensation Type (Hourly, Salary)
- Change_Hourly Rate$
- Change_Department Allocation (time allocated between two departments)
Thanks for any help or suggestions.