How to implement storing the history of an entity in a relational database?

Question

Database design

Hello.
There is a task to make storage of histories of a certain entity. This is both a row in the DBMS that has a UID, and links to other tables.
Never designed stories like this.
So far, I see only that "history" from the point of view of RDBMS is all the same standard strings and links, only with new IDs. But in order to be able to pull out the history of the document, I think it is necessary at the level of ALL entities that will be involved in the system to make a SECOND identifier, which will be unique within one version.
Are there any pitfalls in this matter? Can anyone share their experience on how to do this kind of thing?

Answer 1

Here
https://habr.com/ru/post/101544/
a short and transparent description of different options for organizing versioning.

Answer 2

It depends on how this story will be used later. If it's just for the sake of being able to restore data or see who and what has changed. Then a simple option would be to collect the entity with all the relationships into a json object and store it in a table of type
id | related_id | date_create | user_id | json_data