Best practices for history table
Just wondering what the best practices are for creating a history table. I have a human resources file and the parent record just contains a serial number and employee status (active, terminated, etc). This parent record has related records (RR) that contain all the personnel info. This allows for all changes to be logged by creating new records each time information is changed. My original idea was to have the creation of a new RR by duplicating the last RR so all of the info from the last record populates in the fields and the user would just change the appropriate fields. A layout based on the parent record would display the latest RR via a relationship sort. Is there a way to not have to duplicate the last RR; rather the user would simply enter data into the fields that need updating, and leave the other fields blank.....then somehow display the updated fields on the parent record layout, but use data from the second to last RR for the other fields that were not updated? This would allow someone to easily view what was updated during a record audit.