What is database normalization, and can you explain the first three normal forms with examples?

Question

Accepted Answer

Database normalization is the process of organizing a database schema to reduce data redundancy and improve data integrity. Each normal form builds on the previous one, adding stricter rules about how data should be structured. Understanding at least the first three normal forms is essential for any DBA involved in schema design or review.

First Normal Form requires that each column in a table contains only atomic, indivisible values, and that each row is unique. A table violates 1NF when a column contains multiple values. For example, a contacts table with a phone_numbers column storing "555-1234, 555-5678" violates 1NF because the column contains a list rather than a single value. To fix this, create a separate phone_numbers table with one row per phone number and a foreign key back to the contacts table. Each row in the table must be uniquely identifiable, typically through a primary key.

Second Normal Form requires that the table is already in 1NF and that every non-key column depends on the entire primary key, not just part of it. This rule is relevant when a table has a composite primary key made up of multiple columns. For example, consider an order_items table with a composite key of order_id and product_id. If the table also includes product_name, that column depends only on product_id and not on the combination of order_id and product_id. This partial dependency violates 2NF. The fix is to move product_name to a separate products table where it depends solely on product_id.

Third Normal Form requires that the table is in 2NF and that no non-key column depends on another non-key column. This is called eliminating transitive dependencies. For example, a customer table might have columns for customer_id, name, city, and state. If the state can be determined from the city, then state transitively depends on customer_id through city. To satisfy 3NF, you would create a cities table that maps cities to states and store only the city reference in the customer table. In practice, this particular example is often left denormalized for convenience, but the principle applies strongly to cases where the transitive dependency causes real data inconsistency.

The benefits of normalization include reduced data redundancy, meaning each piece of information is stored in exactly one place. This eliminates update anomalies where changing a value in one place but not another creates inconsistency. It prevents insertion anomalies where you cannot add certain data without adding unrelated data. It prevents deletion anomalies where deleting one record unintentionally removes other information.

However, normalization has trade-offs. Highly normalized schemas require more JOIN operations to retrieve related data, which can impact query performance. In practice, most production databases aim for 3NF as a reasonable balance between data integrity and query complexity. Strategic denormalization, intentionally violating normal forms for specific performance reasons, is acceptable when done consciously and documented clearly.

As a junior DBA, when reviewing or designing schemas, check whether tables are at least in 3NF. If you find redundant data across tables, that is usually a sign that further normalization would improve data integrity.