Database Systems Audit

Your organisation already uses databases, but you want to be sure it is performing correctly and efficiently. Why would it not?
Even if your database began life as a well-designed application with no idiosyncrasies or inconsistencies, over time, this state of perfection can degrade. Some of the reasons for this degradation are:

• Improper entry of data
  • This isn't to say that the user is at fault, as such - simply that the data entered doesn't fit the original design concepts for the data, or that duplications arise because of slight misspellings, for instance.
• Change of use of data
  • The purpose of some data has evolved over the course of the database's existence, making its consistency constraints and properties no longer correct.
• Subversion of data consistency checks
  • Sometimes, for reasons of expediency, data checks may be turned off or circumvented. While this might solve a short-term problem, in the long term, the database becomes less and less reliable and therefore less and less useful.
• Software errors
  • Despite the claims of some software manufacturers, no programme is ever completely free of errors. There will always be some uncommon circumstances which lead to incorrect handling, and in these cases, data integrity is degraded.
• Application extensions
  • This is really a special case of the previous point, but it is more prevalent. Functionality that is added to a database application after it has been in operation for some time is often designed by different engineers who may have a less than perfect understanding of the complete design. This can lead to data mishandling.

All of these problems, and many others, can be corrected by a professional audit of the database design and of the data itself.

Schema documentation

As a first step, the entire database schema is documented. The schema is the description of the data elements of the database, including the field types and sizes, their attributes, constraints, default values, index properties, relationships, link types etc. as well as view (or query) design.

The schema is an essential tool for identifying potential problems related to the data design (as opposed to form and report design), and is useful as a starting point for making modifications and extensions.

Troubleshooting and data correction

Using the schema, problems in the design can be identified and a plan constructed for correcting the problems. In order to correct a design without losing information, it may be necessary to create new queries and functions. Faulty data can also sometimes be corrected, but this may need to be accomplished in collaboration with the data users.

Optimisations

• Schema rationalisation: while not being formally wrong, inconsistencies in naming, in field definitions, constraints etc, can cause confusion in use and potential problems for future modifications and extensions.
• Schema normalisation: a normalised schema ensures that data is stored once only, saving space and preventing inconsistencies. It also ensures that SQL access to the data will succeed.
• Speed optimisation: much can often be done to avoid unnecessary data transfers between client and server, thereby speeding up data access.

After changes have been made, a new schema document will be created.

Regular audits

A database audit is recommended on a regular basis in order to keep a database in tip-top condition and performing to expectations. On request, a review of the user interfaces (navigation, form design, report design, etc) as well as application functions, backup policies, security issues etc can also be undertaken.