Normalization is a process in database design that ensures the efficiency, integrity and scalability of a database schema. It is a set of rules that govern how a database is organized in order to prevent anomalies, redundancy and inconsistencies, which can lead to data loss, errors and inconsistencies. Normalization is typically divided into a series of normal forms, each of which builds upon the previous one, adding additional constraints on the database design.
The main aim of normalization is to minimize data redundancy (information duplication) in tables, avoid update and deletion anomalies, and promote data consistency. Depending on the level of normalization, we can minimize redundancy to such extent that no data is duplicated in the same table or across different tables.
Here are the different normal forms in database design:
1. First normal form (1NF): This is the most basic level of normalization that a table must adhere to. It states that each column in a table must have atomic values, i.e., each field in a table should have only one value. No repeating groups or arrays are allowed, each field should have its own unique value.
For example, a table which stores customer data can have separate columns for customer name, customer id, and address. The customer name column cannot have a comma separated list of names, one per row.
2. Second normal form (2NF): A table is in 2NF if it is already in 1NF and every non-key attribute in the table is fully functionally dependent on the primary key.
A functional dependency is a relationship between two attributes in a table, such that the value of one attribute determines the value of the other. We say that the second attribute is “functionally dependent” on the first attribute. A table that has a composite primary key (a primary key formed using two or more columns) is considered to be in 2NF if each non-key field is dependent on the entire primary key.
For example, a table which stores vendor orders can have a composite primary key, (order_id, item_id) where the order id is the unique identifier and item id identifies the product being ordered. The order date and quantity depend entirely on the (orde_id, item_id) combination and can’t appear in any other table.
3. Third normal form (3NF): A table is in 3NF if it is already in 2NF and no non-key attribute in the table is transitively dependent on the primary key.
Transitive dependency is a situation where a field value in a table depends on other fields that are not part of the primary key. For example, consider that a customer order table as described earlier had product unit price stored in each row. Product unit price depends on the product id, but not directly on the order id. This is transitive dependency, and it is resolved by splitting the table into two tables:
Order table: order_id, customer_id, order_date
Order details table: order_id, item_id, unit_price, quantity
Here, the unit price is no longer in the same table as order_id, and the Order details table maintains a one-to-many relationship with the order table.
Normalization can go beyond the third normal form, to include the fourth, fifth and even higher normal forms, depending on the complexity of the database. The higher the normal form, the more normalized and the smaller the resultant tables, with reduced duplication of data.