Beforehead
In the past, almost all of my projects are constructed using Non-relational databases, like using Firebase. This helped me develop products that do not require very complicated database design fastly, but also undermined my chance go get a deeper understanding of how to construct relational tables. And therefore I checked some materials regarding this part, but hey let’s be very frank: a lot of materials out there use super complicated language to describe a simple idea with no examples, which causes a lot of confusion.
I am not trying to say that using formal language is not good, but sometimes it would really be helpful if we could have some great examples, right?
So here we go. In this article I am going to discuss the 3 normal forms of relational databases by reducing a super bad table to several nicely designed ones.
1NF
For 1NF, the requirements are quite simple:
- No repeated rows
- Every attributes are atomic and non-divisible
Take a look at the table below:
Hmmmm, seems like a well-desinged table yeah? Not exactly! Let’s check:
- There are no reapted rows, okay…
- Oops, not every attributes are atomic!
Take a look at the name section. We can further divide it into first name and last name. That is an issue. Let’s revise it then:
Now it is better. But this table still looks too complicated. We will further optimize it in 2NF and 3NF.
2NF
2NF needs to fulfill 1NF first. Except for those, 2NF also need to ensure that:
- There are no partial dependencies.
What the heck is partial dependencies?? At first, I am very lost. Actually it is simple, very simple. Just remember:
- Do not add redundancies to your primary key.
What does it mean? Look at our previous table. We can tell that One’s Department, Job Title and Responsibility solely depend on one’s ID, but not their names. However, we put names in our primary key as well, which is redundant! So how should we improve this? We split them into different tables and that’s it:
Seems like I forget the Date of Birth oclumn, but you get the idea…
Can we do even better? Yes!
3NF
Like 2NF, 3NF must fulfill 2NF first. But what is it about 3NF?
- There are no transitive dependencies.
Take a look at the table above. The responsibility actually depends on one’s department and job title, while one’s department and job title depends on ID! That is called a transitive dependency. So how can we improve this? We make extra tables, yes. All we need to do is to split the table into one with ID, Dept and Job Title, and the other with Dept, Job Title and Responsibility.
After 3NF
Actually there are also BCNF, 4NF and 5NF, but usually these are a bit overkill and I am not going to cover them in this article (the table might be super complicated, too). So, I’ll leave it here for now.