How to design a model so that it is possible to track the change in position?

Question

SQL

I have an employee entity. How to provide for the storage of the history of the change of positions by employees? So that you can request, for example, the number of job changes or how many times an employee has been in such and such a position?

Answer 1

employees separately, positions separately, the current position of the employee in the table of employees, and a separate table with the history of positions: employee, position, when he joined, when he left the position and who carried out the acceptance / removal from the position

Answer 2

What's stopping you from keeping it that way?
employee
id position id (or write the position directly, does not play a role)
Date of entry into the position

Answer 3

It is possible to hang up tracking of change on the trigger.
Personally, I switched to storing small amounts of hierarchical data in JSON a long time ago. Also hung up on adding/changing triggers. There is a history table where, when the trigger fires, I transfer the old Json data. I do version comparisons only if necessary outside the database. Calculating the difference through the query language is very difficult.