دیتابیس و SQLدیتابیس در کار واقعیدرس ۶ از ۱۰

طراحی جدول و نرمال‌سازی

جدول‌هایی طراحی کن که با گذشت زمان خراب نشوند: مشکل جدول تخت شبیه اکسل و ناهماهنگی داده، قانون «هر واقعیت یک جا»، شناسایی موجودیت و کلید، رابطه‌ی یک‌به‌چند و چندبه‌چند با جدول واسط، انتخاب نوع داده (پول، زمان، متن)، قیدهای UNIQUE و CHECK، رفتار ON DELETE، و تغییر جدول با ALTER TABLE.

حدود ۲۱ دقیقه مطالعه
۸ مثال اجراشده
۳ تمرین با جواب
مقدماتی تا متوسط
به‌روزرسانی: ۲۹ مهر ۱۴۰۵

توی این درس یاد می‌گیری قبل از نوشتن CREATE TABLE فکر کنی. می‌بینی جدول «تخت» شبیه اکسل چطور بعد از چند ماه داده‌ی متناقض تولید می‌کند، قانون ساده‌ی «هر واقعیت فقط یک جا» (که اسم رسمی‌اش نرمال‌سازی است) را یاد می‌گیری، موجودیت‌ها و رابطه‌ها را پیدا می‌کنی (یک‌به‌چند، چندبه‌چند با جدول واسط)، نوع داده‌ی درست را برای پول و زمان و متن انتخاب می‌کنی، با قیدها (UNIQUE، CHECK، ON DELETE) قانون‌های کسب‌وکار را به دیتابیس می‌سپاری و جدول موجود را با ALTER TABLE تغییر می‌دهی.

مسئله: جدولی که کم‌کم دروغ می‌گوید

کسی که از اکسل می‌آید، اولین جدولش معمولاً این شکلی است: هر سطر یک سفارش، با اسم و شهر مشتری و فهرست کالاها در یک خانه. روز اول عالی کار می‌کند. سه ماه بعد، یک مشتری با سه شهر مختلف داری، نمی‌توانی بشماری از هر کالا چند تا فروخته‌ای، و برای ثبت مشتری‌ای که هنوز خرید نکرده جایی نیست. دیتابیس خطایی نداده؛ طراحی اجازه‌ی این تناقض‌ها را داده.

تشبیه: دفترچه‌ی تلفن و یادداشت‌ها

اگر شماره‌ی تلفن دوستت را در هر یادداشتی که درباره‌اش می‌نویسی تکرار کنی، روزی که شماره‌اش عوض شود باید همه‌ی یادداشت‌ها را پیدا و درست کنی و حتماً یکی جا می‌ماند. راه درست: شماره یک بار در دفترچه‌ی تلفن، و یادداشت‌ها فقط می‌گویند «علی». نرمال‌سازی همین است: هر واقعیت (شماره‌ی علی، شهر سارا، قیمت ماوس) یک جا نوشته می‌شود و بقیه فقط به آن اشاره می‌کنند.

جدول تخت همه‌چیز را تکرار می‌کند؛ طراحی نرمال هر چیز را یک بار نگه می‌دارد و با کلید خارجی به آن اشاره می‌کند.

مثال‌های عملی

داده را تازه کن:

psql shop -f ~/shop.sql
psql shop

مثال ۱: ناهماهنگی را با چشم خودت ببین

یک جدول تخت موقت بساز:

CREATE TABLE sales_flat (
    order_id INTEGER,
    customer_name TEXT,
    customer_city TEXT,
    products TEXT,
    total INTEGER
);
INSERT INTO sales_flat VALUES
    (1, 'سارا احمدی', 'تهران', 'کتاب پایتون، ماوس بی‌سیم x2', 1220000),
    (2, 'رضا کریمی', 'اصفهان', 'کیبورد مکانیکی', 1890000),
    (3, 'سارا احمدی', 'تهران', 'فلش 64 گیگ x3، کابل شبکه x5', 1345000);

سارا به کرج رفته و کسی فقط سفارش آخرش را درست می‌کند:

UPDATE sales_flat SET customer_city = 'کرج' WHERE order_id = 3;
SELECT DISTINCT customer_name, customer_city FROM sales_flat ORDER BY 1;

 customer_name | customer_city
---------------+---------------
 رضا کریمی     | اصفهان
 سارا احمدی    | تهران
 سارا احمدی    | کرج
(3 rows)

حالا دیتابیس دو جواب برای «سارا کجاست؟» دارد. به این ناهنجاری به‌روزرسانی (update anomaly) می‌گویند. ستون products هم مشکل دارد: «چند ماوس فروخته‌ایم؟» را فقط با جست‌وجوی متنی (LIKE '%ماوس%') می‌شود تقریب زد، و x2 داخل متن را هیچ تابعی جمع نمی‌زند. و total را هم باید دستی حساب و درست نگه داری.

DROP TABLE sales_flat;

همان کار در طراحی درست:

UPDATE customers SET city = 'کرج' WHERE id = 1;
SELECT o.id, c.name, c.city
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.id = 1;

UPDATE 1
 id |    name    | city
----+------------+------
  1 | سارا احمدی | کرج
  3 | سارا احمدی | کرج
(2 rows)

یک سطر عوض شد و همه‌ی سفارش‌های سارا درست‌اند، چون شهر فقط یک جا نوشته شده.

مثال ۲: سه قانون نرمال‌سازی، به زبان ساده

کتاب‌ها نرمال‌سازی را با «فرم‌های نرمال» (1NF، 2NF، 3NF) توضیح می‌دهند. برای کار روزمره این سه سؤال کافی است:

هر خانه یک مقدار؟ (فرم اول) فهرست، چند مقدار با کاما، یا x2 داخل متن ممنوع. چند کالا = چند سطر (order_items).
هر ستون درباره‌ی کل کلید است؟ (فرم دوم) در order_items با کلید (order_id, product_id)، ستون qty به هر دو بستگی دارد (چند تا از این کالا در این سفارش). ولی «اسم محصول» فقط به product_id بستگی دارد، پس جایش products است.
هیچ ستونی درباره‌ی ستون دیگری نیست؟ (فرم سوم) در orders، «شهر مشتری» درباره‌ی مشتری است، نه سفارش؛ پس جایش customers است و سفارش فقط customer_id دارد.

خلاصه‌ی معروفش: هر ستون باید درباره‌ی «کلید، کل کلید، و هیچ چیز جز کلید» باشد.

مثال ۳: موجودیت‌ها و رابطه‌ها

برای طراحی، اسم‌های جمله‌های کسب‌وکار را پیدا کن: «مشتری سفارش می‌دهد؛ هر سفارش چند محصول دارد». هر اسم یک موجودیت (entity) و یک جدول است. بعد بپرس «هر کدام از این، چند تا از آن دارد؟»:

رابطه	مثال	پیاده‌سازی
یک‌به‌چند	یک مشتری، چند سفارش	کلید خارجی در سمت «چند» (`orders.customer_id`)
چندبه‌چند	یک سفارش چند محصول، یک محصول در چند سفارش	جدول واسط با دو کلید خارجی (`order_items`)
یک‌به‌یک	یک کاربر، یک پروفایل	کلید خارجی `UNIQUE` (یا همان جدول)

جدول واسط فقط برای اتصال نیست؛ اغلب اطلاعات خود رابطه را نگه می‌دارد: qty و unit_price نه مال سفارش‌اند و نه مال محصول، مال «این محصول در این سفارش»‌اند.

مسیر طراحی: از جمله‌های کسب‌وکار تا جدول‌ها و قیدها.

مثال ۴: یک رابطه‌ی چندبه‌چند بساز

محصول‌ها برچسب می‌گیرند («پرفروش»، «هدیه») و هر برچسب روی چند محصول است:

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
CREATE TABLE product_tags (
    product_id INTEGER NOT NULL REFERENCES products (id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);
INSERT INTO tags (name) VALUES ('پرفروش'), ('هدیه'), ('حرفه‌ای');
INSERT INTO product_tags VALUES (4, 1), (4, 2), (5, 3), (1, 2), (8, 1);

SELECT p.name, string_agg(t.name, '، ' ORDER BY t.name) AS tags
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id, p.name
ORDER BY p.id;

      name      |     tags
----------------+--------------
 کتاب پایتون    | هدیه
 ماوس بی‌سیم     | هدیه، پرفروش
 کیبورد مکانیکی | حرفه‌ای
 فلش 64 گیگ     | پرفروش
(4 rows)

کلید اصلی دوتایی جلوی برچسب تکراری را می‌گیرد:

INSERT INTO product_tags VALUES (4, 1);

ERROR:  duplicate key value violates unique constraint "product_tags_pkey"
DETAIL:  Key (product_id, tag_id)=(4, 1) already exists.

و «همه‌ی محصول‌های هدیه» یک WHERE t.name = 'هدیه' است، نه جست‌وجوی متنی در یک ستون tags TEXT که «هدیه» و «هدیه‌ی تولد» را قاطی کند.

مثال ۵: `ON DELETE`، وقتی والد حذف می‌شود

درس قبل دیدی سفارشی که قلم دارد حذف نمی‌شود:

DELETE FROM orders WHERE id = 5;

ERROR:  update or delete on table "orders" violates foreign key constraint "order_items_order_id_fkey" on table "order_items"
DETAIL:  Key (id)=(5) is still referenced from table "order_items".

این رفتار پیش‌فرض (NO ACTION/RESTRICT) امن است. ولی اقلام سفارش بدون سفارش معنی ندارند؛ منطقی است با حذف سفارش، اقلامش هم بروند. قید را عوض کن:

ALTER TABLE order_items DROP CONSTRAINT order_items_order_id_fkey;
ALTER TABLE order_items ADD CONSTRAINT order_items_order_id_fkey
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE;
DELETE FROM orders WHERE id = 5;
SELECT count(*) FROM order_items WHERE order_id = 5;

ALTER TABLE
ALTER TABLE
DELETE 1
 count
-------
     0
(1 row)

کدام را انتخاب کنی سؤال کسب‌وکار است، نه فنی:

گزینه	با حذف والد	مثال مناسب
پیش‌فرض (`NO ACTION`)	خطا	مشتری با سفارش (تاریخچه‌ی مالی نباید گم شود)
`ON DELETE CASCADE`	فرزندها هم حذف	اقلام سفارش، برچسب‌های محصول
`ON DELETE SET NULL`	ستون فرزند خالی می‌شود	«کارمند مسئول» یک تیکت که کارمندش رفته

CASCADE را با احتیاط بگذار: یک DELETE کوچک ممکن است زنجیره‌ای هزاران سطر را در چند جدول پاک کند.

مثال ۶: قید `UNIQUE` و `ALTER TABLE`

جدول‌ها با کسب‌وکار عوض می‌شوند. حالا مشتری‌ها ایمیل لازم دارند و ایمیل نباید تکراری باشد:

ALTER TABLE customers ADD COLUMN email TEXT;
UPDATE customers SET email = 'sara@example.com' WHERE id = 1;
UPDATE customers SET email = 'reza@example.com' WHERE id = 2;
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);
UPDATE customers SET email = 'sara@example.com' WHERE id = 3;

ALTER TABLE
UPDATE 1
UPDATE 1
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(sara@example.com) already exists.

ستون جدید برای سطرهای قبلی NULL است؛ برای همین NOT NULL نگذاشتیم (اگر می‌خواهی، اول همه را پر کن و بعد ALTER TABLE customers ALTER COLUMN email SET NOT NULL). UNIQUE چند NULL را مجاز می‌داند، چون NULL با هیچ چیز، حتی خودش، «برابر» نیست. با \d نتیجه را ببین:

\d customers

                              Table "public.customers"
  Column   |  Type   | Collation | Nullable |                Default
-----------+---------+-----------+----------+---------------------------------------
 id        | integer |           | not null | nextval('customers_id_seq'::regclass)
 name      | text    |           | not null |
 city      | text    |           | not null |
 joined_on | date    |           | not null | CURRENT_DATE
 email     | text    |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

می‌بینی که UNIQUE خودش یک ایندکس ساخته (درس بعد).

مثال ۷: نوع داده‌ی درست

SELECT 0.1 + 0.2 = 0.3 AS numeric_ok, 0.1::float + 0.2::float = 0.3::float AS float_ok;
SELECT 0.1::float + 0.2::float AS f;

 numeric_ok | float_ok
------------+----------
 t          | f
(1 row)

          f
---------------------
 0.30000000000000004
(1 row)

عددهای اعشاری شناور (float، real، double precision) در مبنای دو ذخیره می‌شوند و ۰٫۱ را دقیق نمی‌توانند نگه دارند (همان بحث درس بیت و بایت). برای پول هرگز float نگذار:

داده	نوع پیشنهادی	چرا
پول	`INTEGER`/`BIGINT` (به ریال یا تومان) یا `NUMERIC(12,2)`	دقیق، بدون خطای گرد کردن
شمارنده و شناسه	`INTEGER`؛ جدول خیلی بزرگ `BIGINT`	`INTEGER` تا حدود ۲ میلیارد
متن	`TEXT`	در PostgreSQL محدودیت طول لازم نیست؛ با `CHECK` محدود کن
روز	`DATE`	تاریخ تولد، تاریخ سفارش
لحظه	`TIMESTAMPTZ`	زمان دقیق با منطقه‌ی زمانی؛ همیشه به‌جای `TIMESTAMP`
بله/خیر	`BOOLEAN`	به‌جای `'yes'`/`'no'` یا ۰ و ۱

مثال ۸: نام‌گذاری یکدست

یک قرارداد ساده انتخاب کن و همه‌جا رعایتش کن: اسم جدول جمع و با حروف کوچک انگلیسی (customers، order_items)، کلمه‌ها با زیرخط (created_on)، کلید اصلی id، کلید خارجی جدول_مفرد_id (customer_id). اسم‌های فارسی یا با فاصله برای جدول و ستون ممکن است، ولی هر بار باید داخل "..." بنویسی‌شان و ابزارها و کتابخانه‌ها با آن‌ها اذیت می‌کنند. داده فارسی، اسم‌ها انگلیسی.

پشت پرده: کی عمداً تکرار کنیم؟

نرمال‌سازی قانون است، نه دین. دو جا عمداً داده را تکرار می‌کنیم:

عکس لحظه‌ای (snapshot): order_items.unit_price کپی قیمت محصول است. این تکرار نیست، یک واقعیت دیگر است: «قیمتی که مشتری در آن روز پرداخت». اگر قیمت محصول را بعداً عوض کنی، فاکتور قدیمی نباید عوض شود. آدرس ارسال سفارش هم همین‌طور.
کارایی (denormalization): گاهی یک جمع پرتکرار (مثلاً تعداد نظرهای یک پست) را در یک ستون نگه می‌دارند تا هر بار شمرده نشود. این کار هزینه دارد: باید مطمئن باشی همیشه به‌روز می‌ماند. اول نرمال طراحی کن؛ فقط وقتی کوئری واقعاً کند بود و ایندکس (درس بعد) کمک نکرد، سراغ این برو.

قیدها هم بخشی از طراحی‌اند: هر قانونی که در CREATE TABLE بنویسی (NOT NULL، UNIQUE، CHECK، REFERENCES) برای همه‌ی برنامه‌ها و اسکریپت‌ها و حتی دستورهای دستی تو اجرا می‌شود. قانونی که فقط در کد برنامه است، با اولین اسکریپت دستی یا برنامه‌ی دوم دور زده می‌شود.

جدول‌های مرجع

`ALTER TABLE`	کار
`ADD COLUMN c TEXT`	ستون جدید
`DROP COLUMN c`	حذف ستون (با داده‌اش)
`RENAME COLUMN a TO b`	تغییر اسم ستون
`ALTER COLUMN c SET NOT NULL`	اجباری کردن
`ALTER COLUMN c SET DEFAULT 0`	مقدار پیش‌فرض
`ADD CONSTRAINT n UNIQUE (c)`	قید یکتا
`ADD CONSTRAINT n CHECK (c > 0)`	قید شرطی
`DROP CONSTRAINT n`	حذف قید
`RENAME TO t2`	تغییر اسم جدول

علامت طراحی بد	مشکل	درمان
چند مقدار با کاما در یک خانه	شمارش و جست‌وجو ناممکن	جدول جدا، یک سطر برای هر مقدار
ستون‌های `phone1`، `phone2`، `phone3`	چهارمی جا ندارد	جدول `phones`
اسم مشتری در جدول سفارش	ناهماهنگی	`customer_id`
ستون محاسبه‌شدنی (`total`)	ممکن است با اقلام نخواند	با کوئری حساب کن

اشتباهات رایج

۱) طراحی بر اساس اولین گزارش

جدول را شکل اولین گزارشی که مدیر خواسته نساز. گزارش‌ها عوض می‌شوند؛ موجودیت‌ها (مشتری، سفارش، محصول) نه. راه‌حل: جدول‌ها را بر اساس چیزها بساز و گزارش‌ها را با JOIN و GROUP BY.

۲) `float` برای پول

0.30000000000000004 در فاکتور. راه‌حل: INTEGER یا NUMERIC.

۳) کلید خارجی نگذاشتن «چون برنامه چک می‌کند»

بعد از اولین اسکریپت دستی یا باگ، سطرهای یتیم داری. راه‌حل: همیشه REFERENCES.

۴) `CASCADE` روی همه‌چیز

حذف یک مشتری کل تاریخچه‌ی مالی‌اش را پاک می‌کند. راه‌حل: CASCADE فقط برای فرزندهایی که بدون والد معنی ندارند (اقلام، برچسب‌ها)؛ برای بقیه پیش‌فرض یا «حذف نرم» (ستون deleted_at به‌جای DELETE).

۵) `ALTER TABLE` روی سرور واقعی بدون فکر

اضافه کردن قید UNIQUE یا NOT NULL روی جدول بزرگ کل جدول را می‌خواند و ممکن است مدتی قفلش کند، و اگر یک سطر شرط را نداشته باشد، کل دستور رد می‌شود. راه‌حل: اول با SELECT داده‌ی ناسازگار را پیدا و درست کن، روی کپی امتحان کن، در ساعت کم‌ترافیک اجرا کن.

تمرین

برچسب‌های مثال ۴ را آخر کار پاک کن تا shop.sql دوباره بی‌مشکل اجرا شود (products نباید جدول وابسته‌ی دیگری داشته باشد):

DROP TABLE product_tags, tags;

تمرینآسان

این جدول چه مشکلی دارد؟ آن را درست طراحی کن (فقط CREATE TABLE).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    courses TEXT,          -- 'لینوکس، گیت، پایتون'
    teacher_phone TEXT
);

دیدن جواب

سه مشکل: courses چند مقدار در یک خانه دارد (قانون ۱)، teacher_phone درباره‌ی معلم است نه دانشجو (قانون ۳؛ و هر دانشجو فقط یک معلم ندارد)، و name می‌تواند خالی باشد. رابطه‌ی دانشجو و دوره چندبه‌چند است:

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    phone TEXT
);
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL UNIQUE,
    teacher_id INTEGER NOT NULL REFERENCES teachers (id)
);
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id INTEGER NOT NULL REFERENCES courses (id),
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

enrolled_on نمونه‌ی اطلاعاتی است که مال رابطه است.

تمرینمتوسط

به جدول products ستون sku (کد کالا، متن) اضافه کن، برای سه محصول اول مقدار بگذار، و قید یکتا بگذار. بعد ثابت کن دو محصول نمی‌توانند یک sku داشته باشند، ولی محصول‌های بی‌کد مشکلی ندارند.

دیدن جواب

ALTER TABLE products ADD COLUMN sku TEXT;
UPDATE products SET sku = 'BK-' || id WHERE id <= 3;
ALTER TABLE products ADD CONSTRAINT products_sku_key UNIQUE (sku);
SELECT id, name, sku FROM products WHERE id <= 4 ORDER BY id;
UPDATE products SET sku = 'BK-1' WHERE id = 4;
SELECT count(*) FROM products WHERE sku IS NULL;

ALTER TABLE
UPDATE 3
ALTER TABLE
 id |    name     | sku
----+-------------+------
  1 | کتاب پایتون | BK-1
  2 | کتاب لینوکس | BK-2
  3 | کتاب داکر   | BK-3
  4 | ماوس بی‌سیم  |
(4 rows)

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(BK-1) already exists.
 count
-------
     9
(1 row)

|| دو متن را به هم می‌چسباند. ۹ محصول NULL دارند و UNIQUE مشکلی با آن‌ها ندارد. (جدول با اجرای بعدی shop.sql به حالت اول برمی‌گردد.)

تمرینسخت

برای یک وبلاگ کوچک، در یک دیتابیس جدا (createdb blog) طراحی کامل بنویس: کاربرها (ایمیل یکتا)، پست‌ها (هر پست یک نویسنده، عنوان، متن، وضعیت پیش‌نویس یا منتشرشده، زمان انتشار)، نظرها (هر نظر روی یک پست، از یک کاربر) و برچسب‌ها (چندبه‌چند). تصمیم بگیر با حذف پست و حذف کاربر چه شود. بعد کمی داده بریز و «تعداد نظر هر پست منتشرشده، شامل پست‌های بی‌نظر» را بگیر.

دیدن جواب

createdb blog
psql blog

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL
);
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES users (id),
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published')),
    published_at TIMESTAMPTZ,
    CHECK (status = 'draft' OR published_at IS NOT NULL)
);
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users (id) ON DELETE SET NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE tags (id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

INSERT INTO users (email, name) VALUES ('ali@example.com', 'علی'), ('sara@example.com', 'سارا');
INSERT INTO posts (author_id, title, body, status, published_at) VALUES
    (1, 'شروع لینوکس', '...', 'published', '2026-10-01 10:00+03:30'),
    (1, 'گیت برای همه', '...', 'published', '2026-10-15 09:00+03:30'),
    (2, 'پیش‌نویس SQL', '...', 'draft', NULL);
INSERT INTO comments (post_id, user_id, body) VALUES (1, 2, 'عالی بود'), (1, 1, 'ممنون');

SELECT p.title, count(c.id) AS comments
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.status = 'published'
GROUP BY p.id, p.title
ORDER BY p.published_at;

    title     | comments
--------------+----------
 شروع لینوکس  |        2
 گیت برای همه |        0
(2 rows)

تصمیم‌ها: حذف پست، نظرها و برچسب‌هایش را می‌برد (CASCADE)؛ حذف کاربر نویسنده رد می‌شود (پست‌ها نباید بی‌نویسنده شوند)، ولی نظرهای یک کاربر حذف‌شده می‌مانند و فقط نویسنده‌شان خالی می‌شود (SET NULL، پس user_id نباید NOT NULL باشد). قید CHECK آخر posts یک قانون بین دو ستون است: پست منتشرشده باید زمان انتشار داشته باشد.

آزمونک

هدف اصلی نرمال‌سازی چیست؟
ستون tags با مقدار «هدیه، پرفروش» کدام قانون را می‌شکند؟
رابطه‌ی چندبه‌چند چطور پیاده می‌شود؟
برای پول کدام نوع نامناسب است؟
ON DELETE CASCADE روی order_items.order_id یعنی…
چرا order_items.unit_price تکرار قیمت محصول «اشکال» نیست؟

جمع‌بندی

جدول تخت شبیه اکسل به ناهماهنگی می‌رسد؛ قانون طلایی: هر واقعیت یک جا.
سه سؤال: هر خانه یک مقدار؟ هر ستون درباره‌ی کل کلید؟ ستونی درباره‌ی ستون دیگر نیست؟
اسم‌ها = جدول‌ها؛ یک‌به‌چند با کلید خارجی در سمت چند، چندبه‌چند با جدول واسط.
نوع درست: پول INTEGER/NUMERIC، لحظه TIMESTAMPTZ، متن TEXT.
قیدها قانون کسب‌وکار را برای همه اجرا می‌کنند؛ ON DELETE را آگاهانه انتخاب کن.
ALTER TABLE برای تغییر جدول؛ روی داده‌ی واقعی اول بررسی کن.

برگه‌ی تقلب این درس

دستور	کاری که می‌کند
`REFERENCES t (id) ON DELETE CASCADE`	حذف زنجیره‌ای
`REFERENCES t (id) ON DELETE SET NULL`	خالی شدن ارجاع
`PRIMARY KEY (a_id, b_id)`	جدول واسط چندبه‌چند
`ALTER TABLE t ADD COLUMN c TEXT;`	ستون جدید
`ALTER TABLE t ADD CONSTRAINT n UNIQUE (c);`	قید یکتا
`CHECK (status IN ('a', 'b'))`	محدود کردن مقدارها
`NUMERIC(12,2) / INTEGER`	پول
`TIMESTAMPTZ`	زمان با منطقه

مسئله: جدولی که کم‌کم دروغ می‌گوید

تشبیه: دفترچه‌ی تلفن و یادداشت‌ها

مثال‌های عملی

مثال ۱: ناهماهنگی را با چشم خودت ببین

مثال ۲: سه قانون نرمال‌سازی، به زبان ساده

مثال ۳: موجودیت‌ها و رابطه‌ها

مثال ۴: یک رابطه‌ی چندبه‌چند بساز

مثال ۵: ON DELETE، وقتی والد حذف می‌شود

مثال ۶: قید UNIQUE و ALTER TABLE

مثال ۷: نوع داده‌ی درست

مثال ۸: نام‌گذاری یکدست

پشت پرده: کی عمداً تکرار کنیم؟

جدول‌های مرجع

اشتباهات رایج

۱) طراحی بر اساس اولین گزارش

۲) float برای پول

۳) کلید خارجی نگذاشتن «چون برنامه چک می‌کند»

۴) CASCADE روی همه‌چیز

۵) ALTER TABLE روی سرور واقعی بدون فکر

تمرین

آزمونک

جمع‌بندی

مثال ۵: `ON DELETE`، وقتی والد حذف می‌شود

مثال ۶: قید `UNIQUE` و `ALTER TABLE`

۲) `float` برای پول

۴) `CASCADE` روی همه‌چیز

۵) `ALTER TABLE` روی سرور واقعی بدون فکر