When most people think of Microsoft Excel, they think of graphs, formulas, financials, etc.  But by scratching slightly below the surface, one will notice that Excel is extremely powerful at more than just the everyday crunching and presentation of data.

In working with databases on a regular basis, there are tedious tasks that can take valuable time away from expanding, solidifying and tuning the database itself.  Of course there are 3rd party tools that can be used for database administration.  However, for data related issues that require large numbers of SQL statements to insert or update records, or to perform in-depth analysis of large amounts of data, I find that there is no better tool than Microsoft Excel.

For many companies and government agencies, accurate Audit and History information is important and in some cases, mandated.

For the purposes of this article, the following definitions are presented:
• Audit – which record was inserted, updated, or deleted, when, and by who
• History – storage of earlier values from records that have changed

Considerations for Audit and History System Designs
In designing a comprehensive Audit and History (A/H) system, a number of considerations must be made.

Among them:
• Is this A/H functionality being built into the system early in the design process or is an existing system being retrofitted with the A/H functionality?
• For what data should A/H  be maintained?  Partial?  Complete?
• Will the history information tie into systems external to the databases/file systems under direct control of the immediate system?
• Where should the functionality be located – client, midtier, or database?
• Is A/H important enough that transactions should fail if the A/H fails?
• If the A/H functionality is based in the database, will the client log in to the database with the user’s own id or one or more generic accounts?
• How long should A/H information be stored?