History Tables and Audit Trails

Use case: Maintaining previous versions or an archive

Some business cases require a full history of changes available for audit trails. What’s usually done is to maintain live data in the main table, modeled with the rules we already saw, and model a specific history table covering where to maintain previous versions of the rows or an archive.

History table and denormalization

A history table itself isn’t a denormalized version of the main table but rather another version of the model entirely, with a different primary key to begin with.

What parts might require denormalization for history tables?

• Foreign key references to other tables won’t be possible when those reference changes, and you want to keep a history that, by definition, doesn’t change.

• The schema of your main table ...