What is Normalization Anyway?

Think of an address. John Doe, 123 Main Street NW this_town,Iowa, USA 52302. If I type this address in over one thousand times, odds are pretty high that I will mis-type something at least once (and If you follow this blog, you will know that grammatical errors and I are old friends.). Normalizing information is breaking down critical pieces of information into logical groups. For instance, if we create a look-up table to select Street vs. Avenue vs. Circle, I can simply select the right entry. This is often called street type. Because so much critical business information is associated to an address, it is critical to a business that it is normalized. An address table might take the above address and store it this way.

-First Name (John)
-Last Name (Doe)
-Street Number (123)
-Street Name (Main)
-Street Type (Street)
-Street Direction (NW)
-City (This-Town)
-State (This State)
-Country (USA)
-Zipcode (52302)

The fields of Street Type, Street Direction, State, and Country should be look-up fields (ie. the values are stored in a table so the individual doesn't TYPE the information but selects the values from a table). One of the ways to determine if a particular piece of information should be segregated is to ask yourself (Mr./Ms. Business Person) would I want to query or report on information based on this field. Would I want to see a report of all clients by state, or by zipcode and yes even by street type. (Oh, you don't think so? think AGAIN!). What would happen if someone approached you with a business proposition. We want to do a joint venture with you. We want to pitch (insurance, lawn mowers, Security cameras..blah blah blah) to everyone that lives ON A CUL DE SAC! (oh come on now, IT COULD HAPPEN!!, work with me here). Can you generate your report?
Normalization of course, can go too far. There is the legend of an eager young lad, who normalized their core business records into over 100 separate tables. Each table storing a particular value (a table for address, a table for names, a table for products,etc). This was all very good except for the fact, that this business was a high margin, low-volume business. In the 60 year history of the company, they had a total of 40 clients. ummm NO.

Editor's Note:  This post was first published on 8/28/2008.

About Sprawlgeek.
A Seasoned Technology Visionary possessing a deep understanding of technology and business processes.

Doug has accumulated over 35 years of experience in the technology and media markets. His broad career has ranged from leading Research and Development work for a 300+ million dollar company to an E-Government startup. His efforts have been recognized by major trade associations as well as Tier 1 clients.

Doug is now a private consultant and continues to provide his insights to the industry.