In this tip we will take a look at database normalization and the advantages and disadvantages of normalization for SQL Server databases.
What is normalization?
Normalization is the process of designing a data model to efficiently store data in a database. The end result is that redundant data is eliminated, and only data related to the attribute is stored within the table.
For example, let's say we store City, State and ZipCode data for Customers in the same table as Other Customer data. With this approach, we keep repeating the City, State and ZipCode data for all Customers in the same area. Instead of storing the same data again and again, we could normalize the data and create a related table called City. The "City" table could then store City, State and ZipCode along with IDs that relate back to the Customer table, and we can eliminate those three columns from the Customer table and add the new ID column.
Normalization rules have been broken down into ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
several forms. People often refer to the third normal form (3NF) when talking about database design. This is what most database designers try to achieve: In the conceptual stages, data is segmented and normalized as much as possible, but for practical purposes those segments are changed during the evolution of the data model. Various normal forms may be introduced for different parts of the data model to handle the unique situations you may face.
Whether you have heard about normalization or not, your database most likely follows some of the rules, unless all of your data is stored in one giant table. We will take a look at the first three normal forms and the rules for determining the different forms here.
Rules for First Normal Form (1NF)
Eliminate repeating groups. This table contains repeating groups of data in the Software column.
ComputerSoftware
1 Word
2 Access, Word, Excel
3 Word, Excel
To follow the First Normal Form, we store one type of software for each record.
Rules for second Normal Form (2NF)
Eliminate redundant data plus 1NF. This table contains the name of the software which is redundant data.
ComputerSoftware
1 Word
2 Access
2 Word
3 Excel
3 Word
3 Excel
To eliminate the redundant storage of data, we create two tables. The first table stores a reference SoftwareID to our new table that has a unique list of software titles.
Rules for Third Normal Form (3NF)
Eliminate columns not dependent on key plus 1NF and 2NF. In this table, we have data that contains both data about the computer and the user.
Computer User Name User Hire Date Purchased
1 Joe 4/1/2000 5/1/2003
2 Mike 9/5/2003 6/15/2004
To eliminate columns not dependent on the key, we would create the following tables. Now the data stored in the computer table is only related to the computer, and the data stored in the user table is only related to the user.
Computer Purchased
1 5/1/2003
2 6/15/2004
User User Name User Hire Date
1 Joe 5/1/2003
2 Mike 6/15/2004
Computer User
1 1
2 1
What does normalization have to do with SQL Server?
To be honest, the answer here is nothing. SQL Server, like any other RDBMS, couldn't care less whether your data model follows any of the normal forms. You could create one table and store all of your data in one table or you can create a lot of little, unrelated tables to store your data. SQL Server will support whatever you decide to do. The only limiting factor you might face is the maximum number of columns SQL Server supports for a table.
SQL Server does not force or enforce any rules that require you to create a database in any of the normal forms. You are able to mix and match any of the rules you need, but it is a good idea to try to normalize your database as much as possible when you are designing it. People tend to spend a lot of time up front creating a normalized data model, but as soon as new columns or tables need to be added, they forget about the initial effort that was devoted to creating a nice clean model.
To assist in the design of your data model, you can use the DaVinci tools that are part of SQL Server Enterprise Manager.
Advantages of normalization
1. Smaller database: By eliminating duplicate data, you will be able to reduce the overall size of the database.
2. Better performance:
a. Narrow tables: Having more fine-tuned tables allows your tables to have less columns and allows you to fit more records per data page.
b. Fewer indexes per table mean faster maintenance tasks such as index rebuilds.
c. Only join tables that you need.
Disadvantages of normalization
1. More tables to join: By spreading out your data into more tables, you increase the need to join tables.
2. Tables contain codes instead of real data: Repeated data is stored as codes rather than meaningful data. Therefore, there is always a need to go to the lookup table for the value.
3. Data model is difficult to query against: The data model is optimized for applications, not for ad hoc querying.
Summary
Your data model design is both an art and a science. Balance what works best to support the application that will use the database and to store data in an efficient and structured manner. For transaction-based systems, a highly normalized database design is the way to go; it ensures consistent data throughout the entire database and that it is performing well. For reporting-based systems, a less normalized database is usually the best approach. You will eliminate the need to join a lot of tables and queries will be faster. Plus, the database will be much more user friendly for ad hoc reporting needs.
About the author: Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux serves as the SearchSQLServer.com Backup and Recovery expert and welcomes your questions.
When are you planning on adopting SQL Server 2005? Edgewood Solutions would like to know. Please take the survey today.
More information from SearchSQLServer.com
Tip: SQL Server's DaVinci code for data modeling
Chapter download: Designing effective database systems
Topic: Research database modeling and design topics
