When getting your data into 1NF, there are a few rules you need to follow:

Before Codd’s relational database model was published, the de facto standard for mainframe databases was a hierarchical database model where information was stored in a tree structure with parent-child relationships. The second rule is an important departure from hierarchical databases where the order of records and their level of indenture indicated parent-child relationships. This order independence not only allows records to be reordered, but accessed via relationships from other tables.

This last rule is often seen as the defining rule of first normal form. Figure 3-1, Figure 3-2, and Figure 3-3 show examples of tables that violate the rules of 1NF, specifically in the way that they store the PhoneNumber. Figure 3-1 adds additional columns to store PhoneNumber2 and PhoneNumber3. This is a common mistake made by end users who design systems or novice developers.

Figure 3-1. Adding additional columns to the database

Figure 3-2 stores two values in the same column. SharePoint lists allow you to do this, but it violates the rules of first normal form and is not recommended when developing a business application.

Figure 3-2. Duplicate entry for different phone numbers

Figure 3-3 is creative in that is has a single phone number column, but uses a column to delimit the multiple phone numbers for Andy Daverson.

Figure 3-3. Single entry with different phone numbers delimited

Imagine the challenges associated with storing your data in these ways. If I wanted to return all users with a phone number starting with ‘123,’ I’d need to write the following query against Figure 3-1:

Select * from MyTable where
(PhoneNumber like '123%') or
(PhoneNumber2 like '123%)' or
(PhoneNumber3 like '123%')

Figure 3-2 and Figure 3-3 are perhaps worse because they lose the semantic significance of the PhoneNumber heading. That column could now contain a single telephone number, many numbers, or nothing at all.

We’ve talked about what not to do and identified examples that violate 1NF. Now let’s talk about an example that implements a structure that follows the rules of 1NF correctly. Figure 3-4 deconstructs the single table into two tables: one table describes the person, and the second table associates one or many PhoneNumbers with each person. You can see that by adding rows to the second table, one can add as many phone numbers as necessary. It’s easy to find a person who has a specific phone number.

Figure 3-4. Deconstructs the single table into two tables

Now when we want to find everyone whose phone number starts with ‘123,’ we don’t need to know what column it’s stored in. The query to find everyone whose phone number starts with ‘123’ could be easily described as follows:

Select P.* from Person P inner join PhonerNumber PH on
 P.PersonID=PH.PersonID where PhoneNumber like '123%'

In short, by requiring that each row be uniquely identified, enforcing order independence, and removing repeating groups of data, Codd’s first normal form gives us the foundation to build a normalized database. Next, we’ll build on these concepts with the second normal form.

The second normal form (2NF) focuses on removing repeating data from multiple rows by making sure that each attribute relies on the entire key. Having the same data across multiple rows can lead to errors when updating data in your table. Let’s look at Figure 3-5.

Figure 3-5. Database view

The first thing we need to do is identify the unique key for each row. In this case, we can’t use FullName or Skill because neither is unique. This example is tricky because uniqueness is defined by a composite key, which is composed of both FullName and Skill.

The Address column is only dependent on the FullName column, so this table does not conform to the rules of 2NF. Just like in Figure 3-4, we will normalize the data by breaking it into two tables as shown in Figure 3-6.

Figure 3-6. Deconstructs the single table into two tables

The first table has a key of PersonID, and both FullName and Address directly relate. The second table defines uniqueness with a composite key of PersonID and Skill. There are no columns in either table that do not relate to the full key, making this approach compliant with the rules of 2NF. When we update an address, it exists in only one place, preventing update errors.

The third normal form (3NF) adds only one more rule for us to understand. 3NF simply states that every nonkey attribute or column in the table must provide a fact about the key.

Codd’s third normal form has been described memorably in a play on words similar to the swearing in of witnesses in a court of law: Every nonkey attribute must provide a fact about the key, the whole key, and nothing but the key, so help me Codd.

When we examine the data in Figure 3-7, this rule is easy to understand. ArticleID is the key for this table. ArticleTitle, Year, and Author directly relate to the article, but the AuthorAge does not.

Figure 3-7. ArticleID table

As you can see in Figure 3-8, we now have two tables in which every attribute or column relates directly to the key. We removed duplicate data, which ensures that we won’t have errors when updating the data.

Figure 3-8. Deconstructs the single table into two tables

The last few sections covered some pretty complicated topics. Before we move on and apply these principles in our LightSwitch application, I want to cover one more topic that is a bit more advanced.