Database - Data Versioning

Question

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:

1. A full record for a specific person id
2. A table view listing all persons (by id)
3. 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?

Answer 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.