# فصل ۰۵ — کِی عمداً تکرار می‌کنیم

<div dir="rtl">

# فصل ۰۵ — کِی عمداً تکرار می‌کنیم

## در این فصل چه یاد می‌گیری

فصلِ قبل شش بخش گفت هر واقعیت فقط یک جا. این فصل همان قاعده را عمداً می‌شکند — **و اجازهٔ شکستنش را با عدد می‌گیرد، نه با سلیقه.**

پرسشِ «پرامانت‌ترین کتاب‌ها کدام‌اند؟» روی مدلِ نرمال‌شده باید هر بار ۲۶ هزار سطرِ رویداد را بگردد. اگر همان عدد را در یک ستونِ کنارِ خودِ کتاب نگه داریم، خواندنش تقریباً رایگان می‌شود. **هر دو زمان را اندازه می‌گیریم.**

و بعد هر دو هزینه‌اش را هم اندازه می‌گیریم. **هزینهٔ اول کوچک است: نوشتن حدودِ یک‌ونیم برابر کند می‌شود.** هزینهٔ دوم آن چیزی است که کارِ واقعی را خراب می‌کند: **بعد از ۵۰۰ امانتِ تازه که فقط در یک جا ثبت شوند، آن ستون دقیقاً ۵۰۰ واحد دروغ می‌گوید — و هیچ خطایی نمی‌دهد.**

و در راه، یک عددِ غیرمنتظره پیدا می‌کنیم که خودش یک درسِ کامل است.

![یک قفسهٔ ابزار با نسخهٔ دومِ یک ابزار که نزدیک دست گذاشته شده و نسخهٔ اصلی در انتهای قفسه مانده](/lesson-assets/t4-s05-denormalization/prompt/01-image-second-copy-at-hand.png)

آخر این فصل می‌توانی:

- تبادلِ خواندن و نوشتن را با دو عددِ اندازه‌گیری‌شده توضیح بدهی
- یک ستونِ محاسبه‌شده یا یک جدولِ خلاصه بسازی و درستی‌اش را ثابت کنی
- هزینهٔ ناسازگاری را بشماری، نه اینکه دربارهٔ آن حرف بزنی
- سه `TRIGGER` بنویسی که خودِ پایگاه داده ستونِ خلاصه را سازگار نگه دارد، و قیمتش را با دو عدد بگویی
- بگویی کِی این معامله را نباید کرد

## قبل از شروع

**از فصلِ ۴:** نرمال‌سازی و وابستگیِ تابعی. **از ترمِ ۳ فصلِ ۳:** تجمیع قبل از پیوند. **از فصلِ ۳ همین ترم:** آن ۳۶ رویدادِ یتیم — که در بخشِ ۲ از یک راهِ کاملاً تازه دوباره پیدایشان می‌کنیم.

**تعریف، در یک جمله:** غیرنرمال‌سازی یعنی عمداً یک واقعیت را دو جا نگه داری تا خواندنش ارزان شود. **کلمهٔ مهم «عمداً» است** — همان کاری که فصلِ ۴ به‌عنوان اشتباه نشانش داد، اینجا با چشمِ باز و با عدد انجام می‌شود.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-4-modelling/session-05-denormalization.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `catalog_raw` | ۱٬۲۰۰ | کمتر از یک ثانیه |
| `members_raw` | ۳۰۰ | آنی |
| `loan_events` | ۲۶٬۱۱۹ | حدودِ یک ثانیه |

<div dir="rtl">

## ۱. پرسشی که هر بار ۲۶ هزار سطر را می‌گردد

</div>

```python
SLOW = """
    SELECT c.title, COUNT(e.id) AS loans
    FROM catalog_raw AS c
    LEFT JOIN loan_events AS e ON e.catalog_id = c.id AND e.kind = 'out'
    GROUP BY c.id, c.title
    ORDER BY loans DESC, c.id
    LIMIT 3
"""
print(q(SLOW))
```

```text
              title  loans
0        پنجره و پل     44
1  در جست‌وجوی چراغ     44
2      چراغ پشت برف     44
```

<div dir="rtl">

جواب درست است و پرسش هم درست نوشته شده. **این تجمیع بعد از پیوند است، ولی fan-out خطری ندارد:** فقط یک طرفِ پیوند چندتایی است و `COUNT(e.id)` خودِ همان سطرها را می‌شمارد. `LEFT JOIN` هم کتابِ بی‌امانت را نگه می‌دارد. حالا زمانش:

</div>

```python
print("زمان اجرا:", timed(SLOW), "میلی‌ثانیه")
print()
print(plan(SLOW))
```

```text-approx
زمان اجرا: 10.42 میلی‌ثانیه
       id  parent  notused                                             detail
0       9       0        0                                             SCAN c
1      13       0        0        BLOOM FILTER ON e (catalog_id=? AND kind=?)
2      26       0        0  SEARCH e USING AUTOMATIC PARTIAL COVERING INDEX
3      74       0        0                       USE TEMP B-TREE FOR ORDER BY
```

<div dir="rtl">

**عددِ دقیقِ زمان روی ماشینِ تو فرق می‌کند** — به همین دلیل در بلوکِ جدا آمده و ادعای قطعی نیست.

و آن تابعِ `plan` تابعِ سومِ سلولِ راه‌اندازی است که از ترمِ ۱ آنجا بوده و تا امروز به آن کاری نداشتیم: **نقشهٔ اجرا را می‌دهد، یعنی کاری که پایگاه داده واقعاً می‌کند، نه کاری که فکر می‌کنی می‌کند.** فعلاً همین‌قدر بخوانش که کلِ جدولِ کتاب‌ها **پیمایش** می‌شود و برای هر کتاب سراغِ رویدادها می‌رود، و آخرش یک درختِ موقت برای مرتب‌سازی ساخته می‌شود. **متنِ این نقشه به نسخهٔ `SQLite` بند است، پس ادعای قطعی نیست و در بلوکِ جدا آمده؛ ترمِ ۵ فصلِ ۲ کلمه‌به‌کلمه بازش می‌کند.**

و مهم‌تر از عدد: **این کار با بزرگ شدنِ جدولِ رویدادها بزرگ‌تر می‌شود.** امروز ۲۶ هزار سطر است؛ سالِ بعد صدهزار.

## ۲. ستونِ محاسبه‌شده — و عددی که انتظارش را نداشتیم

راهِ‌حل ساده است: همان شمارش را **یک بار** حساب کن و کنارِ خودِ کتاب بگذار.

</div>

```python
con.executescript("""
    DROP TABLE IF EXISTS books_hot;
    CREATE TABLE books_hot (
        id         INTEGER PRIMARY KEY,
        title      TEXT    NOT NULL,
        branch     TEXT    NOT NULL,
        loan_count INTEGER NOT NULL
    );
    INSERT INTO books_hot (id, title, branch, loan_count)
    SELECT c.id, c.title, c.branch,
           (SELECT COUNT(*) FROM loan_events AS e
            WHERE e.catalog_id = c.id AND e.kind = 'out')
    FROM catalog_raw AS c;
""")
con.commit()

FAST = """
    SELECT title, loan_count AS loans
    FROM books_hot
    ORDER BY loans DESC, id
    LIMIT 3
"""
print(q(FAST))
print()
print("جمع ستون loan_count      :",
      int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0]))
print("شمارش مستقیم رویدادهای out:",
      int(q("SELECT COUNT(*) AS n FROM loan_events WHERE kind = ?", ("out",)).iloc[0, 0]))
```

```text
              title  loans
0        پنجره و پل     44
1  در جست‌وجوی چراغ     44
2      چراغ پشت برف     44

جمع ستون loan_count      : 13422
شمارش مستقیم رویدادهای out: 13458
```

<div dir="rtl">

**سه سطرِ اول دقیقاً همان سه سطرِ بخشِ ۱ است. ولی دو عددِ پایین یکی نیستند — و اختلافشان ۳۶ است.**

سی‌وشش. **همان ۳۶ رویدادِ یتیمِ فصلِ ۳.** ستونِ `loan_count` از `catalog_raw` ساخته شد و آن شش کتاب در `catalog_raw` وجود ندارند، پس رویدادهایشان هیچ خانه‌ای برای نشستن نداشتند و بی‌صدا افتادند بیرون.

**این سومین مسیرِ کاملاً مستقلی است که به همان عدد رسیده‌ایم** — یک بار با `NOT EXISTS`، یک بار با `PRAGMA foreign_key_check`، و حالا یک بار از راهِ اختلافِ دو جمع. و درسش فراتر از این عدد است: **یک جدولِ خلاصه، وضعیتِ خرابیِ امروزِ داده را در خودش منجمد می‌کند.** هر کسی که فردا فقط `books_hot` را بخواند، دیگر هیچ نشانی از آن ۳۶ رویداد نمی‌بیند.

حالا زمانِ خواندن:

</div>

```python
print("خواندن از مدل نرمال‌شده:", timed(SLOW), "میلی‌ثانیه")
print("خواندن از ستون آماده  :", timed(FAST), "میلی‌ثانیه")
print()
print(plan(FAST))
```

```text-approx
خواندن از مدل نرمال‌شده: 10.42 میلی‌ثانیه
خواندن از ستون آماده  : 0.15 میلی‌ثانیه
   id  parent  notused                        detail
0   4       0        0                SCAN books_hot
1  17       0        0  USE TEMP B-TREE FOR ORDER BY
```

<div dir="rtl">

**ده و نیم میلی‌ثانیه در برابرِ پانزده‌صدم — حدودِ هفتاد برابر.** و نقشهٔ اجرا می‌گوید چرا: جدولِ رویدادها اصلاً باز نشد.

> ✅ **چک کن:** سه سطرِ اولِ `FAST` باید دقیقاً همان سه سطرِ `SLOW` باشند، با همان عددها. اگر نبودند، شرطِ `kind = 'out'` را در ساختنِ ستون جا انداخته‌ای و داری رویدادها را می‌شماری نه امانت‌ها — همان اشتباهِ فصلِ ۱، این بار منجمدشده در یک ستون.

## ۳. حالا هزینهٔ نوشتن را بشمار

ادعای «سریع‌تر شد» بدونِ عددِ طرفِ دیگر نصفِ ادعاست. هر امانتِ تازه حالا باید **دو جا** ثبت شود.

**دو چیزِ تازه در کدِ زیر هست که فعلاً فقط به‌شکلِ مکانیکی به‌کارشان می‌بریم.** اولی `BEGIN` است: یعنی «از اینجا تا `commit` بعدی، همهٔ نوشتن‌ها یک واحدند». دومی این است که به‌جای `timed` تابعِ خودمان را نوشتیم، چون `timed` برای پرسش‌های خواندنی است و ما داریم نوشتن را می‌سنجیم — **ولی همان قاعده را نگه داشتیم: میانهٔ چند اجرا، نه یک اجرا.** ترمِ ۵ فصلِ ۵ تراکنش را کامل باز می‌کند و فصلِ ۱ همان ترم اندازه‌گیریِ زمان را.

</div>

```python
import time

BATCH = [(i % 1200 + 1, 7, "2025-06-30 12:00:00", "out") for i in range(2000)]


def plain():
    con.execute("BEGIN")
    for row in BATCH:
        con.execute("""
            INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
            VALUES (?, ?, ?, ?)
        """, row)
    con.commit()


def maintained():
    con.execute("BEGIN")
    for row in BATCH:
        con.execute("""
            INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
            VALUES (?, ?, ?, ?)
        """, row)
        con.execute("UPDATE books_hot SET loan_count = loan_count + 1 WHERE id = ?",
                    (row[0],))
    con.commit()


def median_ms(job, repeat=5):
    times = []
    for _ in range(repeat):
        start = time.perf_counter()
        job()
        times.append((time.perf_counter() - start) * 1000)
    return round(sorted(times)[len(times) // 2], 2)


print("۲۰۰۰ امانت، فقط در دفتر رویداد :", median_ms(plain), "میلی‌ثانیه")
print("۲۰۰۰ امانت، در دفتر و در ستون  :", median_ms(maintained), "میلی‌ثانیه")
```

```text-approx
۲۰۰۰ امانت، فقط در دفتر رویداد : 8.27 میلی‌ثانیه
۲۰۰۰ امانت، در دفتر و در ستون  : 13.02 میلی‌ثانیه
```

<div dir="rtl">

**حدودِ یک‌ونیم برابر کندتر — و این عددِ صادقانه‌ای است که باید همان‌طور گزارش شود، نه بزرگ‌تر و نه کوچک‌تر.**

معامله تا اینجا روشن است: **خواندن حدودِ هفتاد برابر ارزان‌تر، نوشتن حدودِ یک‌ونیم برابر گران‌تر.** اگر کارِ تو صد بار خواندن به‌ازای هر نوشتن است، این معامله قطعاً به سودِ توست. اگر برعکس است، اصلاً به آن دست نزن.

**ولی این هنوز هزینهٔ اصلی نیست.**

## ۴. هزینهٔ واقعی: ناسازگاری

جدولِ ما بعد از آزمایشِ بالا به‌هم ریخته — پنج بار `plain` اجرا شد و ستون را به‌روز نکرد. اول یک بار از نو حسابش می‌کنیم و **زمانِ همین ترمیم را هم می‌شماریم**، چون آن هم بخشی از هزینه است.

</div>

```python
start = time.perf_counter()
con.execute("""
    UPDATE books_hot
    SET loan_count = (SELECT COUNT(*) FROM loan_events AS e
                      WHERE e.catalog_id = books_hot.id AND e.kind = 'out')
""")
con.commit()
repair_ms = round((time.perf_counter() - start) * 1000, 2)

events = int(q("SELECT COUNT(*) AS n FROM loan_events WHERE kind = ?", ("out",)).iloc[0, 0])
column = int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0])
print("رویدادهای out    :", events)
print("جمع ستون خلاصه   :", column)
print("اختلاف            :", events - column, "(همان ۳۶ یتیم)")
```

```text
رویدادهای out    : 33458
جمع ستون خلاصه   : 33422
اختلاف            : 36 (همان ۳۶ یتیم)
```

<div dir="rtl">

حالا هر دو طرف سازگارند. **و همین سازگاری را با پانصد امانتِ تازه می‌شکنیم — با کاری که در دنیای واقعی هر روز اتفاق می‌افتد: یک اسکریپت که کسی سه سال پیش نوشته و از وجودِ ستونِ خلاصه خبر ندارد.**

</div>

```python
con.execute("BEGIN")
for i in range(500):
    con.execute("""
        INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
        VALUES (?, ?, ?, ?)
    """, (i % 1200 + 1, 9, "2025-06-30 13:00:00", "out"))
con.commit()

drift = q("""
    SELECT COUNT(*) AS wrong_books, SUM(true_count - loan_count) AS total_drift
    FROM (
        SELECT h.id, h.loan_count,
               (SELECT COUNT(*) FROM loan_events AS e
                WHERE e.catalog_id = h.id AND e.kind = 'out') AS true_count
        FROM books_hot AS h
    )
    WHERE true_count <> loan_count
""")
print(drift)
print()
print("خطایی گرفتیم؟ نه. پرسش‌های بالا همه بدون اعتراض اجرا شدند.")
```

```text
   wrong_books  total_drift
0          500          500

خطایی گرفتیم؟ نه. پرسش‌های بالا همه بدون اعتراض اجرا شدند.
```

<div dir="rtl">

**پانصد واحد دروغ، پخش‌شده روی ۵۰۰ کتاب — و هیچ‌کس چیزی نگفت.**

این خطا از هر خطای دیگری در این ترم بدتر است، و دلیلش سه چیز است:

1. **پیام ندارد.** نه `INSERT` اعتراض کرد و نه `SELECT`. گزارشی که از `books_hot` بخوانی کاملاً سالم به‌نظر می‌رسد.
2. **رشد می‌کند.** هر امانتِ بعدیِ آن اسکریپت یک واحد به فاصله اضافه می‌کند. امروز ۵۰۰، ماهِ بعد ۵۰٬۰۰۰.
3. **مقصرش پیدا نیست.** آن اسکریپت هیچ کارِ اشتباهی نکرد — سطرش را درست وارد کرد. **کاری که نکرد، کاری بود که هیچ‌جا نوشته نشده بود.**

و ترمیمش هم رایگان نیست. زمانِ همان `UPDATE`ی که بالای همین بخش زدیم:

</div>

```python
print("ترمیم کامل ستون:", repair_ms, "میلی‌ثانیه")
```

```text-approx
ترمیم کامل ستون: 2085.66 میلی‌ثانیه
```

<div dir="rtl">

**دو ثانیه — دویست برابرِ همان پرسشی که غیرنرمال‌سازی می‌خواست سریع‌ترش کند.** یعنی ترمیم کاری نیست که بشود در هر درخواست انجامش داد؛ کاری است که شبانه انجام می‌شود، و بینِ دو ترمیم داده‌ات کهنه است.

**و درسِ مهندسی‌اش این است: هر جا یک واقعیت را دو جا نگه می‌داری، باید یک راهِ خودکار برای سازگار نگه داشتنشان باشد** — یک `trigger` در خودِ پایگاه داده، یا یک کارِ زمان‌بندی‌شده که هر شب از نو حساب کند. اگر راهش «همه یادشان باشد» است، **آن اصلاً راه نیست.**

**و آزمونِ سازگاری را همیشه بنویس، حتی وقتی مطمئنی.** همان پرسشِ بالا — «چند کتاب عددِ غلط دارند؟» — باید هر شب اجرا شود و جوابش صفر باشد. اگر نبود، همان‌جا معلوم می‌شود، نه سه ماه بعد در یک گزارشِ مدیریتی.

## ۵. `TRIGGER`: نگهبانی که یادش نمی‌رود

بخشِ قبل گفت «یک راهِ خودکار لازم است». حالا می‌سازیمش. **`TRIGGER` قاعده‌ای است که به یک جدول می‌چسبد و هر بار که کسی روی آن جدول می‌نویسد، خودِ پایگاه داده اجرایش می‌کند** — چه از پایتون، چه از یک اسکریپتِ سه‌ساله، چه دستی.

سه راهِ نوشتن داریم و برای هر سه یکی لازم است. داخلِ `TRIGGER` دو نامِ مخصوص در دسترس‌اند: **`NEW` سطر را بعد از نوشتن نشان می‌دهد و `OLD` قبلش را.** درج فقط `NEW` دارد، حذف فقط `OLD`، و به‌روزرسانی هر دو.

اول ستون را از نو حساب می‌کنیم تا از نقطهٔ درستی شروع کنیم، بعد نگهبان‌ها را می‌گذاریم:

</div>

```python
con.execute("""
    UPDATE books_hot
    SET loan_count = (SELECT COUNT(*) FROM loan_events AS e
                      WHERE e.catalog_id = books_hot.id AND e.kind = 'out')
""")
con.commit()

con.executescript("""
    DROP TRIGGER IF EXISTS hot_after_insert;
    DROP TRIGGER IF EXISTS hot_after_delete;
    DROP TRIGGER IF EXISTS hot_after_update;

    CREATE TRIGGER hot_after_insert AFTER INSERT ON loan_events
    WHEN NEW.kind = 'out'
    BEGIN
        UPDATE books_hot SET loan_count = loan_count + 1 WHERE id = NEW.catalog_id;
    END;

    CREATE TRIGGER hot_after_delete AFTER DELETE ON loan_events
    WHEN OLD.kind = 'out'
    BEGIN
        UPDATE books_hot SET loan_count = loan_count - 1 WHERE id = OLD.catalog_id;
    END;

    CREATE TRIGGER hot_after_update AFTER UPDATE ON loan_events
    BEGIN
        UPDATE books_hot SET loan_count = loan_count - 1
         WHERE id = OLD.catalog_id AND OLD.kind = 'out';
        UPDATE books_hot SET loan_count = loan_count + 1
         WHERE id = NEW.catalog_id AND NEW.kind = 'out';
    END;
""")

print(q("""
    SELECT name, tbl_name FROM sqlite_master
    WHERE type = 'trigger' ORDER BY name
""").to_string(index=False))
```

```text
            name    tbl_name
hot_after_delete loan_events
hot_after_insert loan_events
hot_after_update loan_events
```

<div dir="rtl">

**سه قاعده، و هر سه به `loan_events` چسبیده‌اند نه به `books_hot`** — چون کاری که باید کنترل شود، نوشتن در دفترِ رویدادهاست.

حالا **همان پانصد امانتِ بخشِ ۴، با همان کد**، و بعد همان آزمونِ سازگاری:

</div>

```python
con.execute("BEGIN")
for i in range(500):
    con.execute("""
        INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
        VALUES (?, ?, ?, ?)
    """, (i % 1200 + 1, 9, "2025-06-30 14:00:00", "out"))
con.commit()

CONSISTENCY = """
    SELECT COUNT(*) AS wrong_books, SUM(true_count - loan_count) AS total_drift
    FROM (
        SELECT h.id, h.loan_count,
               (SELECT COUNT(*) FROM loan_events AS e
                WHERE e.catalog_id = h.id AND e.kind = 'out') AS true_count
        FROM books_hot AS h
    )
    WHERE true_count <> loan_count
"""
print(q(CONSISTENCY))
```

```text
   wrong_books total_drift
0            0        None
```

<div dir="rtl">

**صفر کتاب، در برابرِ ۵۰۰ کتابِ بخشِ ۴ — با کدِ درجِ کاملاً یکسان.** تفاوت در کدِ ما نیست؛ در این است که این بار کسی سرِ جدول ایستاده بود. و آن `None` یادآوریِ رایگانِ ترمِ ۲ فصلِ ۱ است: **`SUM` روی صفر سطر `NULL` می‌دهد، نه صفر.**

حالا دو راهِ دیگر. یک رویداد را از کتابی به کتابِ دیگر منتقل می‌کنیم و یک رویداد را حذف:

</div>

```python
moved_id = int(q("SELECT MAX(id) AS id FROM loan_events").iloc[0, 0])
con.execute("UPDATE loan_events SET catalog_id = ? WHERE id = ?", (7, moved_id))
con.execute("DELETE FROM loan_events WHERE id = ?", (moved_id - 1,))
con.commit()

print(q("SELECT id, loan_count FROM books_hot WHERE id IN (7, 500) ORDER BY id"))
print()
print(q(CONSISTENCY))
```

```text
    id  loan_count
0    7          23
1  500          30

   wrong_books total_drift
0            0        None
```

<div dir="rtl">

**هر سه راهِ نوشتن پوشش داده شدند و ستون هنوز دقیق است.** به‌روزرسانی دو کارِ جدا می‌کند — یکی از کتابِ قدیمی کم و یکی به کتابِ تازه اضافه — و اگر فقط یکی‌شان را بنویسی، هر جابه‌جایی یک واحد به خطا اضافه می‌کند.

**و حالا قیمتش.** همان تابعِ `plain` بخشِ ۳ را دو بار می‌سنجیم، یک بار با نگهبان‌ها و یک بار بدونشان. **کدِ پایتون در هر دو حالت حرف‌به‌حرف یکی است.**

</div>

```python
with_trigger = median_ms(plain)

con.executescript("""
    DROP TRIGGER hot_after_insert;
    DROP TRIGGER hot_after_delete;
    DROP TRIGGER hot_after_update;
""")
without_trigger = median_ms(plain)

con.executescript("""
    CREATE TRIGGER hot_after_insert AFTER INSERT ON loan_events
    WHEN NEW.kind = 'out'
    BEGIN
        UPDATE books_hot SET loan_count = loan_count + 1 WHERE id = NEW.catalog_id;
    END;
""")
con.execute("""
    UPDATE books_hot
    SET loan_count = (SELECT COUNT(*) FROM loan_events AS e
                      WHERE e.catalog_id = books_hot.id AND e.kind = 'out')
""")
con.commit()

print("۲۰۰۰ امانت، بدون نگهبان:", without_trigger, "میلی‌ثانیه")
print("۲۰۰۰ امانت، با trigger  :", with_trigger, "میلی‌ثانیه")
```

```text-approx
۲۰۰۰ امانت، بدون نگهبان: 9.04 میلی‌ثانیه
۲۰۰۰ امانت، با trigger  : 14.0 میلی‌ثانیه
```

<div dir="rtl">

**تقریباً همان یک‌ونیم برابرِ بخشِ ۳ — کار جایی نرفت، فقط از کدِ تو به داخلِ پایگاه داده منتقل شد.** و این دقیقاً چیزی است که می‌خواستیم: هزینه همان است، ولی دیگر به یاد داشتنِ کسی بند نیست.

> ⚠️ **مواظب باش:** `TRIGGER` فراموش‌شدنی نیست، ولی **نامرئی** است. کسی که فردا یک `INSERT` می‌زند و می‌بیند ستونِ جدولِ دیگری هم عوض شد، هیچ ردی از دلیلش در کدِ خودش نمی‌بیند. **دو عادت این را کم‌خطر می‌کند:** نامِ trigger بگوید چه‌کار می‌کند (`hot_after_insert`، نه `trg1`)، و `SELECT name, tbl_name FROM sqlite_master WHERE type = 'trigger'` جزوِ اولین کارهایت روی هر پایگاه دادهٔ ناآشنا باشد.

**و یک مرزِ صریح:** بالا سه نگهبان ساختیم و بعد فقط یکی‌شان را برگرداندیم، چون از این به بعد فقط درج می‌کنیم. **در یک سامانهٔ واقعی این خطاست** — قاعده‌ای که فقط یکی از سه راه را می‌بندد، در همان راهِ سومی می‌شکند که نبسته‌ای.

## ۶. جدولِ خلاصه: همان معامله، در دانه‌بندیِ درشت‌تر

ستونِ محاسبه‌شده کنارِ سطرِ اصلی می‌نشیند و دانه‌بندی را عوض نمی‌کند. **جدولِ خلاصه یک قدم جلوتر می‌رود: یک جدولِ کاملاً تازه در دانه‌بندیِ درشت‌تر.**

</div>

```python
BY_BRANCH = """
    SELECT c.branch, COUNT(DISTINCT c.id) AS books, COUNT(e.id) AS loans
    FROM catalog_raw AS c
    LEFT JOIN loan_events AS e ON e.catalog_id = c.id AND e.kind = 'out'
    GROUP BY c.branch
    ORDER BY c.branch
"""
con.execute("DROP TABLE IF EXISTS branch_stats")
con.execute("""
    CREATE TABLE branch_stats (
        branch TEXT PRIMARY KEY,
        books  INTEGER NOT NULL,
        loans  INTEGER NOT NULL
    )
""")
con.executemany("INSERT INTO branch_stats VALUES (?, ?, ?)",
                [tuple(r) for r in q(BY_BRANCH).itertuples(index=False)])
con.commit()

print(q("SELECT * FROM branch_stats ORDER BY branch"))
print()
print("جمع ستون books:", int(q("SELECT SUM(books) AS s FROM branch_stats").iloc[0, 0]),
      "| کتاب‌های فهرست: 1200")
print("جمع ستون loans:", int(q("SELECT SUM(loans) AS s FROM branch_stats").iloc[0, 0]),
      "| جمع ستون loan_count:",
      int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0]))
```

```text
  branch  books  loans
0   بهار    214   9310
1   دانش    187   8657
2  مرکزی    180   9621
3   نسیم    224  11001
4   پویا    195   7591
5   کوشا    200   8241

جمع ستون books: 1200 | کتاب‌های فهرست: 1200
جمع ستون loans: 54421 | جمع ستون loan_count: 54421
```

<div dir="rtl">

**شش سطر به‌جای ۱٬۲۰۰، و این بار هر دو شمارشِ کنترلی می‌خوانند.**

جمعِ ستونِ `books` دقیقاً ۱٬۲۰۰ است — هیچ کتابی دو بار شمرده نشده و هیچ‌کدام جا نمانده. و جمعِ ستونِ `loans` با جمعِ ستونِ `loan_count` هم برابر است، چون نگهبانِ بخشِ ۵ سرِ جایش نشسته.

**حالا پانصد امانتِ دیگر — و همان دو عدد را دوباره می‌گیریم.**

</div>

```python
con.execute("BEGIN")
for i in range(500):
    con.execute("""
        INSERT INTO loan_events (catalog_id, member_id, happened_at, kind)
        VALUES (?, ?, ?, ?)
    """, (i % 1200 + 1, 11, "2025-06-30 15:00:00", "out"))
con.commit()

snapshot = int(q("SELECT SUM(loans) AS s FROM branch_stats").iloc[0, 0])
column = int(q("SELECT SUM(loan_count) AS s FROM books_hot").iloc[0, 0])
print("جمع جدول خلاصه branch_stats:", snapshot)
print("جمع ستون نگهبان‌دار         :", column)
print("اختلاف                      :", column - snapshot)
```

```text
جمع جدول خلاصه branch_stats: 54421
جمع ستون نگهبان‌دار         : 54921
اختلاف                      : 500
```

<div dir="rtl">

**ستونِ نگهبان‌دار خودش را به‌روز کرد و جدولِ خلاصه نکرد.** هیچ‌کدامشان غلط محاسبه نشده‌اند؛ **تفاوتشان زمان است.** `branch_stats` یک عکس از لحظه‌ای است که گرفته شد، و آن لحظه گذشته.

**و این خطرِ مخصوصِ جدولِ خلاصه است:** یک عکسِ لحظه‌ای که **هیچ‌چیزی رویش ننوشته مالِ کدام لحظه است.** ستونِ محاسبه‌شده دستِ‌کم کنارِ سطرِ زنده‌اش می‌نشیند و حالا با آن جلو هم می‌آید؛ یک جدولِ خلاصهٔ جدا می‌تواند ماه‌ها کهنه بماند و کسی متوجه نشود. **پس هر جدولِ خلاصه یا باید نگهبانِ خودش را داشته باشد، یا دستِ‌کم یک ستونِ «آخرین به‌روزرسانی»** — و اینکه آن ستون را چطور ذخیره کنی، موضوعِ کلِ فصلِ ۷ است.

> 🔧 **اگر کار نکرد:** در این فصل تراکنش را دستی باز کردیم. اگر یکی از `commit`ها را جا بیندازی و دوباره `BEGIN` بزنی، `SQLite` صریح اعتراض می‌کند:

</div>

```python
con.commit()
con.execute("BEGIN")
try:
    con.execute("BEGIN")
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
con.rollback()
```

```text
sqlite3.OperationalError
cannot start a transaction within a transaction
```

<div dir="rtl">

**ولی خطای واقعیِ این فصل همان ۵۰۰ بود که هیچ پیامی نداشت.**

## ۷. معیارِ تصمیم: دو عدد، نه سلیقه

غیرنرمال‌سازی نه گناه است و نه راهِ‌حل. **یک معامله است، و معامله بدونِ دو عدد قمار است.** فهرستِ کارِ کوتاهِ این فصل:

1. **زمانِ خواندن را قبل و بعد اندازه بگیر.** اگر تفاوت در محدودهٔ نوسان بود، **همان را بنویس و کار را همین‌جا تمام کن** — یک بهینه‌سازیِ بی‌اثر که پیچیدگی اضافه می‌کند، بدترین حالتِ ممکن است.
2. **زمانِ نوشتن را هم اندازه بگیر.** اینجا یک‌ونیم برابر شد؛ در جدولی با پنج ستونِ خلاصه می‌تواند خیلی بیشتر شود.
3. **نسبتِ خواندن به نوشتن را بدان.** هفتاد برابر سریع‌تر شدنِ کاری که ماهی یک بار انجام می‌شود، ارزشِ آن را ندارد که کاری که ثانیه‌ای یک بار انجام می‌شود یک‌ونیم برابر کند شود.
4. **راهِ خودکارِ سازگار ماندن را از قبل بنویس.** بخشِ ۵ ساختش: سه `TRIGGER` روی هر سه راهِ نوشتن. اگر چنین راهی نداری، معامله را نکن.
5. **آزمونِ سازگاری را بنویس و زمان‌بندی‌اش کن.** جوابش باید همیشه صفر باشد.

**و یک قاعدهٔ آخر که از بخشِ ۲ آمد: هرگز مدلِ نرمال‌شده را دور نینداز.** ستونِ خلاصه از رویش ساخته می‌شود، نه به‌جایش. اگر `loan_events` را پاک کرده بودیم، آن اختلافِ ۳۶ را هیچ‌وقت پیدا نمی‌کردیم — و بدتر، **هیچ راهی برای ترمیمِ ستون هم نمی‌ماند.**

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در `books_hot` یک سطر یک کتاب است و ستونش تعدادِ امانت‌هایش؛ در `branch_stats` یک سطر یک شعبه است. **با کدام شمارشِ دوم سنجیدی؟** با سه‌تا: سه سطرِ اولِ پرسشِ سریع در برابرِ پرسشِ کند که یکی درآمدند، جمعِ ستونِ `books` که ۱٬۲۰۰ شد، و جمعِ ستونِ `loans` در برابرِ جمعِ `loan_count` که اول **خواند** و بعد از پانصد امانتِ تازه ۵۰۰ واحد از آن عقب افتاد. **چه کسی از قلم افتاد؟** ۳۶ رویدادِ یتیم که در هیچ‌کدام از دو جدولِ خلاصه نیستند — و از این به بعد هر کسی که فقط این دو جدول را بخواند، **حتی نمی‌داند که چیزی از قلم افتاده.**

> 🤖 **از دستیارت بپرس:** «غیرنرمال‌سازی چیست و چه زمانی توجیه دارد؟» بعد این را بپرس: **«اگر یک ستونِ محاسبه‌شده بگذارم، چطور مطمئن شوم هیچ‌وقت با مدلِ اصلی ناسازگار نمی‌شود؟»** — جوابِ درست دستِ‌کم یکی از این دو را نام می‌برد: `trigger` در خودِ پایگاه داده — که بخشِ ۵ ساختش — یا محاسبهٔ دوبارهٔ زمان‌بندی‌شده به‌علاوهٔ یک آزمونِ سازگاری. اگر جواب فقط گفت «حواست باشد به‌روزش کنی»، با بخشِ ۴ ردش کن.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| denormalization | دی‌نرمالایزیشن | غیرنرمال‌سازی؛ تکرارِ عمدیِ یک واقعیت برای خواندنِ ارزان‌تر |
| computed column | کامپیوتد کالمن | ستونی که مقدارش از جای دیگری حساب شده و ذخیره شده |
| summary table | سامری تیبل | جدولِ خلاصه؛ جدولی در دانه‌بندیِ درشت‌تر که از داده‌ی اصلی ساخته می‌شود |
| read-write trade-off | ریدرایت ترید‌آف | تبادلِ خواندن و نوشتن |
| stale data | استیل دیتا | دادهٔ کهنه؛ نسخه‌ای که دیگر با اصل نمی‌خواند |
| trigger | تریگر | قاعده‌ای در پایگاه داده که با هر نوشتن خودکار اجرا می‌شود |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا جمعِ ستونِ `loan_count` از شمارشِ مستقیمِ رویدادها ۳۶ واحد کمتر بود؟

**تمرینِ اجباری:** همان معامله را برای اعضا بساز: ستونِ `loan_count` در یک جدولِ `members_hot`. **سه عدد چاپ کن** — زمانِ خواندنِ «پرکارترین اعضا» از مدلِ نرمال‌شده، همان از ستونِ آماده، و جمعِ ستونِ تازه در برابرِ شمارشِ مستقیم. **اختلافِ دو عددِ آخر را حتماً توضیح بده** — راهنمایی: فصلِ ۳ گفت ۱۹ رویداد به عضوی اشاره می‌کنند که وجود ندارد.

**تمرینِ ستاره‌دار ⭐:** آزمونِ سازگاریِ بخشِ ۴ را به یک تابع تبدیل کن که هر جدولِ خلاصه‌ای را بگیرد و بگوید چند سطرش با واقعیت نمی‌خواند. رویش `books_hot` و `branch_stats` را بزن. بعد سؤالِ سخت را جواب بده: **این آزمون چقدر طول می‌کشد، و آیا از خودِ پرسشی که غیرنرمال‌سازی می‌خواست سریع‌ترش کند ارزان‌تر است؟** جوابت باید بگوید این یعنی آزمون را با چه فاصلهٔ زمانی می‌شود اجرا کرد.

## در فصل بعد

پنج فصل است دربارهٔ ساختار حرف می‌زنیم. فصلِ بعد سراغِ **قانون** می‌رود: چیزهایی که در اسکیما می‌نویسی تا پایگاه داده خودش جلوی دادهٔ غلط را بگیرد.

و با سه عیبی شروع می‌کند که ترمِ ۱ در همین داده کاشت و از آن روز تا حالا دست‌نخورده مانده‌اند: **۲۳۱ سطر بدونِ امتیاز، ۳۷ مقدارِ متنی در ستونی که `INTEGER` اعلام شده، و ۱۹ سطر که نامِ نویسنده‌شان با نویسهٔ عربی وارد شده.** برای هر سه یک قید می‌نویسیم و **می‌شماریم که هرکدام دقیقاً چند سطر از دادهٔ موجود را رد می‌کند.**

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t4/s05-denormalization (یادستان, Yaadestan)
