First Normal Form, why is it good and how does it reduce redundancy

Question

I asked a similar question on SO and was advised to ask the type of question here. This is for a course on relational databases. A sample problem asks "how come every table in a relational database should be in First Normal Form" my first problem with this is isn't 3NF considered better so wouldn't the statement be false? My second problem with this is on SO people said that First Normal Form is good because it eliminates redundancy. I don't see how it does this because 1)all values are atomic 2)there is a primary key - neither of which eliminates redundancy.

I guess another way to ask it is why is it important for values to be atomic?

Answer 1

The primary importance of first normal form is not that it eliminates redundancy, but rather, it's that it eliminates repeating groups.

Instead of having multiple columns of the same kind of data in a record, (0NF) you remove the repeated information into a separate relation and represent them as rows. This is what constitutes 1NF.

Tables that have columns like: phone_1, phone_2, phone_3 or that contain list-oriented data like: 212-555-1212, 212-555-1234, 416-967-1111 violate 1NF.

1NF is important because it is much more flexible than 0NF while being much easier to use when inserting, updating and reading data. This is because every type of data element (e.g. customer phone number) has exactly one column in which to find it and that column has only one piece of data for each record. This means that you can use simple SQL statements to read or write individual data elements without having to parse delimited strings or use constructions like: where phone_1=@Number or phone_2=@Number or phone_3=@Number and so forth.

Regarding 1NF vs 3NF, the normal forms are cumulative. A table in 3NF is also in 1NF, so it is just as true to say that "Every table in a relational database should be in 1NF" as it is to say that "Every table in a relational database should be in 3NF." I would say both of these are true, but I would add "unless you have a really good, well considered reason to denormalize".

Answer 2

"I guess another way to ask it is why is it important for values to be atomic?"

I'm currently working with applications that have comma-separated values in varchar/text fields, and it's a pain in the ass. Among other things, in the application you have to do all kids of silly string splitting and converting to get the data on the correct form, and on the DB side you can't use indexes on the field, as all searches have to be on the form WHERE mycolumn LIKE '%search%'

There are other reasons as well, but those two come to mind first.

Answer 3

1NF does eliminate redundancy, and it also helps reduce or eliminate data consistency issues.

Assume you have a table of Invoices, which contains payment data as well as customer data.

The customer data is information like Name,Address,PhoneNumber, etc. If you duplicate this data on every row...

• The data is presumably redundant (since you have the same details in every Invoice record for the same Customer
• The data could potentially become inconsistent. If I have two different Address values for the same Customer, which one is correct?
• The data is less logically/intuitively organized. If you want to send a customer a promotion, it would make more sense to check an Address table than to check the Invoice table for a current address.