Propose database changes. I've converted my list of changes for the Cellar database to the code that Web Listing 3 shows. I propose to break the CELLAR table into two tables: WINETYPE and WINESTORAGE. The WINETYPE table contains a list of the wines in the cellar, the wine producers, the years the wines were produced, each wine's ready date, and comments about each wine. I uniquely identify each entry in the WINETYPE table with a WineID (the primary key). The associated WINESTORAGE table contains an entry for each storage bin number; the entry includes the WineID of the wine that's stored in the bin, the number of bottles, and the cost per bottle. WINESTORAGE is an enforced reference to WINETYPE because I want to restrict storage of wines in the WINESTORAGE table to only those wines identified in the WINETYPE table.
I also believe that I need to decompose the VISITOR table into two tables: PERSON and PERSONPHONE. The PERSON table contains a list of all the friends who come to the wine tastings; I uniquely identify each person with a VID. The PERSONPHONE table contains phone numbers and phone types related to each VID. If a person has three phones (a home phone, a work phone, and a cell phone) in the VISITOR table, then that person will have three entries (three rows) in the PERSONPHONE table. The relationship between PERSON and PERSONPHONE is 1:M and is also an enforced relationship because I don't want a phone number in PERSONPHONE that doesn't have an associated record in the PERSON table.
The code in Web Listing 3 also contains SQL statements that I can use to transfer data from CELLAR to WINETYPE and WINESTORAGE, and from VISITOR to PERSON and PERSONPHONE. I can create the new tables while production programs are accessing the old tables. But I need to load the new tables while the old tables are offline to avoid losing data modifications to the old tables while the new tables are loading.
Design views that support existing applications. The code that Web Listing 3 shows contains the Cellar and Visitor views that will recreate virtual copies of the CELLAR and VISITOR tables. Before I can create these new views, I need to rename the original CELLAR and VISITOR tables, so I have to take them offline.
In the Cellar view, WINETYPE and WINESTORAGE are joined together as an inner join to recreate the layout of the old CELLAR table. However, in the Visitor view, to recreate the phone array that was in the VISITOR table, the join operation has to pivot the data to display HomePhone, WorkPhone, and CellPhone in one record with their associated person.
Implement the changes. If the client agrees with my suggestions for normalizing the Cellar database, and the DBA gives me time to work during a maintenance window when no production programs will be accessing the tables, I implement the code in Web Listing 3. After I make the changes, I can diagram the database as Figure 5, page 35, shows.
Monitor performance. After all the restructuring is finished, I ask the DBA to track performance to determine whether any appreciable degradation has occurred. The DBA needs to pay special attention to the Visitor view because the number of queries required to recreate the old VISITOR table has increased from one to three. If performance has dropped, the client has two options: He can enhance performance with more powerful hardware and a greater bandwidth network, if necessary, or he can have his development programmers rewrite the production programs to take advantage of the new user table architecture.
New Life
Normalizing a database is easiest at the very beginning of development, before the database goes into production. However, such an ideal situation doesn't happen often. To normalize an existing production database, you need to know what the client uses the database for, what each of the tables mean, and how each field in each table relates to other fields in the table so that the changes you make won't adversely affect existing production. The changes that you make to a production database can make it scalable and extendable and can reduce the instances of data corruption. By properly normalizing a production database, you can give it a new lease on life.
End of Article
Prev. page
1
2
3
[4]
next page -->
