Database structure to track change history

I have implemented pure record change data using a "thin" design:

RecordID  Table  Column  OldValue  NewValue
--------  -----  ------  --------  --------

You may not want to use "Table" and "Column", but rather "Object" and "Property", and so forth, depending on your design.

This has the advantage of flexibility and simplicity, at the cost of query speed -- clustered indexes on the "Table" and "Column" columns can speed up queries and filters. But if you are going to be viewing the change log online frequently at a Table or object level, you may want to design something flatter.

EDIT: several people have rightly pointed out that with this solution you could not pull together a change set. I forgot this in the table above -- the implementation I worked with also had a "Transaction" table with a datetime, user and other info, and a "TransactionID" column, so the design would look like this:

CHANGE LOG TABLE:
RecordID  Table  Column  OldValue  NewValue  TransactionID
--------  -----  ------  --------  --------  -------------

TRANSACTION LOG TABLE:
TransactionID  UserID  TransactionDate
-------------  ------  ---------------

I did something like this. I have a table called LoggableEntity that contains: ID (PK).

Then I have EntityLog table that contains information about changes made to a loggableentity (record): ID (PK), EntityID (FK to LoggableEntity.ID), ChangedBy (username who made a change), ChangedAt (smalldatetime when the change happened), Type (enum: Create, Delete, Update), Details (memo field containing what has changed - might be an XML with serialized details).

Now every table (entity) that I want to be tracked is "derived" from the LoggableEntity table - what it means that for example Customer has FK to LoggableEntity table.

Now my DAL code takes care of populating EntityLog table everytime there is a change made to a customer record. Everytime when it sees that entity class is a loggableentity then it adds new change record into entitylog table.

So here is my table structure:

┌──────────────────┐           ┌──────────────────┐
│ LoggableEntity   │           │ EntityLog        │
│ ──────────────── │           │ ──────────────── │
│ (PK) ID          │ ◀──┐      │ (PK) ID          │
└──────────────────┘    └───── │ (FK) LoggableID  │
        ▲                      │      ...         │
        │                      └──────────────────┘
┌──────────────────┐
│ Customer         │
│ ──────────────── │
│ (PK) ID          │
│ (FK) LoggableID  │
│      ...         │
└──────────────────┘

I'd say create some kind of event listening class that you ping every time something happens within your system & places a description of the event in a database.

It should store basic who/what/where/when/what info.

sorting through that project-events table should get you the info you want.