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.

Another approach is to create a separate, archival database for obsolete records. Export the records you want to archive, then delete them from the database.

You can use this same principle to archive e-mails. In Outlook, for example, create an archive datafile for each user, and let users create whatever archival file structure suits them. These datafiles can be located on the server, if users are storing their normal e-mail on their local drives (preferably, all e-mail will be on the server). When it's time to archive, just drag and drop.

In a general management of data, you could adopt this approach (with relevant procedures and safeguards) to all systems, not just your database or e-mail system. Most likely, you have many information repositories that need to be cleaned up and downsized.

Something you could also consider is what to do with the paper records that your company probably has lying around in dusty boxes. You could probably just toss all of them, but that might lead to an unpleasant surprise later. Still, they are taking up space and can't be searched.

A good solution is to ask each department head to sort through the boxes pertaining to his/her department and retain anything important for digitization. You will need a good business-class flatbed scanner for that. Be sure to time-bind this, or it won't get done. So, set a "remove boxes by" date, and then the day after that date passes you can toss all the boxes. Be sure to send out a reminder two weeks in advance and then again every day for the three days prior.

If there's no cost associated with digitization, the department managers will just want it all digitized. So if you are going to hire a temp worker to scan the files (into predesignated folders by department), be sure to calculate the cost per document and let the department heads know this is what they'll be charged if they have your department handle it.

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.