First Normal Form (1NF)

Two definitions.

“A relation is said to be in first normal form (abbreviated 1NF) if and only if it satisfies the condition that it contains scalar values only.”1

“A relation is in first normal form if the domain of each attribute contains only atomic values”2

To break this down into more digestible terms, first normal form (1NF) refers to the granularity of data within a field/column. For example, consider the following.

Name

Joseph T. Dokes

Mary Wilson

Lou J. Malnotto


Our name column is really not atomic as it consists of the persons fistname, lastname, and middle initial. It would be normalized in 1NF as follows.

First

MI

Last

Joseph

T

Dokes

Mary

 

Wilson

Lou

J

Malnotto


Next, consider…

ID

Lastname

Phone

1

Smith

555-1212

2

Jones

352-555-2323

3

Smith

233-555-9877


Our phone number is really not as atomic as it could be. The following would place this entity in 1NF.

ID

Lastname

Phone

areacode

exchange

extension

1

Smith

555-1212

 

555

1212

2

Jones

352-555-2323

352

555

2323

3

Smith

233-555-9877

233

555

9877


The most important point I have to make about table and column normalization is as follows:

Normalization is not a strict algorithm that must always be rigidly applied. Normalizing data also involves understanding the purpose and the nuances of the data.

For example, let us look at the phone number example. It is really necessary to break the number into three separate columns? I would argue it is not for the following reasons. One, we can handle in code and differentiate between a seven and a ten digit phone number. Ahhhh, but I could also argue we are storing phone numbers in a statewide database and we want to query the table based upon the area code. If we have the phone number granular to three columns we could place an index on the area code column. We could not do this if we have the number in a single column.

But, it gets even more nuanced that this. What phone number is this representing. Home? Work? Cell? Fax? Should we not break phone number out into its own table? Decisions, decisions.

Now consider the name example. Should we break this out into discrete fields? The answer is typically yes. But why? It is the same issue is it not? Well, suppose we have an application where we need to lookup a user in our system. It will be much more efficient to do this is we break the name out into its atomic parts (lastname, firstname, middle initial).

Finally, consider the following example:

patient

Allergies

1

pollen, cats, dust

2

dander, wheat

3

None


The allergies column is not in 1NF. The following table place this column in 1NF.

patient

Allergies

1

pollen

1

Cats

1

Dust

2

dander

2

wheat

3

none


Now you might be questioning why we put this data into first normal form by dealing with the row axis instead of the column axis. Well, in the name and phone example out columns were finite. In this example the question is how many allergies can a person have. We could take a stab and say twenty but the minute we made twenty discrete allergy columns, but what would we do when we got a patient that had more than twenty? Not to mention it is just bad form and practice to use a relational database to create what amounts to a fixed array of columns.

As you can see there are many factors and things we need to consider when designing a database and considering whether a column should be in first normal form (1NF).

1 C.J. Date. An Introduction To Database Systems 6th Edition. Addison-Wesley. p 289

2 Elmasri, Ramez and Navathe, Shamkant B. (July 2003). Fundamentals of Database Systems, Fourth Edition. Pearson. p. 315.

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