All projects start with a design. Polished database design is necessary to have a successful business. Even if your queries are optimized and tuned to perfection you will not be able to achieve reasonable results with poor database design. Database design describes what objects must be created in the database, what data will they store and what will be the relationship between entities. It also defines integrity rules. The most common way to design a database is using an Entity Relationship Diagram (ERD).
A database is sometimes referred to as “normalized”. There are six normal (or normalization if you will) forms to describe the implemented level of data integrity and data redundancy.
| Normalization form | Normalization form shortcut | Description |
|---|---|---|
| First Normal Form | 1NF | The First Normal Form defines an object's domains as being indivisible and having only a one value of that domain. |
| Second Normal Form | 2NF | The objects is in the second normal form if it is in the 1NF and if a non-prime attribute is a an attribute of any other candidate key. |
| Third Normal Form | 3NF | The entity is in the third normal form if it is in the 2NF and all attributes are defined by a candidate key and not by a non-prime attribute. |
| Boyce-Codd Normal Form | BCNF | The entity is in Boyce-Codd normal form if there are no redundancy in dependencies of any objects. |
| Fourth Normal Form | 4NF | The object is in the Fourth Normal Form when there are no non-trivial multivalued dependencies but candidate keys. |
| Fifth Normal Form | 6NF | The entity is in the fifth normal form only if every non-trivial dependency is implied by the candidate keys. |
You can find an example of how tables might be normalized on the picture below
There are only first three normalization forms because these are the most commonly used. We also sometimes need to perform a “denormalization” of data in order to denormalize them. It is the reverse process of normalization. Why would we do that? The answer is very simple: to make querying easier for end users.
With normalized tables, users must incorporate lots of joins and queries grows bigger. In data marts, we want to make it as easy as possible for users to extract their desired data – thus we denormalize to avoid using joins.
Quiz: What does ERD stand for?
Answer: