homepage Welcome to WebmasterWorld Guest from 54.145.243.51
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Normalisation
bizminder



 
Msg#: 4111286 posted 9:55 am on Apr 7, 2010 (gmt 0)

Can anybody tell me what is database normalisation. And why it is done?

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4111286 posted 3:47 pm on Apr 7, 2010 (gmt 0)

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-
[Employees]
EmployeeName | EmployeePhone | EmployeeID
Bob Bobbins | 555-5555 | 5

[CompanySoftballLeague]
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:
[EmployeeAddresses]
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:
[EmployeeAddresses]
EmployeeID | Street | ZIPCode
5 | 123 Main Street | 99987
7 | 321 1st Street | 99987

[ZIPCodes]
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.

damon_cool

5+ Year Member



 
Msg#: 4111286 posted 12:53 pm on Apr 20, 2010 (gmt 0)

Lookup 3NF or third normal form on wikipedia.

Some good examples there. It's database 101.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved