Recall the general requirements of 2NF:
Remove subsets of data that apply to multiple rows of a table and place them in separate rows.
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. Their Customers table might look something like this:
CustNum | FirstName | LastName | Address | City | State | ZIP |
1 | John | Doe | 12 Main Street | Sea Cliff | NY | 11579 |
2 | Alan | Johnson | 82 Evergreen Tr | Sea Cliff | NY | 11579 |
3 | Beth | Thompson | 1912 NE 1st St | Miami | FL | 33157 |
4 | Jacob | Smith | 142 Irish Way | South Bend | IN | 46637 |
5 | Sue | Ryan | 412 NE 1st St | Miami | FL | 33157 |
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 look like this:
ZIP | City | State |
11579 | Sea Cliff | NY |
33157 | Miami | FL |
46637 | South Bend | IN |
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 |
1 | John | Doe | 12 Main Street | 11579 |
2 | Alan | Johnson | 82 Evergreen Tr | 11579 |
3 | Beth | Thompson | 1912 NE 1st St | 33157 |
4 | Jacob | Smith | 142 Irish Way | 46637 |
5 | Sue | Ryan | 412 NE 1st St | 33157 |
We've now minimized the amount of redundant information stored within the database and our structure is in second normal form!