Redgate Data Modeler

20 November 2021

How to Work With Historical Data: Use a Flag to Mark Active Records

Often, there are objects in databases that should not be removed even though they are no longer used. Imagine a store database that includes the following tables:

What happens if the store decides a certain product type is no longer sold? Do you delete it from the database? That is probably not a good idea, as other objects, such as historical orders or invoices, may still be linked to this product type. However, we need a way to differentiate product types that are currently in use from the historical ones.

How to Distinguish Historical Records From the Ones That Are Actively Used

It’s simple – use a Boolean field such as is_active or is_supported. You can also create a view that queries the table for active elements only. Whenever the historical data is needed, query the whole table instead. Now, our model looks like this:

This solution is often used in dictionary tables, such as product types, tax rates, and inactive tariff plans.