مدل‌سازی کالای چندبعدی در دیتابیس: تجربه ماتریس نمره عدسی

اندازه مسئله

یک مدل عدسی را در نظر بگیرید. نمره کروی (SPH) مثلاً از ۱۰- تا ۶+ با گام ۰٫۲۵ است، یعنی ۶۵ مقدار. نمره استوانه‌ای (CYL) از صفر تا ۴- با همان گام، یعنی ۱۷ مقدار. همین یک مدل بیش از هزار ترکیب دارد. حالا این را در تعداد برندها، ضریب شکست‌ها و پوشش‌ها ضرب کنید.

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

مدل: کالای پایه + واریانت با کلید ترکیبی

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

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

CREATE TABLE lens_products (
  id          BIGSERIAL PRIMARY KEY,
  brand_id    BIGINT NOT NULL,
  name        TEXT NOT NULL,
  lens_index  NUMERIC(3, 2) NOT NULL,   -- ضریب شکست، مثل 1.56
  coating     TEXT NOT NULL
);

CREATE TABLE lens_variants (
  id          BIGSERIAL PRIMARY KEY,
  product_id  BIGINT NOT NULL REFERENCES lens_products(id),
  sph_q       SMALLINT NOT NULL,        -- نمره کروی × ۴ (‎-2.75 → ‎-11)
  cyl_q       SMALLINT NOT NULL CHECK (cyl_q <= 0),
  UNIQUE (product_id, sph_q, cyl_q)
);

چرا نمره را عدد صحیح ذخیره کردم

نمره‌ها همیشه مضرب ۰٫۲۵ هستند. به‌جای ذخیره اعشاری، نمره را ضرب در ۴ و به‌صورت عدد صحیح (smallint) ذخیره می‌کنم: نمره ۲٫۷۵- می‌شود ۱۱-. سه فایده دارد.

اول، هیچ‌وقت مشکل مقایسه اعشاری پیش نمی‌آید؛ ۱۱- همیشه دقیقاً برابر ۱۱- است. دوم، مقدار نامعتبر (مثلاً ۲٫۷۰-) اصلاً قابل ذخیره نیست، چون در ورودی به نزدیک‌ترین گام اعتبارسنجی می‌شود و نمره‌ای که ضربش در ۴ صحیح نشود رد می‌شود. سوم، ایندکس روی اعداد صحیح کوچک فشرده و سریع است. تبدیل به نمایش (۲٫۷۵-) فقط در لایه نمایش انجام می‌شود.

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

واریانت را فقط وقتی لازم است بسازید

لازم نیست برای هر کالا همه هزار و چند خانه ماتریس را از قبل بسازید. مدل «پراکنده» (sparse) بهتر است: واریانت اولین باری ساخته می‌شود که برایش خرید، فروش یا قیمت خاصی ثبت شود. خانه‌هایی که هیچ‌وقت موجودی نداشته‌اند ردیفی هم ندارند.

این کار دیتابیس را کوچک نگه می‌دارد و گزارش‌ها را معنادار می‌کند. موقع ثبت فاکتور، سیستم واریانت را با «INSERT … ON CONFLICT DO NOTHING» پیدا یا ایجاد می‌کند، که هم‌زمانی را هم درست مدیریت می‌کند.

قیمت‌گذاری بازه‌ای به‌جای قیمت برای هر خانه

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

CREATE TABLE lens_price_rules (
  id          BIGSERIAL PRIMARY KEY,
  product_id  BIGINT NOT NULL REFERENCES lens_products(id),
  sph_min_q   SMALLINT NOT NULL,
  sph_max_q   SMALLINT NOT NULL,
  cyl_min_q   SMALLINT NOT NULL,   -- منفی‌تر
  cyl_max_q   SMALLINT NOT NULL,
  priority    SMALLINT NOT NULL DEFAULT 0,
  price       NUMERIC(20, 0) NOT NULL
);

-- قیمت یک نمره: قاعده با بالاترین اولویت که بازه‌اش شامل نمره است
SELECT price
FROM lens_price_rules
WHERE product_id = $1
  AND $2 BETWEEN sph_min_q AND sph_max_q
  AND $3 BETWEEN cyl_min_q AND cyl_max_q
ORDER BY priority DESC
LIMIT 1;

گزارش ماتریسی و رابط ورود داده

کاربر انبار نمی‌خواهد فهرست ردیفی ببیند؛ می‌خواهد همان جدولی را ببیند که روی کاغذ دارد: نمره‌های کروی در ردیف‌ها، استوانه‌ای در ستون‌ها و موجودی در هر خانه. این گزارش با یک GROUP BY ساده روی واریانت‌ها و جمع موجودی لایه‌ها ساخته می‌شود و تبدیل به جدول در لایه نمایش انجام می‌شود.

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

دامی که باید از آن دوری کرد: EAV

وسوسه رایج در این مسئله، الگوی EAV (موجودیت-ویژگی-مقدار) است: یک جدول عمومی که هر ویژگی هر کالا را به‌صورت یک ردیف نگه می‌دارد. انعطافش زیاد است، اما کوئری‌ها پیچیده و کند می‌شوند، قید یکتایی و اعتبارسنجی تقریباً غیرممکن است و گزارش ساده‌ای مثل «موجودی این نمره» به چند join تبدیل می‌شود.

قاعده‌ای که دنبال می‌کنم: بعدهایی که موجودی و قیمت به آن‌ها وابسته است، ستون واقعی با نوع مشخص می‌شوند. ویژگی‌هایی که فقط توصیفی‌اند (کشور سازنده، توضیحات) می‌توانند در یک ستون JSONB کنار کالا بمانند. این ترکیب هم سخت‌گیری لازم را دارد و هم انعطاف کافی.