Redgate Data Modeler

27 November 2021

How to Work With Historical Data: Use Separate Tables for Active and Historical Data

What do you do if you need to keep both current and historical versions of your database records? If there are a lot of them, storing everything in one table may cause performance problems and complicate logic, especially if most of the operations are performed on the active data.

You can solve this problem by storing the current and historical versions in separate tables. In our example, the current and historical account plans were put in two different tables: current_account_plan and account_plan_history.

With such a design, you can query just one of the tables and reduce the number of records that need to be scanned.