How Much Normalization is Enough?I'm pretty sure you've already had enough of this topic as well. I have had a number of contacts from accidental DBAs and new developers in appreciation of the great lessons shared from real world problem solving.
J writes in with an ERD (Entity Relationship Diagram) emphasizing how detailed you can get with your normalization. J submits the following design with a nice big smile.
I decided to share this last example for the newbies to demonstrate the extremes to which you can take normalization. What I find really interesting about this schema is that it emphasizes the fact that one size does not fit all. I have actually worked on two databases in the last 25 years where this idea was the correct design (out of hundreds of different databases).
This schema represents the North America Numbering Plan. It highly reflects the actual switching hardware used by telephone companies to make connections from one phone device to another over a switched network.
Each of the three segments of a number have meaning and restrictions. For example, the number 0 is never allowed for the first digit of the NPA (Number Plan Area Code) or NXX (Central Office Exchange Code). This is to simplify the complexity of the switch allowing a user to dial 0 to contact a switchboard operator. See the link above for more rules and a history of how they were created.
The database I worked with was for telephone billing. Therefore, the components of the phone number were significant. I had the option to store them all in a single field (column) and use substrings to break the component parts out. Or, I could easily put them in their own column as represented by the Phone Numbers table.
I did have NPA and NXX tables because they are real entities. An NPA represents a physical region as a collection of Exchanges. An NXX represents a central office hosting the switches connected to customer telecom devices. It was important that I be able to validate accuracy at the NPA and NXX level; so separate tables were essential.
Why don't I recommend this schema for every database? Because, normally I don't care. Normally the breakout of a phone number is not relevant within the data I am storing. I can use a phone to call someone without knowing that an Area Code determines long distance or a toll call; that the NXX determines an exchange routing the call, and that the number is specifically assigned to subscriber. I simply dial 10 (or 11 digits if I also specify the country code) and my call magically connects with my intended party.
Additionally, this schema does not lend itself for use outside NAPN countries. A string or number field is adequate, and is flexible to the switching devices worldwide. A string value allow for the storing of the format associated with the number. A Numeric value requires an additional field for the format to be used when presenting the number. Either way works.
This article is extracted from below link
http://www.sswug.org/editorials/default.aspx?id=2208
