Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies


9:55 am on Apr 7, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Mar 15, 2010
votes: 0

Can anybody tell me what is database normalisation. And why it is done?
3:47 pm on Apr 7, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
votes: 24

From Wikipedia [en.wikipedia.org]
In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies—that could lead to a loss of data integrity.

On a basic level, what that means is that you don't want data that you may update in more than one table. Similarly, you want to have common data separated into a different table to reduce duplicate data.

Example 1:
Non-normalized tables-
EmployeeName | EmployeePhone | EmployeeID
Bob Bobbins | 555-5555 | 5

EmployeeName | EmployeePhone | Position
Bob Bobbins | 555-5555 | Catcher

If Bob ever changes his phone number, you have to update the phone number in every single table where Bob's record may appear. (Similarly, if an employee changes his/her name, you would also have to update every field where Bob's record may appear.)

The way to normalize the second table would be as follows:
EmployeeID | Position
5 | catcher

If Bob Bobbins changes his phone number or name, you only update the Employees table.

Example 2:
EmployeeID | Street | City | State | ZIPCode
5 | 123 Main Street | Smallville | CA | 99987
7 | 321 1st Street | Smallville | CA | 99987

You know that the 99987 ZIPCode is for Smallville and that Smallville is in California. So you can normalize as follows:
EmployeeID | Street | ZIPCode
5 | 123 Main Street | 99987
7 | 321 1st Street | 99987

City | State | ZIPCode
Smallville | CA | 99987

(Again, if the city name changes for the 99987 ZIPCode, you only need to update the ZIPCodes table.)

There are times when you don't want to completely normalize your database. In Example 1, maybe you want to use Bob's home phone number for company records, but his cell phone for the softball league. In example 2, one ZIP Code may have 1 official name and 1 or more acceptable names and a person may prefer to use the unofficial name.
12:53 pm on Apr 20, 2010 (gmt 0)

New User

5+ Year Member

joined:Oct 15, 2009
votes: 0

Lookup 3NF or third normal form on wikipedia.

Some good examples there. It's database 101.

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members