15
Bending or Breaking the Rules
Nature never breaks her own laws.
—LEONARDO DA VINCI
Topics Covered in This Chapter
When May You Bend or Break the Rules?
I always advocate following proper database design techniques. As you’ve already learned, there are numerous reasons for doing so. But first and foremost, you should use a good design method to ensure the integrity of the database. I cannot overstate how important this is. You now know the consequences of improperly establishing data integrity, so following the rules is of paramount importance.
When May You Bend or Break the Rules?
Only two specific circumstances exist 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, “Relational Databases,” 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, “Establishing Table Structures”). 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 consideration—you 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
Even in this age (2020 at the time of this writing), improving processing performance is still 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. Although this modification does indeed increase the speed at which the RDBMS processes the query or report (particularly in older systems), it also introduces a number of new problems, such as unnecessary duplicate fields, redundant data, and problems when editing the data; you basically keep trading one performance problem for another. 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 the sake of performance (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 integrity—in whatever way it manifests itself—as 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 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. Hardware has really come a long way in the past 25 years, so this is still the easiest way to increase processing performance. Items such as a faster CPU, more memory, switching to solid-state devices, obtaining a printer that better meets your printing requirements, and upgrading the network will all help to greatly decrease the time it takes the RDBMS to process a complex query or report.
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 and server hardware. You can greatly enhance general processing performance by working with the configuration options settings. The types of modifications you make to the operating system in general will depend on your operating system, so you’ll have to refer to your 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 designed—there 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.
Documenting Your Actions
If you’ve exhausted all other options and still come to the conclusion that you need to bend or break the rules, then you must document each rule you break and each action you take! Documenting your changes is important because doing so will compel you to think about the consequences of what you are about to do, and it provides a means of recording the changes you make to the database structure. Should you decide later that the modifications did not provide significant benefits, you can use the documentation as a guide to reverse the modifications you initially made.
These are the items that you should record.
The reason you’re breaking the rules: Increasing processing performance and decreasing the time it takes to print complex reports are two of the most common reasons for breaking the rules. Whatever your reason, be sure to state it thoroughly and clearly.
The design principle you’re violating: Recording how you’ve altered the database design will give you the means to reverse these changes later should you determine that performance did not significantly improve. You might indicate that you’re altering the structure of a table, for example.
The aspect of the database that you’re modifying: Indicate which particular field, table, relationship, or view you are going to alter. Once again, this information will be valuable should you decide to reverse the modifications.
The specific modifications you are making: After you determine which item you need to modify, record the exact modifications you make to that item. For example, if you need to modify a relationship, note the exact changes you make to its characteristics.
The anticipated effects on the database and the application program: Any modifications you make to the database are going to affect all accompanying end-user application programs. For example, altering the structure of a particular table can affect data integrity, view structures, data entry forms and reports built upon the table (either partially or totally), and programming code that refers to the table. You must be sure to list every effect.
Add this document to the documentation you compiled for the database. Even if you reverse the changes later, this record could prevent you from yielding to a future impulse to attempt the same types of changes.
Summary
The chapter opened by examining the two circumstances under which you might feel compelled to depart from proper database design techniques. You learned that breaking the rules is acceptable if you are designing an analytical database; otherwise, you should design the database properly first and then make deliberate decisions to break or bend specific rules. You then learned that the most common reason for departing from proper design techniques is to improve processing performance. Although this is not a satisfactory reason for breaking the rules, there are times when circumstances dictate that you must consider such changes.
We then continued with a discussion of the alternate measures you can take to improve processing performance, such as enhancing or upgrading the hardware and reviewing the implementation of the database. You learned that you should do all you can to improve performance first and depart from proper design techniques only as a last resort. The chapter then closed with a list of items you should record if you need to break the rules.