پروژه‌ی پایانی (۱): طراحی دیتابیس سفارش و تولید کارخانه‌ی فرش

صورت مسئله

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

جدول‌ها

CREATE DATABASE carpet_factory CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE carpet_factory;

CREATE TABLE customers (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name  VARCHAR(120) NOT NULL,
  city       VARCHAR(60)  NOT NULL,
  mobile     CHAR(11)     NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customers_mobile (mobile)
);

CREATE TABLE designs (
  id     SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(80) NOT NULL UNIQUE,
  colors TINYINT UNSIGNED NOT NULL CHECK (colors BETWEEN 1 AND 16)
);

CREATE TABLE products (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  design_id   SMALLINT UNSIGNED NOT NULL,
  reeds       SMALLINT UNSIGNED NOT NULL,
  width_cm    SMALLINT UNSIGNED NOT NULL,
  length_cm   SMALLINT UNSIGNED NOT NULL,
  area_m2     DECIMAL(6,2) AS (width_cm * length_cm / 10000) STORED,
  price_toman DECIMAL(12,0) NOT NULL,
  UNIQUE KEY uq_product (design_id, reeds, width_cm, length_cm),
  CONSTRAINT fk_products_design FOREIGN KEY (design_id) REFERENCES designs (id),
  CONSTRAINT ck_products_reeds CHECK (reeds IN (500, 700, 1000, 1200, 1500))
);

CREATE TABLE orders (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      ENUM('draft','confirmed','in_production','ready','shipped','cancelled')
              NOT NULL DEFAULT 'draft',
  ordered_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  due_date    DATE NOT NULL,
  KEY ix_orders_status_due (status, due_date),
  KEY ix_orders_ordered_at (ordered_at),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE order_items (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id         INT UNSIGNED NOT NULL,
  product_id       INT UNSIGNED NOT NULL,
  qty              SMALLINT UNSIGNED NOT NULL CHECK (qty > 0),
  unit_price_toman DECIMAL(12,0) NOT NULL,
  UNIQUE KEY uq_item (order_id, product_id),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders (id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE looms (
  id        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code      VARCHAR(10) NOT NULL UNIQUE,
  reeds     SMALLINT UNSIGNED NOT NULL,
  hall      VARCHAR(20) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE production_jobs (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_item_id INT UNSIGNED NOT NULL,
  loom_id       SMALLINT UNSIGNED NOT NULL,
  planned_qty   SMALLINT UNSIGNED NOT NULL,
  produced_qty  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  defect_qty    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  status        ENUM('queued','weaving','paused','done') NOT NULL DEFAULT 'queued',
  started_at    DATETIME NULL,
  finished_at   DATETIME NULL,
  KEY ix_jobs_loom_status (loom_id, status),
  KEY ix_jobs_finished (finished_at),
  CONSTRAINT fk_jobs_item FOREIGN KEY (order_item_id) REFERENCES order_items (id),
  CONSTRAINT fk_jobs_loom FOREIGN KEY (loom_id) REFERENCES looms (id),
  CONSTRAINT ck_jobs_time CHECK (finished_at IS NULL OR finished_at >= started_at)
);

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

متراژ ستون محاسبه‌شده‌ی STORED است تا در گزارش‌ها جمع زده و در صورت نیاز ایندکس شود. قیمت در order_items تکرار شده، چون قیمت روز سفارش با تغییر لیست قیمت نباید عوض شود. حذف سفارش اقلامش را با CASCADE حذف می‌کند، اما اگر برای قلمی کار تولید ثبت شده باشد، کلید خارجی بدون CASCADE جلوی حذف را می‌گیرد؛ دقیقاً همان رفتاری که می‌خواهیم.

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

• CHECK روی ستونی که در کلید خارجی با عمل CASCADE یا SET NULL به کار رفته مجاز نیست؛ آن قید را باید در سطح برنامه یا تریگر گذاشت.
• MySQL برای هر کلید خارجی، اگر ایندکس مناسبی نباشد، خودش ایندکس می‌سازد؛ پس ایندکس جداگانه روی customer_id تکراری است.
• نام‌گذاری صریح قیدها (fk_... و ck_...) پیام خطا را خوانا می‌کند و حذف بعدی را بدون جست‌وجو در information_schema ممکن می‌سازد.
• ENUM برای وضعیت فشرده است، اما افزودن مقدار وسط فهرست، جدول را بازسازی می‌کند؛ مقدار جدید را همیشه در انتها اضافه کنید.