Lesson 11 / الدرس 11

Normalization / التطبيع

Storing every fact once. The formal rules have intimidating names; underneath, all three of the ones that matter are the same instruction, and this lesson shows the damage each one prevents.

تخزين كل حقيقة مرة واحدة. وللقواعد الرسمية أسماء مخيفة؛ وتحتها القواعد الثلاث التي تهم كلها تعليمة واحدة، ويعرض هذا الدرس الضرر الذي تمنعه كل واحدة.

What duplication actually costs

one big sheet, the way a spreadsheet grows:

guest_name  guest_phone  room   room_price  check_in     nights
Sara        0100000001   Jasmine  650       2026-01-05   3
Sara        0100000001   Lotus    1200      2026-02-14   2
Sara        0100000001   Palm     400       2026-04-02   2

Sara's phone is stored three times. Change it once and you have two answers.

The demonstration builds that sheet, then changes one guest's phone number the way a busy person would — by updating the row they were looking at. Watch what the database now believes.

Two rows now say Sara's number is one thing and one row says it is another, and no query can tell you which is true — the information about which was updated last is not in the data. This is called an update anomaly, and it has two siblings. An insert anomaly: you cannot record a new room until somebody books it, because a room only exists as part of a booking row. A delete anomaly: cancel Omar's only booking and the guest house forgets Omar existed, along with his phone number. All three are the same disease — facts sharing a row with facts they do not belong to.

The three forms, in plain words

Form	The rule	What it forbids
1NF	One value per cell, and no repeating groups	phone1, phone2, phone3 — or 'a,b,c' in one cell
2NF	Every column depends on the WHOLE key	Room price sitting in a row keyed by booking
3NF	And on nothing but the key	City stored beside a postcode that already implies it
In one sentence	Every fact lives in exactly one place, beside the thing it is a fact about

You will be asked about these three in interviews, and the honest summary is the fourth row. If you can explain the update anomaly you saw above, and say which table each fact belongs to and why, you understand normalization better than someone who can recite the definitions.

The other side of the same coin is deliberate denormalization for speed. A dashboard that counts a million bookings on every page load may store the count and update it as bookings arrive. That is a legitimate trade — you have chosen to maintain two copies in exchange for a fast page — but it is a trade, not a default. Normalize first, measure, and denormalize only the specific thing that proved too slow, with a comment saying what keeps the copies in step.

واختبار سريع تجريه على أي جدول دون معرفة القواعد الرسمية: أشِر إلى خلية واسأل "عمّ هذه حقيقة؟" فإن لم يكن الجواب هو الشيء الذي سُمّي الجدول باسمه فالخلية في الجدول الخطأ. وفي الورقة المسطحة أعلاه، room_price حقيقة عن غرفة تجلس في جدول حجوزات — وذلك السؤال وحده يجدها.

Try it live / جرّب بنفسك

<h3>The flat sheet</h3>
<div id="flat"></div>

<h3>Try things</h3>
<div id="try"></div>

<script>
  MiniSQL.run("CREATE TABLE sheet (id INT, guest VARCHAR(60), phone VARCHAR(20), room VARCHAR(40), price INT)");
  MiniSQL.run("INSERT INTO sheet (id, guest, phone, room, price) VALUES "
    + "(1, 'Sara', '0100000001', 'Jasmine', 650),"
    + "(2, 'Sara', '0100000001', 'Lotus', 1200),"
    + "(3, 'Sara', '0100000001', 'Palm', 400)");

  sqlTable('#flat', 'SELECT * FROM sheet');
  sqlConsole('#try', "UPDATE sheet SET phone = '0111111111' WHERE id = 2;");
</script>
<style>
  body { font-family: system-ui, sans-serif; padding: 1rem; }
  h3 { font-size: .85rem; color: #64748b; margin: 1rem 0 .3rem; }
</style>

Preview / المعاينة

Check yourself / اختبر نفسك

1. What is an update anomaly?

2. Should an invoice store the price it charged, when the room already has a price?

3. What is the one-sentence version of 1NF, 2NF and 3NF?

Your task / مهمتك

Start from a deliberately flat table — one sheet with everything in it, at least eight columns and enough rows that something repeats three times. Reproduce all three anomalies on it: update one copy of a repeated fact and show the contradiction, name something you cannot record until an unrelated row exists, and delete a row to make a fact disappear that should have survived. Then split it into normalized tables, show the same three operations behaving properly, and identify one value you would still store twice on purpose, with your reason.

ابدأ من جدول مسطح عمدًا — ورقة واحدة فيها كل شيء، بثمانية أعمدة على الأقل وصفوف تكفي لتكرار شيء ثلاث مرات. وأعد إنتاج الشذوذات الثلاثة عليه: حدّث نسخة من حقيقة مكررة وأظهر التناقض، وسمِّ شيئًا لا تستطيع تسجيله حتى يوجد صف لا علاقة له به، واحذف صفًا لتُختفي حقيقة كان ينبغي أن تنجو. ثم قسّمه إلى جداول مطبَّعة، وأظهر العمليات الثلاث نفسها تتصرف كما ينبغي، وحدّد قيمة واحدة ستظل تخزّنها مرتين عمدًا مع سببك.

A flat table with a fact repeated three times جدول مسطح بحقيقة مكررة ثلاث مرات
All three anomalies reproduced and shown الشذوذات الثلاثة مُعادة ومعروضة
The normalized split, with the same operations working التقسيم المطبَّع والعمليات نفسها تعمل
One deliberate duplication, with its reason تكرار متعمَّد واحد مع سببه