One wide table, three anomalies
Update: a repeated fact means many rows to change. Insertion: a new restaurant has no row until its first order. Deletion: cancelling an order can erase a dish.
Read dependencies off the data?
Bad: Names are unique, so name → id
Until two customers are both called Asha.
Good: Rules come from the business
Data can disprove a dependency, never prove one. Ask the product owner.
The whole key and nothing but the key
- 1NF: one value per cell
- 2NF: no fact about part of the key
- 3NF: no fact about a non-key column
- BCNF: every determinant is a key
Split along each rule until every fact has one home
The flat order-lines table becomes five, customers, restaurants, dishes, orders and order lines. The rename is now one row.
Each kitchen cooks one cuisine; each area sends each cuisine to one kitchen.
The catch
3NF holds, BCNF fails: kitchen_id determines cuisine but isn’t a key. And the BCNF split loses a rule.
Two independent lists in one table multiply
Biryani Junction has 3 tags and delivers to 40 PIN codes. One table needs 3 × 40 = 120 rows; two tables need 43.
Where to stop
Bad: Copy wherever reads feel slow
Each copy brings extra writes and repair code.
Good: Normalize, then copy on purpose
Each copy names the read it speeds up and its sync path.
Remember
Every column describes the key, the whole key and nothing but the key. Copy facts only on purpose.