Best practices for history/temporal tables?

Question

Suppose I have an object, with certain fields which I want to track history, and certain fields which I do not want to track history. From a normalization perspective, is the following schema alright:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

where MyObjectHistory contains the tracked fields for all except for the latest revision. Or, should all tracked fields be in one table, and all revisions including the latest be in that table, as in:

CREATE TABLE MyObject AS (
    MyObjectId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectField1 VARCHAR(100) NOT NULL,
    MyObjectField2 VARCHAR(100) NOT NULL,
    MyObjectField3 VARCHAR(100) NOT NULL,
)
CREATE TABLE MyObjectHistory AS (
    MyObjectHistoryId INT IDENTITY NOT NULL PRIMARY KEY,
    MyObjectId INT NOT NULL FOREIGN KEY REFERENCES MyObject(MyObjectId),
    MyObjectTrackedField1 VARCHAR(100) NOT NULL,
    MyObjectTrackedField2 VARCHAR(100) NOT NULL,
    MyObjectTrackedField3 VARCHAR(100) NOT NULL,
)

Answer 1

For practical data access reasons, you should use the structure from your first option, but instead keep all versions of your tracked column values including the current version in your history table.

The reason for this is that in general, when you want to look at history, you want to include the present and all past versions. When you don't want to look at history, you want it out of the way. In many cases this means going so far as to segregate the history into a separate schema or database altogether. Even if you keep your history in the same schema as your current data, any queries that look at historical data (including the current values) will be much more complex since they have to essentially union two sources.

Answer 2

I would prefer the first version because you probably only rarely need to see the history but you will frequently need to see the current value. A history table should be populated from a trigger, so you don't need to worry about the data getting out of synch generally. So suppose you have a million records in MyObject and then you have 10,000,000 record in MyObjectHistory. Do you really want to join to a table with that many records to get the current value?

Now if you are going to need to query the history as freqently or more frequenlty than the current value, then the second struture would work. (And if you are going to be displaying the value as of a particular date, I would have a begindate and enddate field in it to make querying simpler.)

BTW I would add a date field to the history table to be able to tell what order the changes happened. You can't rely on identities for temporal order. PLus if there is a question about a previosu value and when it changed, you will need to knwo. I might also put in values for the application the change came from (if you have multiple applications) and/or the person who made the change.

Answer 3

There are a couple of important reasons for #1. The first is the size issue HLGEM points out but the there are other important ones as well.

Typically your audit trail is going to have requirements develop over time. You may end up wanting to track database users, time of change, etc. The audit trail requirements and your main table are likely to change over time somewhat independently. Finally you are likely to want to purge audit trail data after a period of time independently and a fully separate table.

Of course there may be cases where you want to fully merge them (like we do for tax rates in LedgerSMB) because historical data may be used for current calculations and the number of records is likely to be relatively small.

I am going to suggest, however, that storing objects in tables like this rarely leads to good, normalized designs. In my experience, you really want some encapsulation between good normalized storage and an application object model.