طراحی نمونه: دیتابیس تولید و سفارش کارخانه‌ی فرش

از نیازمندی تا DDL

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

CREATE SCHEMA factory;
SET search_path = factory, public;
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE DOMAIN mobile_ir AS text CHECK (VALUE ~ '^09[0-9]{9}$');
CREATE TYPE order_status AS ENUM ('draft','confirmed','weaving','finishing','delivered','cancelled');

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name  text NOT NULL,
  city       text NOT NULL,
  mobile     mobile_ir,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE designs (
  code    text PRIMARY KEY,                 -- مثل AF-1203
  title   text NOT NULL,                    -- افشان، هریس، وکیلی
  reed    smallint NOT NULL CHECK (reed IN (500, 700, 1000, 1200, 1500)),
  density smallint NOT NULL,
  colors  smallint NOT NULL CHECK (colors BETWEEN 1 AND 16),
  tags    text[] NOT NULL DEFAULT '{}'
);

CREATE TABLE looms (
  id        int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code      text NOT NULL UNIQUE,
  reed      smallint NOT NULL,
  hall      text NOT NULL,
  is_active boolean NOT NULL DEFAULT true
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers,
  status      order_status NOT NULL DEFAULT 'draft',
  due_date    date,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id        bigint NOT NULL REFERENCES orders ON DELETE CASCADE,
  design_code     text NOT NULL REFERENCES designs,
  width_cm        int NOT NULL CHECK (width_cm BETWEEN 50 AND 600),
  length_cm       int NOT NULL CHECK (length_cm BETWEEN 50 AND 1200),
  qty             int NOT NULL CHECK (qty > 0),
  unit_price_rial bigint NOT NULL CHECK (unit_price_rial >= 0),
  line_total_rial bigint GENERATED ALWAYS AS (qty * unit_price_rial) STORED
);

CREATE TABLE production_runs (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_item_id bigint NOT NULL REFERENCES order_items,
  loom_id       int NOT NULL REFERENCES looms,
  during        tstzrange NOT NULL,
  produced_qty  int NOT NULL DEFAULT 0,
  EXCLUDE USING gist (loom_id WITH =, during WITH &&)
);

CREATE TABLE qc_inspections (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  run_id       bigint NOT NULL REFERENCES production_runs,
  inspected_at timestamptz NOT NULL DEFAULT now(),
  grade        smallint NOT NULL CHECK (grade BETWEEN 1 AND 3),
  defects      jsonb NOT NULL DEFAULT '{}'    -- {"رج_کشی": 2, "پرز_دهی": 1}
);

CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (status, created_at);
CREATE INDEX ON order_items (order_id);
CREATE INDEX ON order_items (design_code);
CREATE INDEX ON production_runs (order_item_id);
CREATE INDEX ON qc_inspections (run_id);

تصمیم‌های طراحی

• کلید نقشه طبیعی (code) است چون در کارخانه همه با همین کد صحبت می‌کنند و ثابت است؛ بقیه کلید جایگزین bigint دارند.
• قیمت در order_items کپی می‌شود (واقعیت تاریخی) و جمع ردیف generated است تا هیچ‌وقت با qty و قیمت ناهمخوان نشود.
• عیوب کنترل کیفیت متغیرند، پس jsonb؛ اما درجه (grade) که در گزارش‌ها فیلتر می‌شود ستون واقعی است.

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

• REFERENCES customers بدون نام ستون، خودکار به کلید اصلی آن جدول اشاره می‌کند.
• کل این اسکریپت را داخل BEGIN; ... COMMIT; اجرا کنید؛ اگر خط چهلم خطا داشت، نیمه‌کاره نمی‌ماند (DDL تراکنشی).
• ایندکس (status, created_at) برای کوئری‌های «سفارش‌های باز این ماه» است؛ ستون تساوی (status) اول و ستون بازه (created_at) دوم بیاید.
• برای ثابت ماندن نام قیدها در migrationها، به آن‌ها نام صریح بدهید؛ نام خودکار (مثل orders_customer_id_fkey) با تغییر نام ستون عوض نمی‌شود و بعدها گیج‌کننده است.