Database Tips: Include Deleted Data

When you design your application and database, most of the energy and time goes into how to load new entries into the database, or processes to modify the data already there.

But you also need to think about what to do when your data need to be removed. While it might seem simplistic just to delete those records you no longer need, this can cause other problems.

What do you do with those transactions when you want to delete the customer or product? If the master record is no longer there, the transactions may disappear from your reports. If you also delete the transactions, then the historical totals will be incorrect.

A safer approach is to include a field to indicate obsolete or deleted records. Keeping that historical data in your database will also keep your auditors happy. You just need to remember to exclude the deleted records from your reports. That is just a simple record selection rule to include in your report.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.