Data Modeling, Breaking & Fixing First Normal Form (1NF) - Page 2
April 9, 2004
Mistake 2, internal array element
This second form of breaking 1NF takes the form of creating a column that contains many values of an attribute. Taking from our CITY table example, this mistake makes our CITY table look like the one in Listing 5. This is a much worse form of breaking 1NF than our first mistake. It has the added difficulty of no boundaries for columns in the city except for the comma separator. It is prone to errors in data entry around the comma-separated fields. Sometimes we will encounter spaces before or after the comma that can play havoc on any parsing that we may want to do.
Since there are no columns and we are not quite sure of the number of cities that any one column may or may not have, our only solution to this problem is to create some logic (application) to extract the information from this column. In Listing 6, there is code to make this mistake behave just like a normal table. In order to accomplish this task we need to do the following.
The breaking of 1NF can and does cause havoc in all of our lives. Until you have the time and resources to fix the problem within the physical model, you must provide solutions that are easy for everyone to use. My suggestion to you is to come up with a solution that will work and allow you to fix the real problem with the least amount of impact on your user community. If you take an approach to put a view in place of the real table, let your user community select from that view, you will be able to hide the logic behind the scenes and hopefully one day fix the real problem and allow yourself to get rid of the view and underlying logic.