First Normal Form (1NF) has two basic rules for a normalized and organized database. The first is to eliminate duplicative columns from the same table. The second is to create separate tables for each group of related data and identify each row with a unique column (the primary key). What do these rules mean when contemplating the practical design of a database?

Eliminate Duplication

The first rule dictates that we must not duplicate data within the same row of a table. Within the database community, this concept is referred to as the atomicity of a table. Tables that comply with this rule are said to be atomic.

Let’s explore this principle with a classic example: a table within a human resources database that stores the manager-subordinate relationship. For the purposes of our example, we’ll impose the business rule that each manager may have one or more subordinates while each subordinate may have only one manager.

Intuitively, when creating a list or spreadsheet to track this information, we might create a table with the following fields:

  • ManagerSubordinate1Subordinate2Subordinate3Subordinate4

However, recall the first rule imposed by 1NF: Eliminate duplicative columns from the same table. Clearly, the Subordinate1 through Subordinate4 columns are duplicative. Take a moment and ponder the problems raised by this scenario.

If a manager only has one subordinate, the Subordinate2 through Subordinate4 columns are wasted storage space (a precious database commodity). Furthermore, imagine the case where a manager has four subordinates. What happens if they take on another employee? The table structure would require modification.

At this point, a second bright idea usually occurs to database novices: We don’t want to have more than one column and we want to allow for a flexible amount of data storage; let’s try something like this:

  • ManagerSubordinates

And the Subordinates field would contain multiple entries in the form of “Mary, Bill, Joe.”

This solution is closer, but it also falls short of the mark. The subordinates column is still duplicative and non-atomic. What happens when we need to add or remove a subordinate? We need to read and write the entire contents of the table. That’s not a big deal in this situation, but what if one manager had one hundred employees? Also, it complicates the process of selecting data from the database in future queries.

Here’s a table that satisfies the first rule of 1NF:

  • ManagerSubordinate

In this case, each subordinate has a single entry, but managers may have multiple entries.

Identify the Primary Key

Now, what about the second rule: Identify each row with a unique column or set of columns (the primary key). You might take a look at the table above and suggest the use of the Subordinate column as a primary key. In fact, the Subordinate column is a good candidate for a primary key due to the fact that our business rules specified that each subordinate may have only one manager.

However, the data that we chose to store in our table makes this a less than ideal solution. What happens if we hire another employee named Jim? How do we store his manager-subordinate relationship in the database?

It’s best to use a unique identifier such as an Employee ID as a primary key. Our final table would look like this:

  • Manager IDSubordinate ID

Now, our table is in first normal form, Beyond this, there are options for putting your database in Second Normal Form, as well as in Third Normal Form if you’re excited about more organization.

Get the Latest Tech News Delivered Every Day