147
Chapter 7: Relational Database Design and Creating the Physical Database Schema
7
Implementing a database without working through the SQL DLL Layer design phase is a certain path to a poorly
performing database. Many database purists didn’t care to learn SQL Server implement conceptual designs only to
blame SQL Server for the horrible performance.Normalization
In 1970, Dr. Edgar F. Codd published “A Relational Model of Data for Large Shared Data
Bank” and became the father of the relational database. During the 1970s Codd wrote a
series of papers that defi ned the concept of database normalization. He wrote his famous
“Codd’s 12 Rules” in 1985 to defi ne what constitutes a relational database and to defend the
relational database from software vendors who were falsely claiming to be relational. Since
that time, others have amended and refi ned the concept of normalization.The primary purpose of normalization is to improve the data integrity of the database by
reducing or eliminating modifi cation anomalies that can occur when the same fact is stored
in multiple locations within the database. In other words, the process of normalization
attempts to reduce redundant data that causes unnecessary updates.Duplicate data raises all sorts of interesting problems for inserts, updates, and deletes. For
example, if the product name is stored in the order detail table, and the product name is
edited, should every order details’ row be updated? If so, is there a mechanism to ensure
that the edit to the product name propagates down to every duplicate entry of the product
name? If data is stored in multiple locations, is it safe to read just one of those locations
without double-checking other locations? Normalization prevents these kinds of modifi ca-
tion anomalies.In addition to the primary goal of consistency and data integrity, several other good rea-
sons to normalize an OLTP relational database exist:■ (^) Performance: Duplicate data requires extra code to perform extra writes, maintain
consistency, and manipulate data into a set when reading data. Addressing the
aforementioned issues is even more problematic when dealing with large highly
transactional databases. Imagine a 2-terabyte database that averages approxi-
mately 30K transactions per second. Moreover, assume that the database is the
back end for a large retail store. If a change were made to one product or item,
that change would need to be propagated across every table that referenced that
product. This could be tens or even hundreds of tables, resulting in a 10–15 percent
system performance degradation.
Normalization also reduces locking contention and improves multiple-user concur-
rency because you need fewer updates.
■ Development costs: Although it may take longer to design a normalized database,
it’s easier to work with a normalized database, and it reduces development costs.
c07.indd 147c07.indd 147 7/30/2012 4:18:10 PM7/30/2012 4:18:10 PM
http://www.it-ebooks.info