Application Error Trapping

August 27th, 2008

Before one line of code is written a proactive strategy for identifying, categorizing and reporting application errors needs to be developed.  Certainly, there are multiple logs, such as the server, database and even language logs, that can be examined to get some idea of what is not functioning properly on the site.  However, this is mostly a reactive approach.  A better way to handle the proactive and immediate identification of many errors is to incorporate error trapping and reporting functionality right from the start of the project.

The first place to start is with the database.  Database systems are designed and built with extremely good error trapping.  However, most developers and applications do not take advantage of this functionality, either because they are unaware of the richness and value of the information when there is a problem, but more likely it is initially easier and faster to bypass the inclusion of application code to adequately handle the errors.  Nonetheless, some type of database error will occur, ranging from an out of expected range parameter, to multiple returned rows when only one is expected, to no rows returned when one is expected or to a deadlock.  Knowing the database error, and possibly the specific parameters, at the time of execution saves time and effort in both tracking down the issue and developing a solution to prevent it from happening again.

The next place to incorporate error trapping is at the individual application page level.  Errors such as missing, unexpected or out of range global, session or page level variables need to be identified and reported.  Again, the idea is to be able to quickly and proactively identify problems and improve the ability to diagnose and correct any issues.

Whenever an error occurs, it is not the expected user experience.  In many cases, the end-user sees a long, extremely tech specific message on the screen and is pretty much left confused and, in many cases, annoyed.  Therefore, it is important to attempt to present the end-user with some type of nicely designed and worded message and/or page acknowledging the error and informing them that someone will be looking at the error to try to prevent it from happening in the future.

Once you’ve come up with a means by which you can adequately handle page level errors you must then develop a scheme for reporting the errors to the appropriate person within the organization for further investigation and resolution.  You should also store the error so that you have enough information to review the errors and perform analysis in the future.  The easiest way to perform these tasks is to have the application send an email message to the appropriate person regarding the error and then to process and store some of the basic information in an error database.

Again, it takes much less effort and time to incorporate error trapping into the application as it is being built than it does to have to go back and retrofit it.  It also makes the identification and resolution of the inevitable issues that come up much easier and faster to resolve, resulting in a better experience for your site visitors.

One area I didn’t talk about in this post was site monitoring which I will leave for another day.

Database Design for Developers

August 15th, 2008

Many people consider that the role of the database modeler on the development team is to merely identify the entities, attributes and relationships and throw in a few indexes for good measure.

While this can provide enough information for the development team to get started and complete their work, the ongoing application development, maintenance and documentation processes can all achieve significant improvements in efficiency, particularly when larger teams and longer development efforts are involved, by spending additional time on several other aspects of the database design. In this case, a little more effort put in at the beginning will yield significant savings over the life of the application.

The first thing that should always be done is to create and publish a document outlining database object naming conventions and rules. As a minimum, this document should include naming conventions for the following:

* Entities (usually singular nouns)
* Tables (usually plural nouns)
* Attributes and columns, in particular standardize the following:
o Identity columns
o Whether True/False or Y/N will be used when binary values are used
o How application-wide codes will be named, implemented and documented
* Indexes
* Primary, unique and foreign keys
* Stored procedures
* Constraints

The development of this document always results in a lively discussion as everyone has somewhat differing opinions of how the objects should be named and used. For the most part many different ways will work. However, the most important aspects are that this document is created at the very beginning of the design process and that it is rigidly adhered to, not only when the project starts but as enhancements and changes are made later on.

Why is this document important? First and foremost it will significantly improve the efficiency of the developers. They will know, without having to spend time looking it up every time, whether a binary value column uses the values ‘T/F’ or Y/N’ or whether a table’s name is plural or the name of the identity column. As new developers are added to the team, their ramp-up time is significantly reduced and they will be more effective, sooner and with less distraction to other developers.

One other area that should be decided during database design is how various application and system wide codes will be handled. In many instances an application ‘codes’ table is created with columns to sufficiently describe, identify and document the various codes that will be used.

Next time I will talk about using the database to ensure application-wide data integrity.