Normalization in plain English

Well, if I had to explain it to my wife it would have been something like that:

The main idea is to avoid duplication of large data.

Let's take a look at a list of people and the country they came from. Instead of holding the name of the country which can be as long as "Bosnia & Herzegovina" for every person, we simply hold a number that references a table of countries. So instead of holding 100 "Bosnia & Herzegovina"s, we hold 100 #45. Now in the future, as often happens with Balkan countries, they split to two countries: Bosnia and Herzegovina, I will have to change it only in one place. well, sort of.

Now, to explain 2NF, I would have changed the example, and let's assume that we hold the list of countries every person visited.Instead of holding a table like:

Person   CountryVisited   AnotherInformation   D.O.B.Faruz    USA              Blah Blah            1/1/2000Faruz    Canada           Blah Blah            1/1/2000

I would have created three tables, one table with the list of countries, one table with the list of persons and another table to connect them both. That gives me the most freedom I can get changing person's information or country information. This enables me to "remove duplicate rows" as normalization expects.

One-to-many relationships should be represented as two separate tables connected by a foreign key. If you try to shove a logical one-to-many relationship into a single table, then you are violating normalization which leads to dangerous problems.

Say you have a database of your friends and their cats. Since a person may have more than one cat, we have a one-to-many relationship between persons and cats. This calls for two tables:

FriendsId | Name | Address-------------------------1  | John | The Road 12  | Bob  | The BelltowerCatsId | Name   | OwnerId ---------------------1  | Kitty  | 12  | Edgar  | 23  | Howard | 2

(Cats.OwnerId is a foreign key to Friends.Id)

The above design is fully normalized and conforms to all known normalization levels.

But say I had tried to represent the above information in a single table like this:

Friends and catsId | Name | Address       | CatName-----------------------------------1  | John | The Road 1    | Kitty     2  | Bob  | The Belltower | Edgar  3  | Bob  | The Belltower | Howard 

(This is the kind of design I might have made if I was used to Excel-sheets but not relational databases.)A single-table approach forces me to repeat some information if I want the data to be consistent. The problem with this design is that some facts, like the information that Bob's address is "The belltower" is repeated twice, which is redundant, and makes it difficult to query and change data and (the worst) possible to introduce logical inconsistencies.

Eg. if Bob moves I have to make sure I change the address in both rows. If Bob gets another cat, I have to be sure to repeat the name and address exactly as typed in the other two rows. E.g. if I make a typo in Bob's address in one of the rows, suddenly the database has inconsistent information about where Bob lives. The un-normalized database cannot prevent the introduction of inconsistent and self-contradictory data, and hence the database is not reliable. This is clearly not acceptable.

Normalization cannot prevent you from entering wrong data. What normalization prevents is the possibility of inconsistent data.

It is important to note that normalization depends on business decisions. If you have a customer database, and you decide to only record a single address per customer, then the table design (#CustomerID, CustomerName, CustomerAddress) is fine. If however you decide that you allow each customer to register more than one address, then the same table design is not normalized, because you now have a one-to-many relationship between customer and address. Therefore you cannot just look at a database to determine if it is normalized, you have to understand the business model behind the database.

This is what I ask interviewees:

Why don't we use a single table for an application instead of using multiple tables ?

The answer is ofcourse normalization. As already said, its to avoid redundancy and there by update anomalies.