SQL Server normalization rules you must follow
Greg Robidoux, Contributor
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in August 2005
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 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.
| |
| Computer |
Software |
| 1 |
Word |
| 2 |
Access, Word, Excel |
| 3 |
Word, Excel |
To follow the First Normal Form, we store one type of software for each record.
| |
| Computer |
Software |
| 1 |
Word |
| 2 |
Access |
| 2 |
Word |
| 3 |
Excel |
| 3 |
Word |
| 3 |
Excel |
Rules for second Normal Form (2NF)
Eliminate redundant data plus 1NF. This table contains the name of the software which is
redundant data.
| |
| Computer |
Software |
| 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.
| |
| Computer |
SoftwareID |
| 1 |
1 |
| 2 |
2 |
| 2 |
1 |
| 3 |
3 |
| 3 |
1 |
| 3 |
3 |
| |
| SoftwareID |
Software |
| 1 |
Word |
| 2 |
Access |
| 3 |
Excel |
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 |
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
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation