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.