Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design

When May You Bend or Break the Rules?

There are only two specific circumstances under which it is at all permissible to bend or break the rules of proper database design. Unless either of these is an inescapable imperative, you should use proper database-design techniques when designing your database.

Designing an Analytical Database

As you learned in Chapter 1, an analytical database stores and tracks historical and time-dependent data. This type of database often contains calculated fields within some of its table structures. The expressions used in many of these fields are meant to record the state of a particular set of data at a given moment in time; other fields store the results of aggregate functions.

You may have already surmised from the description that this type of database violates proper database design because its tables contain calculated fields (refer to Chapter 7). In this particular instance, the violation is acceptable because of the manner in which the data in the database is being used. I recommend that you properly design the database first and then break the rules only after judicious considerationyou should make a deliberate decision to break a rule and understand why doing so is necessary in the specific instance.

Note

Designing an analytical database requires a radically different design methodology than the one you learned in this book. If you determine that your organization requires an analytical database, I strongly recommend that you acquire a good book on the subject and learn how to design such a database properly.

Improving Processing Performance

This is by far the most common reason that people feel compelled to bend or break the rules. Whenever an RDBMS takes what seems to be an inordinate amount of time to process multitable queries or complex reports , many people believe that the solution to the problem is to alter the underlying table structures. For example, they would have you modify a table in such a way that it includes every field necessary for the query or report. While this modification does indeed increase the speed at which the RDBMS processes the query or report, it also introduces a number of new problems, such as unnecessary duplicate fields and redundant data. This is clearly not a desirable solution, because it violates proper database design.

Unfortunately, real life is not as ideal as we would like it to be, so you will sometimes find that you must decide between improving processing performance and holding to proper design principles.

Is It Worth It?

When you take a moment to really think about this dilemma, you'll soon realize that the question really isn't about performance; it's about data integrity. Anytime you break the rules for performance' sake (or any other reason, for that matter), you are surely going to introduce data-integrity problems. The question you must ask yourself, then, is this: Is the perceived increase in processing performance worth the price of reduced (and, therefore, weakened) data integrity? As you well know, the consequences of making imprudent modifications to your data structures will eventually spread, like ripples in a pond, throughout your database. Here are just a few of the problems you'll encounter:

• Inconsistent data . This is a result of introducing unnecessary duplicate fields into a table. It will be your responsibility (or that of your application program) to ensure that the data in these fields is synchronized; if you modify the value in a particular duplicate field, you'll have to make certain that the same modification is made to the remaining duplicate fields.

• Redundant data . Redundant data is also a result of introducing unnecessary duplicate fields into a table. When you edit a particular value in a field that contains redundant data, you must be sure to make the same modification for each instance of that value.

• Impaired data integrity . Bending or breaking the rules often violates one or more components of overall data integrity, such as table-level integrity and relationship-level integrity. It will be your responsibility (or that of your application program) to compensate for the lack of integrityin whatever way it manifests itselfas best as you can.

• Inaccurate information . You cannot possibly expect the database to provide accurate information if it has any of the aforementioned problems.

Improving Performance by Other Means First

If you still think you want to pursue this course of action in order to improve processing performance, do it only as a last resort . Before you take these measures, however, try to improve performance by some other means first. Consider these alternatives:

• Enhance or upgrade the computer hardware. In spite of the cost involved, this is still the easiest way to increase processing performance. A faster CPU, more memory, and a printer that better meets your requirements will all help to greatly decrease the time it takes the RDBMS to process a complex query or report. Using a larger hard drive will also help increase the retrieval speed for disk- intensive queries. Larger hard drives incorporate technology that produces extremely fast disk-access times.

• Fine-tune the operating system software. Make certain that the computer's operating system is optimized for peak performance. This is especially important for networked computers. You can greatly enhance general processing performance by working with the settings of the network's configuration options. The types of modifications you make to the operating system in general will depend on the type of software you're using for the operating system, so you'll have to refer to the software's documentation to determine what types of modifications you can make.

• Review the database structure. Make absolutely certain that the database is properly designed. It makes quite a difference. Poorly designed databases actually contribute to poor processing performance.

• Review the database's implementation. Examine how the database is currently implemented within the RDBMS. Make certain you've taken full advantage of the RDBMS's capabilities and defined the database as efficiently and completely as possible.

• Review the application program used to work with the database. Here's another area you should examine very closely. Is the application program well-written ? Does it make the best use of the tools the RDBMS provides? Are the application's components well-defined ? In some cases, a report may print more slowly because it is poorly designedthere may be more effective ways to design and generate the same report. Queries may run slowly because they are improperly defined. Make certain that each query is defined correctly and in the most efficient way possible.

If you believe you must depart from proper database-design techniques, carefully examine your situation. As I mentioned earlier, it's acceptable to suspend the rules if you are designing an analytical database. But I still strongly recommend that you design your database properly and thoroughly and relax the rules only for very specific reasons.