نرمال‌سازی، jsonb آگاهانه و schemaها برای جداسازی

کجا ستون، کجا jsonb، کجا جدول جدا

نرمال‌سازی در یک نگاه

هدف نرمال‌سازی این است که هر واقعیت فقط یک جا ثبت شود. اگر نام شهر مشتری در جدول سفارش هم کپی شود، روزی که مشتری آدرس عوض کند، سفارش‌های قدیمی و جدید دو شهر مختلف نشان می‌دهند و نمی‌دانید کدام درست است.

استثنای آگاهانه: قیمت در لحظه‌ی فروش. unit_price در ردیف سفارش باید کپی شود، چون قیمت نقشه فردا عوض می‌شود ولی فاکتور دیروز نباید عوض شود. این تکرار نیست، ثبت یک واقعیت تاریخی است.

jsonb: برای چه چیزی، نه برای همه‌چیز

jsonb جای مناسبی است برای ویژگی‌هایی که واقعاً متغیرند: مشخصات فنی که برای فرش ماشینی، دستباف و تابلوفرش فرق می‌کند؛ پاسخ خام درگاه پرداخت؛ تنظیمات کاربر. اما ستونی که در WHERE و JOIN و گزارش مدام استفاده می‌شود، یا کلید خارجی است، باید ستون واقعی باشد.

CREATE TABLE products (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind      text NOT NULL CHECK (kind IN ('machine', 'handmade', 'tableau')),
  title     text NOT NULL,
  price_rial bigint NOT NULL,
  specs     jsonb NOT NULL DEFAULT '{}',
  CONSTRAINT specs_is_object CHECK (jsonb_typeof(specs) = 'object'),
  CONSTRAINT machine_specs CHECK (kind <> 'machine' OR specs ?& ARRAY['reed', 'density'])
);
INSERT INTO products (kind, title, price_rial, specs) VALUES
 ('machine', 'افشان لاکی', 185000000, '{"reed": 1200, "density": 3600, "yarn": "اکریلیک"}'),
 ('handmade', 'کاشان کرک', 2400000000, '{"knots_per_cm2": 64, "material": "کرک و ابریشم"}');

schemaها برای جداسازی

CREATE SCHEMA factory;   -- جدول‌های اصلی
CREATE SCHEMA audit;     -- تاریخچه‌ی تغییرات
CREATE SCHEMA report;    -- ویوها و materialized viewهای گزارش
CREATE SCHEMA staging;   -- داده‌ی ورودی خام اکسل پیش از پاک‌سازی
GRANT USAGE ON SCHEMA report TO bi_reader;

schema واحد مجوز هم هست: به تیم گزارش فقط USAGE روی report بدهید و هیچ دسترسی به factory نداشته باشند.

نکته‌هایی که کمتر کسی می‌داند

• هر UPDATE روی یک کلید jsonb کل سند را بازنویسی می‌کند (و اگر بزرگ باشد، کل مقدار TOAST را)؛ سند چندصد کیلوبایتی که مدام یک شمارنده‌اش عوض می‌شود، WAL و bloat عظیم می‌سازد. شمارنده را ستون کنید.
• برنامه‌ریز برای عبارت‌های داخل jsonb آمار ندارد و معمولاً تعداد ردیف را اشتباه تخمین می‌زند؛ اگر یک کلید jsonb در فیلترها زیاد می‌آید، آن را ستون generated کنید یا روی عبارتش ایندکس بسازید تا آمار جمع شود.
• الگوی EAV (جدول entity، attribute، value) تقریباً همیشه از jsonb بدتر است: هر کوئری ده JOIN و هیچ نوع داده‌ای.
• مدل «یک schema برای هر مشتری» (multi-tenant) با چند ده مشتری خوب است، اما با هزاران schema کاتالوگ سیستمی حجیم، pg_dump کند و هر migration هزار برابر می‌شود؛ برای تعداد بالا ستون tenant_id و RLS (فصل ۷) بهتر است.
• COMMENT ON COLUMN products.specs IS '...' مستندات را کنار خود داده نگه می‌دارد و در \d+ و pgAdmin و DBeaver دیده می‌شود.