Second Normal Form (2NF) further addresses the concept of removing duplicate data as follows:

  • 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 as follows:

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.

For example, imagine a company 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
  • Phone
  • E-mail

Let’s assume that we are storing the “Chicago, IL 60604” and “Orland Park, IL 60462” entries twice each. This may 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 Chicago 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 duplicate 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
  • Phone
  • E-mail

We’ve now minimized the amount of redundant information stored within the database and our structure is in Second Normal Form!