How to deal with old, obsolete database data of a long running system?

Question

What are the possibilities of a programmer to handle data that are rarely used but cannot be simply deleted because at least reporting still requires it?

Some examples I am thinking of:

• Discountinued funding types of older years of a university
• Unused currencies (e.g. Italian lira)
• Names of disappeared countries (e.g. Austro-Hungary, USSR)

Some partial solutions are activity flags, activity periods, priorities of visualization but each of them means a case by case decision and it is hard to know what types of entities need this special handling.

May be there is a design pattern for this problem.

Conclusions: (based on the answers so far)

• If old data makes everyday work difficult on a huge database, partitioning would be helpful. Oracle's description on this subject is here.

• From the point of view of the designer the taxonomy of Slowly changing dimension gives some background information.

Answer 1

With old data not used in most queries the best solution is to partition tables by the the key which differentiates stale from current data (such as date, currency_id or things like that). You can then put the stale data in separate tables, databases or even servers (depending on the configuration you have running).

The downside to this is that your application must become partition-aware to know where to find the data (though there are abstractions that help deal with sharding and partioning).

Answer 2

For any entity which can have a limited lifetime, just add a time-component in its definition. E.g. your Italian Lira can be modeled as:

CREATE TABLE Currency (CurrencyID NUMBER, CurrencyStartDate DATETIME, CurrentEndDate DATETIME)

You can then exclude the expired currencies from any application functions related to current activity, and still maintain the relationship for historical data.