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?

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.

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
possible.

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."

 

I like to get feedback but do not have time to combat all the spammers out there. The best means to contact me is by sending me a message through LinkedIn (I think you need to be a member to do so). Once you signup, just request to add me to your network and I will accept. I will do my best to respond to your message.

View Eric Matthews's profile on LinkedIn



Back to sqlinfo.net homepage.

Homepage
By Eric Matthews on sqlinfo.net