The Importance of Good Database Design
A good database design is crucial for a high performance application, just like an aerodynamic body is important to a race car. If the car doesn't have smooth lines, it will produce drag and go slower. Without optimized relationships, your database won't perform as efficiently as possible. Thinking about relationships and database efficiency is part of normalization.
Normalization refers to the process of structuring data in order to minimize duplication and inconsistencies.
Beyond the issue of performance is the issue of maintenanceyour database should be easy to maintain. This includes only storing a limited amount (if any) of repetitive data. If you have a lot of repetitive data and one instance of that data undergoes a change (such as a name change), that change has to be made for all occurrences of the data. To eliminate duplication and enhance your ability to maintain the data, you might create a table of possible values and use a key to refer to the value. That way, if the value changes names, the change occurs only oncein the master table. The reference remains the same throughout other tables.
For example, suppose you are responsible for maintaining a database of students and the classes in which they're enrolled. If 35 of these students are in the same class, let's call it Advanced Math, this class name would appear 35 times in the table. Now, if the instructor decides to change the name of the class to Mathematics IV, you must change 35 records to reflect the new name of the class. If the database were designed so that class names appeared in one table and just the class ID number was stored with the student record, you would only have to change 1 recordnot 35in order to update the name change.
The benefits of a well-planned and designed database are numerous, and it stands to reason that the more work you do up front, the less you'll have to do later. A really bad time for a database redesign is after the public launch of the application using italthough it does happen, and the results are costly.
So, before you even start coding an application, spend a lot of time designing your database. Throughout the rest of this chapter, you'll learn more about relationships and normalization, two important pieces to the design puzzle.