Denormalization — when and why — Hinglish notes

4.4.22 · Coding › Databases

Denormalization HAI kya?

Denormalize KYUN karein? (Trade-off, derive karke)

Chalo pehle principles se sochte hain, rules yaad karne ki jagah.

Maano ek query ko $n$ tables mein spread data chahiye. Unhe combine karne ke liye database joins perform karta hai. Do tables $R$ aur $S$ ka nested-loop join bina indexes ke roughly $O (R \cdot S)$ rows examine karta hai, ya $S$ par index hone par $O (R lo g S)$ . $n$ tables ko chain karna is kaam ko multiply kar deta hai.

KAISE: common techniques

Worked examples

Worked example Example 1 —

order_total store karna Normalized: orders, order_items(order_id, price, qty). Total dikhane ke liye har baar ye run karo $total = \sum_{i} (p r i c e_{i} \times q t y_{i})$ Denormalized: orders.total_amount add karo, ek baar compute karke store karo.

Step	Ye step kyun?
Hot read identify karo	Order summary page hazaron baar/din load hota hai
Rare write identify karo	Items sirf checkout par change hoti hain (ek baar)
$f_{r} / f_{w}$ bahut bada	Inequality strongly satisfy hoti hai → denormalize karo
`total_amount` add karo	Har read par `SUM` join hata deta hai
`order_items` par trigger add karo	`total_amount` ko sahi rakhta hai → redundancy guard karta hai

Common mistakes

Active recall

Recall Quick self-test (chhupaao aur jawaab do)

Denormalization kya trade karta hai, aur kis direction mein?
Denormalize karne ki inequality batao.
Teen denormalization techniques bolo.
Duplicated data ko "guard" kyun karna zaroori hai?
Ek aisa case bolo jahan nahi karna chahiye denormalize.

Recall Feynman: 12 saal ke bachche ko samjhao

Socho tumhara toy box super tidy hai: cars ek box mein, wheels doosre mein, stickers teesre mein (ye normalized hai — kuch repeat nahi hota). Lekin har baar jab tum finished car se khelna chahte ho, tumhe teen boxes mein bhaagna padta hai aur assemble karna padta hai — slow! Toh tum kuch fully-built cars shelf par ready rakhte ho (ye denormalized hai — copies, jaldi grab ho jaati hain). Pakad: agar tum asli cars ko repaint karo, toh shelf copies bhi repaint karna yaad rakhna padega, nahi toh woh galat dikhenge. Tum ready-made copies sirf unhi toys ki rakhte ho jinse tum hamesha khelte ho, unki nahi jo tum mushkil se use karte ho.

Connections

Normalization (1NF 2NF 3NF BCNF) — jise hum partially undo kar rahe hain
Update Insert Delete Anomalies — woh khatre jo denormalization wapas laati hai
Indexing — reads speed karne ka alternative tarika, data duplicate kiye bina
Materialized Views — denormalization ki cached-query form
Database Triggers — duplicates sync rakhne ka common mechanism
OLTP vs OLAP — OLAP/warehouses (star schema) design se heavily denormalized hote hain
CAP Theorem — distributed systems aksar cross-node joins avoid karne ke liye denormalize karte hain

Denormalization kya hai?

Ek normalized schema mein deliberately controlled redundancy add karna taaki reads fast ho jaayein, write complexity aur storage ki keemat par.

Denormalize karne par kaun sa path sasta hota hai aur kaun sa mehenga?

Reads saste ho jaate hain (kam joins); writes mehengi ho jaati hain (duplicates sync mein rakhne padte hain).

Denormalize kab karna chahiye — rule batao.

Jab

f_{r} / f_{w} > C_{sy n c} / C_{j o in}

ho — read/write ratio sync-to-join cost ratio se zyada ho.

Char denormalization techniques bolo.

Pre-joined/merged tables, pre-computed derived columns, inline repeating/array columns, materialized views.

Never-normalized schema denormalization se alag kyun hai?

Denormalization ek already-normalized design ki deliberate optimization hai; un-normalized schema bas ek missing design step hai.

Denormalization kaun sa khatraa wapas laati hai aur isse kaise guard karein?

Update anomalies / data drift; triggers, transactions, app-layer invariants, ya scheduled refresh se guard karo.

Ek aisa case batao jahan denormalize nahi karna chahiye.

Write-heavy, correctness-critical systems (jaise financial ledger) jahan

f_{w}

high ho aur sync exact hona zaroori ho.

Normalization reads kyun slow karta hai?

Data bahut saari tables mein split hota hai, isliye reads ko unhe JOIN karke wapas jodhna padta hai, aur join cost table sizes aur tables ki sankhya ke saath badhti hai.

Reads speed karne ke liye denormalization ka sasta alternative kya hai?

Proper indexing — ye reads speed karta hai bina data duplicate kiye ya drift ka risk liye.