I am designing a database, and one of the requirements is to store row history. I have the following tables:
employees (id, name, surname, dob, etc. )
employee_docs_rel (id, emp_id, doc_id, document (varbinary(max)), has_document) documents (id, doc_name)
I created another table for employees_history(id, master_id, name, surname, dob, etc., user_id, timestamp, start_date, end_date)
I need to save the history of the documents brought by the employee. Which is the best approach that i should follow in order to retrieve the changing history of each employee and also to retrieve the employee and documents version in a given time.
Thank you in advance