First Normal Form (1NF) - Administration

First Normal Form (1NF)

First Normal Form states that each attribute in a relation has to be atomic and scalar. It also states that a table must have a primary key to identify any particular row. You cannot store a list of values in a single column and you cannot store a list of columns that emulate a fixed size list - also known as a repeating group. For example, suppose you were to create a table to hold contact information for business contacts:

CREATE TABLE Contacts (

      first_name*,
      last_name,
      company,
      phone1,
      phone2,
      phone3,
      fax,|
      email
);

* Data types ellided

This table contains a repeating group - phone1-3. More often than not, several of those columns will be empty, which is a waste. It is also nearly impossible to enforce any uniqueness for this group because a constraint cannot be declared such that any phone1 is unique across phone1, phone2 and phone3.

Consider another set of problems - what if you want to add a company, not a person, to your Contacts table? Or just a phone number and a first name? You would have to leave one or more of the candidate key columns (first_name, last_name, company), which would most certainly violate the only possible primary key constraint. What if you want to delete a fax number from your table? If you simply DELETE WHERE fax=X, you may be deleting people as well.