Technology News, Tips, Reviews, Analysis | Subscribe to Jason Slater Technology BlogTechnology News Feed | Join Jason Slater on TwitterTwitter | Tuesday 7th September 2010

Database Normalisation (Making Sense of Data) – 3NF Third Normal Form

By Jason Slater
  • DiggThis
  • Share

3NF or Third Normal Form

For a proposed data table to meet 3NF requirements it must already be 2NF. The further requirements for 3NF are very similar to 2NF however we must now compare non-key fields with each other (remember in 2NF we were comparing non-key fields with parts of a combined primary key).

So, in our previous example we ended up with 2 tables.

Table 1: Username,Computer with a Primary Key of (Username,Computer)
Table 2: Username,Fullname with a Primary Key of (Username)

As it is our tables would both meet 3NF requirements however to demonstrate the point lets add some more fields to Table 2. Say, Address, Postcode and Email Address.

Table 2: Username,Fullname,Address,Postcode, Email Address with a Primary Key of (Username)

So,

If we know the Fullname can we find out Address,Postcode, or Email Address ? NO because the person may have multiple addresses
If we know the Address can we find out Fullname,Postcode, or Email Address ? NO because more than one person may live at the address
If we know the Postcode can we find out Fullname,Address, or Email Address ? NO because houses share the same postcode
If we know the Email Address can we find out Fullname, Address, or Postcode ? NO because email addresses are typically assigned to usernames, not full names

We could argue about email address, or indeed the fullname against the address (land registry or council records possibly) and that is part of the fun of data normalisation.

However, in this example it means the table meets Third Normal Form.

Related

Leave a comment!

Add your comment below, or trackback from your own site. You can also subscribe to these comments via RSS.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.