Normalising your database: Second Normal Form (2NF) - Part 2

 Jan 12, 2015

In the first part of the series, we looked at normalising into first normal form (1NF). In this article, the second part of the 3-part series, we focus on second normal form. Recall the general requirements of 2NF:
  • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
  • Create relationships between these new tables and their predecessors through the use of foreign keys.
These rules can be summarised in a simple statement: 2NF attempts to reduce the amount of redundant data in a table by extracting it, placing it in new table(s) and creating relationships between those tables.  Let's look at an example. Imagine an online store that maintains customer information in a database. They might have a single table called Customers with the following elements:
  • CustNum
  • FirstName
  • LastName
  • Address
  • City
  • State
  • Postcode
A brief look at this table might reveal a small amount of redundant data. If we imagined a few rows, we could be storing "Chermside, QLD 4034" and "Spring Hill, QLD 4001" entries twice. Now, that might not seem like too much added storage in our simple example, but imagine the wasted space if we had thousands of rows in our table. Additionally, if the postcode for Spring Hill were to change, we'd need to make that change in many places throughout the database. In a 2NF-compliant database structure, this redundant information is extracted and stored in a separate table. Our new table (let's call it Postcodes) might have the following fields:
  • ZIP
  • City
  • State
If we want to be super-efficient, we can even fill this table in advance (Australia Post provides a directory of all valid postcodes and their city/state relationships). Surely, you've encountered a situation where this type of database was utilised. Someone taking an order might have asked you for your postcode first then knew the city and state you were calling from. This type of arrangement reduces operator error and increases efficiency. Now that we've removed the duplicated data from the Customers table, we've satisfied the first rule of the second normal form. We still need to use a foreign key to tie the two tables together. We'll use the ZIP code (the primary key from the ZIPs table) to create that relationship. Here's our new Customers table:
  • CustNum
  • FirstName
  • LastName
  • Address
  • ZIP
We've now minimised the amount of redundant information stored within the database and our structure is in second normal form! Look out for part 3 of the series where we meet the final of our three normal forms!

How do your Excel skills stack up?   

Test Now  

About the Author:

Steve Wiggins  

Steve is a highly experienced technical trainer with over 10 years of specialisation in Software Application Development, Project Management, VBA Solutions and Desktop Applications training. His practical experience in .NET programming, advanced solution development and project management enables him to train clients at all levels of seniority and experience. Steve also currently manages the IT infrastructure for New Horizons of Brisbane, providing him with daily hands-on experience with SCCM, Windows Server 2012 and Windows 8.

Read full bio
top
Back to top