Q2_Inventory_ERD_v1

Tables (15)

brands

  • id — BIGINT
  • brand_name — VARCHAR(100) — NOT NULL
  • is_active — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

business_types

  • id — BIGINT
  • type_name — VARCHAR(50) — NOT NULL
  • is_consignment — BOOLEAN — NOT NULL
  • requires_settlement — BOOLEAN — NOT NULL
  • owns_stock — BOOLEAN — NOT NULL
  • description — VARCHAR(255)
  • is_active — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

cafe24_sync_logs

  • id — BIGINT
  • sync_type — ENUM('PRODUCT', 'SKU', 'FULL') — NOT NULL
  • status — ENUM('RUNNING', 'SUCCESS', 'FAILED') — NOT NULL
  • total_count — INT
  • created_count — INT
  • updated_count — INT
  • failed_count — INT
  • error_message — TEXT
  • started_at — DATETIME
  • finished_at — DATETIME

inbound_items

  • id — BIGINT
  • inbound_order_id — BIGINT — Foreign key → inbound_orders.id — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • quantity — INT — NOT NULL
  • scanned_barcode — VARCHAR(100)
  • created_at — DATETIME
  • INDEX — idx_inbound
  • INDEX — idx_inbound_sku

inbound_orders

  • id — BIGINT
  • inbound_no — VARCHAR(50) — NOT NULL
  • inbound_type — ENUM('BARCODE', 'MANUAL') — NOT NULL
  • supplier — VARCHAR(255)
  • memo — TEXT
  • reason — TEXT
  • total_quantity — INT — NOT NULL
  • status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED')
  • created_by — BIGINT — Foreign key → users.id — NOT NULL
  • confirmed_by — BIGINT — Foreign key → users.id
  • confirmed_at — DATETIME
  • created_at — DATETIME

ips

  • id — BIGINT
  • brand_id — BIGINT — Foreign key → brands.id — NOT NULL
  • ip_name — VARCHAR(100) — NOT NULL
  • is_active — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

product_categories

  • id — BIGINT
  • parent_id — BIGINT — Foreign key → product_categories.id
  • category_name — VARCHAR(100) — NOT NULL
  • sort_order — INT
  • is_active — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME

products

  • id — BIGINT
  • cafe24_product_no — VARCHAR(50) — NOT NULL
  • product_code — VARCHAR(100)
  • product_name — VARCHAR(255) — NOT NULL
  • brand_id — BIGINT — Foreign key → brands.id — NOT NULL
  • ip_id — BIGINT — Foreign key → ips.id — NOT NULL
  • business_type_id — BIGINT — Foreign key → business_types.id — NOT NULL
  • category_id — BIGINT — Foreign key → product_categories.id — NOT NULL
  • is_active — BOOLEAN
  • last_synced_at — DATETIME
  • created_at — DATETIME
  • updated_at — DATETIME
  • INDEX — idx_cafe24_no
  • INDEX — idx_product_name
  • INDEX — idx_brand_ip
  • INDEX — idx_business_category

sales_upload_items

  • id — BIGINT
  • sales_upload_id — BIGINT — Foreign key → sales_uploads.id — NOT NULL
  • row_number — INT — NOT NULL
  • raw_barcode — VARCHAR(100)
  • raw_product_name — VARCHAR(255)
  • sku_id — BIGINT — Foreign key → skus.id
  • quantity — INT — NOT NULL
  • sale_date — DATETIME
  • match_status — ENUM('MATCHED', 'UNMATCHED', 'SKIPPED') — NOT NULL
  • created_at — DATETIME
  • INDEX — idx_upload
  • INDEX — idx_match
  • INDEX — idx_sales_sku

sales_uploads

  • id — BIGINT
  • upload_no — VARCHAR(50) — NOT NULL
  • file_name — VARCHAR(255) — NOT NULL
  • file_path — VARCHAR(500)
  • sale_date_from — DATE
  • sale_date_to — DATE
  • total_rows — INT
  • matched_rows — INT
  • unmatched_rows — INT
  • total_deducted_qty — INT
  • status — ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED')
  • error_message — TEXT
  • uploaded_by — BIGINT — Foreign key → users.id — NOT NULL
  • uploaded_at — DATETIME
  • completed_at — DATETIME

skus

  • id — BIGINT
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • cafe24_variant_code — VARCHAR(100) — NOT NULL
  • sku_code — VARCHAR(100) — NOT NULL
  • barcode — VARCHAR(100)
  • option_name — VARCHAR(255)
  • current_stock — INT — NOT NULL
  • safety_stock — INT
  • is_active — BOOLEAN
  • last_synced_at — DATETIME
  • created_at — DATETIME
  • updated_at — DATETIME
  • INDEX — idx_barcode
  • INDEX — idx_sku_code
  • INDEX — idx_product_id

stock_movements

  • id — BIGINT
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • movement_type — ENUM( 'INBOUND', 'SALE', 'ADJUSTMENT_PLUS', 'ADJUSTMENT_MINUS', 'RETURN', 'INIT' ) — NOT NULL
  • quantity — INT — NOT NULL
  • stock_before — INT — NOT NULL
  • stock_after — INT — NOT NULL
  • reason — TEXT
  • reference_type — VARCHAR(50)
  • reference_id — BIGINT
  • created_by — BIGINT — Foreign key → users.id — NOT NULL
  • created_at — DATETIME
  • INDEX — idx_sku_created
  • INDEX — idx_type_created
  • INDEX — idx_reference

stocktake_items

  • id — BIGINT
  • stocktake_id — BIGINT — Foreign key → stocktakes.id — NOT NULL
  • sku_id — BIGINT — Foreign key → skus.id — NOT NULL
  • system_qty — INT — NOT NULL
  • actual_qty — INT — NOT NULL
  • diff_qty — INT — NOT NULL
  • item_reason — TEXT
  • created_at — DATETIME
  • INDEX — idx_stocktake
  • INDEX — idx_stocktake_sku

stocktakes

  • id — BIGINT
  • stocktake_no — VARCHAR(50) — NOT NULL
  • title — VARCHAR(255)
  • reason — TEXT — NOT NULL
  • status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED')
  • created_by — BIGINT — Foreign key → users.id — NOT NULL
  • confirmed_by — BIGINT — Foreign key → users.id
  • confirmed_at — DATETIME
  • created_at — DATETIME

users

  • id — BIGINT
  • username — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • role — ENUM('ADMIN', 'STAFF') — NOT NULL
  • is_active — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME