Over the past month, we've looked at several aspects of normalizing a database table. First, we discussed the basic principles of database normalization. Last time, we explored the basic requirements laid down by the first normal form (1NF). Now, let's continue our journey and cover the principles of second normal form (2NF).

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 summarized 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
• ZIP

A brief look at this table reveals a small amount of redundant data. We're storing the "Sea Cliff, NY 11579" and "Miami, FL 33157" entries twice each. 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 ZIP code for Sea Cliff 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 ZIPs) might have the following fields:

• ZIP
• City
• State

If we want to be super-efficient, we can even fill this table in advance -- the post office provides a directory of all valid ZIP codes and their city/state relationships. Surely, you've encountered a situation where this type of database was utilized. Someone taking an order might have asked you for your ZIP code first and 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 duplicative data from the Customers table, we've satisfied the first rule of 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 minimized the amount of redundant information stored within the database and our structure is in second normal form!

If you'd like to ensure your database is normalized, explore our other articles in this series:

• Database Normalization Basics
• Putting your Database in First Normal Form
• Putting your Database in Second Normal Form
• Putting your Database in Third Normal Form

If you want to receive notifications of new database articles posted on this site, Subscribe to our newsletter