在DBMS中处理完整历史记录的最佳设计(Best design for handling complete history in a DBMS)

 我正在开发一个带有多个实体和关系的DBMS的网站。 我希望网站的用户能够查看某些内容中已更改内容的完整历史记录，例如，何时，由谁以及字段已更改为什么。 
 
 到目前为止，我已经考虑使用链表方法，您只需持久而不是更新实体，并将ID保存到上一个实体。 但是，由于关系可能会发生变化，因此我不知道上次更新时链接实体的情况。 我的意思是关系可以改变：假设我有一个Person实体，它与另一个Person实体有连接。 如果我只保留另一个人的ID，我不知道其他人员字段是否已被更新，例如，如果另一个人例如更改了其名称字段。 请参阅下文以进一步了解情况。 
 
 另一个方法可能是顺序保存对数据库的每个更改，并简单地恢复到给定的更改，例如保存所有的sql查询。 
 
 有谁知道处理这个问题的正确方法？ 提前致谢。 
 
 更新 
 
 假设我有一个名为Person的实体（见下文）。 这个人可以有很多父母和孩子。 让我们说用户可以更新现有Person中的值。 每当用户例如更新名称字段时，我想保存此更改，以便其他用户可以根据需要还原更改。 如上所述，如果我只持有父母的身份证，我不知道有这个身份证的人是否有所改变。 例如，如果父母名称已更新。 
 
Person {
    int id;
    String name; 
    List<Person> parents;
    List<Person> children;
}
 
 总之：我需要在更新时显示此Person实体的列表。 在这个列表中，我需要查看Persons字段确实包含的内容，以及其父母和孩子如何查看给定更改的时间。 

I am developing a website with a DBMS with several entities and relationships. I want users of the website to be able to view a complete history of what has been changed in certain entites, e.g. when, by whom and what a field has been changed to. 
 
So far I have considered to use a linked list approach where you simply persist rather than update an entity, and keep an ID to the previous entity. However, since relationships can change, I will not know how the linked entity was at the time of the previous update. What I mean by relationships can change: Say I have a Person entity which has a connection to another Person entity. If I only hold on to the ID of the other Person, I cannot know if the other Persons fields have been updated, e.g. if the other Person has for example changed its name field. Please see below for further explenation.
 
Another approache might be to sequentially save every change to the database, and simply revert back to a given change, e.g. save every sql query ever made.
 
Does anyone know a proper way to handle this? Thanks in advance. 
 
Update
 
Lets say I have an entity which is called Person (see below). The Person can have many parents and children. Lets also say a user can update values in a existing Person. Whenever a user for example updates the name field, I want to save this change, so other users can revert the change, if desired. As mentioned, if I only hold on to the ID of the parent, I cannot know wether the Person with that ID has changed or not. For example if the parents name was updated. 
 
Person {
    int id;
    String name; 
    List<Person> parents;
    List<Person> children;
}
 
In conclusion: I need to display a list of this Person entity for whenever it has been updated. In this list I need to see what the Persons fields did contain, but also how its parents and children looked at the time of the given change.

原文：https://stackoverflow.com/questions/46749691