Differences between 1st, 2nd and 3rd normal form

That’s a common knowledge, but probably most of you don’t know about the specific rules of each normal form.

How we normalize the databases following the Kimball or Inmon approach when they specifically ask for 3rd normal form and we do it right? Well, because all we need is a common sense based on our experience. For those who want to keep the ideas clear and gain some free and interesting knowledge, here is my explanation:

1st NORMAL FORM

is composed for 3 simple rules:
* Have a unique key: That means, the table should contain at least 1 unique key (primary key)

* Each field should contain ONE value: That means you cannot keep more than 1 value in the same field, like for example your full name should be split in Name, Middlename and Surname.

* No repeating groups: Let’s say that you have got a table with Companies and their contact, but after, you notice that only one contact is not enough, then you cannot keep all of them in the same field like “James Smith, Bill Imon, Mike Gill” or create repeating groups (columns) like “Contact”, “Contact2”, “Contact3”.

The solution is very simple, you just need to create another table and link these contacts to their company:

2nd NORMAL FORM

Only one rule:

* Any non-key field should be dependent on the entire primary key: That means if you have one or a compound primary key (2 primary keys are doing as a unique) and some other field is dependent on this primary key, then you can get a problem if you update one of them, for an instance:

 

Here ID is the course id and CourseTitle is the name of it, what happens if we modify ID and we forget to change the CourseTitle? that it will create a garbage and some weird data. For that, we have to create a table to give a title to our ID courses and then you can change the Event ID as you like. Something like this:

3rd NORMAL FORM

* No non-key fields are dependent on another non-key field: Sounds strange, but it has a really easy explanation.  What we need to do, in order to reduce redundancy or unnecesary repeated data, we need to bring out of the table all the information that is fixed by another field. Ok, a great example is the UnitePrice and ProductID, is usual to get a table with the ProductID, Quanity and Total, but for example you don’t need to show the Name of the Product each row, the unique price each row and repeat again and again the same data. You just need to bring all this info in a unique row in another table where ProductID 1002 will be a Pillow, Unit price will be a $14.00 and then you can just calculate the total multiplying the UnitPrice of this ProductID by Quantity.


Did you like this? Share it:

Leave a Reply

*