Database Normalization
Why Databases Are Normalized
Database normalization is the process of organizing tables and columns so that each fact is stored once, in the place where it belongs. Its main goal is to reduce duplicate data and the inconsistencies that duplicates cause.
Suppose an orders table stores the customer's name and address on every order row. If the customer moves, every one of those rows must be updated. A missed row leaves two different addresses for the same customer, and nobody can tell which one is correct. Normalization avoids this by moving customer details into their own customers table and keeping only a customer ID in the orders table.
The way tables, rows and columns are arranged is part of the broader database schema. Normalization is the set of rules that guides how facts are split between those tables.
The First Three Normal Forms in Plain Language
Normal forms are a series of rules. Each level builds on the one before, and most practical designs aim for the third.
- First normal form (1NF) requires each column to hold a single value and each row to be distinct. A cell that lists several phone numbers separated by commas breaks this rule, and so does a set of columns named phone1, phone2 and phone3.
- Second normal form (2NF) applies when a table's key is made of more than one column. Every other column must depend on the whole key. In an order items table keyed by order and product, a product name depends only on the product, so it belongs in a products table.
- Third normal form (3NF) requires every non-key column to depend only on the key, not on another non-key column. An orders table that stores the customer's city depends on the customer rather than the order, so that city belongs in the customers table.
Higher normal forms exist, but 1NF to 3NF covers most everyday design work.
A Shop Example Before and After Normalization
Before normalization, a single orders table holds the order ID, customer name, customer email, product name and product price on every row. A customer who buys three items appears three times with the same name and email.
After normalization, customers, products and orders each have their own table, and the order lines link them by ID. Changing a customer's email now means updating one row. Order lines refer to a product by its ID instead of repeating its name, so the product details are stored in one place.
Frequently asked questions
When should a database be denormalized?
Denormalization means adding deliberate duplicates to speed up reads. It can make sense for reporting tables, dashboards or caches, where reads far outnumber writes and slightly stale copies are acceptable. The application must then keep the copies in sync, so the trade-off is extra complexity in exchange for faster queries.
Is a normalized database always better?
Not always. Normalized designs suit systems where data changes often and accuracy matters, such as orders, accounts and payments. Read-heavy workloads may need some duplicated data for speed. A well-designed database usually starts normalized and adds controlled duplication only where measurements show a real need.