Database - Data Versioning [closed]
I've read a few questions on SO (such as this one) in regards to versioning your data within a database.
I liked some of the suggestions that were mentioned. I have for the longest time wanted (needed) to revision many of my tables but never got around to it. Being a programmer with only simple database work under my belt I was wondering how one would actually go about doing this.
I'm not asking for the actual solution in SQL syntax. I can eventually figure that out for myself (or post SO when the time comes). I'm just asking for people to comment as how they would go about doing it and any potential performance problems there might be if I was to 'revision' hundreds of million of records. Or any other suggestions as long as it is based on the example below.
Given a simple example:
Person
------------------------------------------------
ID UINT NOT NULL,
PersonID UINT NOT NULL,
Name VARCHAR(200) NOT NULL,
DOB DATE NOT NULL,
Email VARCHAR(100) NOT NULL
Audit
------------------------------------------------
ID UINT NOT NULL,
UserID UINT NOT NULL, -- Who
TableName VARCHAR(50) NOT NULL, -- What
OldRecID UINT NOT NULL, -- Where
NewRecID UINT NOT NULL,
AffectedOn DATE NOT NULL, -- When
Comment VARCHAR(500) NOT NULL -- Why
I'm not sure how one would link the Audit table to any other tables (such as Person) if the TableName is a string?
Also, assuming that I have three GUI's to populate:
- A full record for a specific person id
- A table view listing all persons (by id)
- A view showing each person with their revision info below each entry (# of revisions per person, dates of revisions, revision comments, etc), ordered by the most recent revisions.
To accomplish 1 and 2, would it be better to query the Person table or the Audit table?
To accomplish 3, would a so called database expert simply get all records and pass it on to the software for processing, or group by PersonID and Affected date? Is this usually handled in one query or many?
Solution 1:
I have done various audit schemes over the years and I am currently going to implement something like this:
Person
------------------------------------------------
ID UINT NOT NULL,
PersonID UINT NOT NULL,
Name VARCHAR(200) NOT NULL,
DOB DATE NOT NULL,
Email VARCHAR(100) NOT NULL
Person_History
------------------------------------------------
ID UINT NOT NULL,
PersonID UINT NOT NULL,
Name VARCHAR(200) NOT NULL,
DOB DATE NOT NULL,
Email VARCHAR(100) NOT NULL
AuditID UINT NOT NULL
Audit
------------------------------------------------
ID UINT NOT NULL,
UserID UINT NOT NULL, -- Who
AffectedOn DATE NOT NULL, -- When
Comment VARCHAR(500) NOT NULL -- Why
The current records are always in the Person table. If there is a change an audit record is created and the old record is copied into the Person_History table (note the ID does not change and there can be multiple versions)
The Audit ID is in the *_History tables so you can link multiple record changes to one audit record if you like.
EDIT:
If you don't have a separate history table for each base table and want to use the same table to hold old and "deleted" records then you have to mark the records with a status flag. The problem with that it's a real pain when querying for current records - trust me I've done that.
Solution 2:
How about you create the table as normal, have a ModifiedDate Colm on each record (and ModifiedBy if you like), and do all your data access through a materialized view which groups the data by Id and then does a HAVING ModifiedDate = MAX(ModifiedDate)?
This way, adding a new record with the same Id as another will remove the old record from the view. If you want to query history, don't go through the view
I've always found maintaining different tables with the same Colm to be complex and error prone.
edit: I've just returned to this answer 12 years after I wrote it. I would say that the the original question is misguided - you should be auditing user level events, not changes to database columns.