CODEWITHSUNDEEP
sqldatabase-design
fonnes
fonnes

Reputation: 509

Best design for handling complete history in a DBMS

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.

Upvotes: 1

Views: 134

Answers (1)

SE1986
SE1986

Reputation: 2740

If I understand correctly, you want to create some sort of audit history whenever a change is made to a table and allow retrieval of the changes made?

Whenever a user for example updates the name field, I want to save this change, so other users can revert the change, if desired

Consider your entity Person which would have a database table like:

Person
id INT
name NVARCHAR(20)
parentId INT

to keep the change history, you can have a Person Change History table in your database:

PersonCH
RevisionCount INT
id INT
name NVARCHAR 
parentId INT
ChangeDateTime DATETIME
ChangedByUserID INT

To explain how this would work, lets work through your example:

Lets add a person called John:

INSERT INTO Person VALUES (100,'John',99);

At the same time, we should also add a record to our history table:

INSERT INTO PersonCH (1,100,'John',99,GETDATE(),123);

and if we change John's name we again add a new record to the history table:

UPDATE Person SET name = 'Jim' WHERE id = 100;
INSERT INTO PersonCH (2,100,'Jim',99,GETDATE(),123);

In reality, the INSERT statement above would be in an AFTER INSERT and / or AFTER UPDATE trigger so we don't need to rely on always specifying it in our application.

Therefore our history table now has an audit trail of this Person's changes which we can see using

SELECT * FROM PersonCH WHERE id = 100 ORDER BY RevisionCount DESC;

Upvotes: 3

Related Questions