First Normal Form Conundrum

First normal form requires that data be atomic. We can demonstrate this with the following example.

Name : Joseph T. Dokes

The entity "Name" is not in 1NF. It is not atomic. We can break the entity down further.

Firstname : Joseph
Middle Initial: T.
Lastname: Dokes

We now have atomic data and are satifying 1NF.

If we look at this entity from a business perspective or from an application development perspective it makes sense to break the name entity down into its constituent parts. Why?

  • To ensure better data collection through the front end.

Imagine all the code we would have to right to deal with a single data field in the user interface to collect a persons name.

How would we determine that they entered their full name?
How would we know that they entered their firstname first?

You get the idea.

  • To better organize and group the data

If we want to lookup a person in an application it is much more efficient to do so if we make this data as atomic as

There are many factors that incent us to not violate 1NF.

So, should all data at a minimum be in 1NF?

Consider the following...

Phone number: 555-255-1515

Is this in 1NF?

A phone number consists of an area code, and exchange, and an extension (there are more potential pieces to a phone
number but these will suffice). Clearly we are not in 1NF. So, what would the advantage of breaking a phone number down offer us?

Actually, unless we were developing telecom phone switching software or creating an electronic phone book, or where we have the need to break down our data area code it really would not serve us a great deal of good. In fact, doing so would require us to either...

A) Add three fields instead of one to our user interface for each and every phone number we wanted to collect.

B) Have to write a program that parsed the phone number so we could atomically store it in our database.

Frankly, neither of these are desirable outcomes. If my sole objective is to not violate 1NF then I have just offered a bone-fide example for when it is acceptable to do so. In fact, it is pretty much a common practice to enter phone number as a single entity for the reasons I have mentioned above.

The decision to conform to 1NF resolves predominantly to the business need for data atomicity. Also, due consideration must given to data access when determining atomicity. In other words it makes a lot more sense in our example above to build an index on "Lastname" than it does to build one on "Name."