CodeParkorder_items(order_id, product_id, product_name, qty,
customer_id, customer_city)
-- primary key: (order_id, product_id)
Each normal form adds one rule on top of the one before it.
1NF (first normal form): every column holds one atomic value. Atomic means a single value that you don't split further. There are also no repeating groups. So no comma-separated list of tags in one column, and no phone1, phone2 columns.
2NF (second normal form): the table is in 1NF. Also, every non-key column depends on the whole primary key, not just part of it. This matters when the key has two columns. In this table, product_name depends only on product_id. And customer_id depends only on order_id. So they belong in products and orders.
3NF (third normal form): the table is in 2NF. Also, no non-key column depends on another non-key column. Here, customer_city depends on customer_id, not on the key. So it belongs in customers.
The normalised result is four tables:
order_items(order_id, product_id, qty)orders(order_id, customer_id)products(product_id, name)customers(customer_id, city)
Now each fact is stored once, so two copies can never disagree.
- 1NF means every column holds one atomic value and there are no repeating groups.
- 2NF means no column depends on only part of a composite key.
- 3NF means no column depends on the key through another non-key column (a transitive dependency).
- The goal is to store each fact once, so updates can't leave the data inconsistent (update anomalies).