WMS PROJECT-v1.0

Tables (23)

Category

  • category_id — BIGINT — Primary key — NOT NULL
  • category_code — CHAR(3) — NOT NULL
  • category_name — VARCHAR(50) — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME

Disposal

  • disposal_id — BIGINT — Primary key — NOT NULL
  • disposal_date — DATETIME — NOT NULL
  • reason — VARCHAR(50) — NOT NULL
  • quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • inventory_id — BIGINT — Foreign key → Inventory.inventory_id — NOT NULL
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL

Export

  • export_id — BIGINT — Primary key — NOT NULL
  • export_status — VARCHAR(20) — NOT NULL
  • export_date — DATETIME — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • order_id — BIGINT — Foreign key → Order.order_id — NOT NULL

Export_Detail

  • export_detail_id — BIGINT — Primary key — NOT NULL
  • quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • export_id — BIGINT — Foreign key → Export.export_id — NOT NULL
  • inventory_id — BIGINT — Foreign key → Inventory.inventory_id — NOT NULL

Inbound

  • inbound_id — BIGINT — Primary key — NOT NULL
  • inbound_code — VARCHAR(100) — NOT NULL
  • approval_status — VARCHAR(20) — NOT NULL
  • inbound_date — DATETIME — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • warehouse_id — BIGINT — Foreign key → Warehouse.warehouse_id — NOT NULL

Inbound_Detail

  • inbound_detail_id — BIGINT — Primary key — NOT NULL
  • quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • inbound_id — BIGINT — Foreign key → Inbound.inbound_id — NOT NULL
  • product_id — BIGINT — Foreign key → Product.product_id — NOT NULL

Inventory

  • inventory_id — BIGINT — Primary key — NOT NULL
  • product_code — VARCHAR(100) — NOT NULL
  • alloc_status — VARCHAR(20) — NOT NULL
  • quality_status — VARCHAR(20) — NOT NULL
  • loc_status — VARCHAR(20) — NOT NULL
  • quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • product_id — BIGINT — Foreign key → Product.product_id — NOT NULL
  • warehouse_id — BIGINT — Foreign key → Warehouse.warehouse_id — NOT NULL
  • lot_id — BIGINT — Foreign key → Lot.lot_id — NOT NULL — 로트 ID

Inventory_History

  • history_id — BIGINT — Primary key — NOT NULL
  • prev_status_set — VARCHAR(100)
  • curr_status_set — VARCHAR(100) — NOT NULL
  • change_reason — VARCHAR(255)
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • inventory_id — BIGINT — Foreign key → Inventory.inventory_id — NOT NULL
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL

Lot

  • lot_id — BIGINT — Primary key — NOT NULL — 로트 ID
  • lot_number — VARCHAR(50) — NOT NULL — 로트 번호
  • manufacturing_date — DATETIME — NOT NULL — 제조일자
  • expiration_date — DATETIME — NOT NULL — 유통기한
  • status — VARCHAR(20) — NOT NULL — 로트 상태 (정상, 출고 금지 등)
  • created_by — BIGINT — NOT NULL — 최초 등록자 ID
  • created_at — DATETIME — NOT NULL — 최초 등록 일시
  • updated_by — BIGINT — 최종 수정자 ID
  • updated_at — DATETIME — 최종 수정 일시
  • product_id — BIGINT — Foreign key → Product.product_id — NOT NULL

Member

  • member_id — BIGINT — Primary key — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password — VARCHAR(50) — NOT NULL
  • role — VARCHAR(50) — NOT NULL
  • member_name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(50) — NOT NULL
  • phone_number — VARCHAR(50) — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME

Member_Store

  • ms_id — BIGINT — Primary key — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL
  • store_id — BIGINT — Foreign key → Store.store_id — NOT NULL

Member_Warehouse

  • mw_id — BIGINT — Primary key — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL
  • warehouse_id — BIGINT — Foreign key → Warehouse.warehouse_id — NOT NULL

Order

  • order_id — BIGINT — Primary key — NOT NULL
  • order_status — VARCHAR(20) — NOT NULL
  • order_date — DATETIME — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • store_id — BIGINT — Foreign key → Store.store_id — NOT NULL
  • assigned_wh_id (warehouse_id) — BIGINT — Foreign key → Warehouse.warehouse_id — NOT NULL

Order_Detail

  • order_detail_id — BIGINT — Primary key — NOT NULL
  • quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • order_id — BIGINT — Foreign key → Order.order_id — NOT NULL
  • product_id — BIGINT — Foreign key → Product.product_id — NOT NULL

Product

  • product_id — BIGINT — Primary key — NOT NULL
  • sku_code — VARCHAR(50) — NOT NULL
  • brand_name — VARCHAR(50) — NOT NULL
  • capacity — VARCHAR(20) — NOT NULL
  • serial_number — CHAR(4) — NOT NULL
  • product_name — VARCHAR(100) — NOT NULL
  • product_price — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • type_id — BIGINT — Foreign key → ProductType.type_id — NOT NULL
  • category_id — BIGINT — Foreign key → Category.category_id — NOT NULL

ProductType

  • type_id — BIGINT — Primary key — NOT NULL
  • type_code — CHAR(3) — NOT NULL
  • type_name — VARCHAR(50) — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME

Recall

  • recall_id — BIGINT — Primary key — NOT NULL
  • reason — TEXT — NOT NULL
  • recall_start_date — DATETIME — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL
  • lot_id — BIGINT — Foreign key → Lot.lot_id — NOT NULL — 로트 ID

Return

  • return_id — BIGINT — Primary key — NOT NULL
  • reason — TEXT — NOT NULL
  • inspection_status — VARCHAR(20) — NOT NULL
  • arrival_date — DATETIME
  • is_notified — BOOLEAN — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • order_id — BIGINT — Foreign key → Order.order_id — NOT NULL
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL

Return_Detail

  • return_detail_id — BIGINT — Primary key — NOT NULL
  • return_quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • return_id — BIGINT — Foreign key → Return.return_id — NOT NULL
  • order_detail_id — BIGINT — Foreign key → Order_Detail.order_detail_id — NOT NULL

Stock_Transfer

  • st_id — BIGINT — Primary key — NOT NULL
  • request_date — DATETIME — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • from_warehouse_id — BIGINT — Foreign key → Warehouse.warehouse_id — NOT NULL
  • to_warehouse_id — BIGINT — Foreign key → Warehouse.warehouse_id — NOT NULL

Stock_Transfer_Detail

  • std_id — BIGINT — Primary key — NOT NULL
  • quantity — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME
  • st_id — BIGINT — Foreign key → Stock_Transfer.st_id — NOT NULL
  • inventory_id — BIGINT — Foreign key → Inventory.inventory_id — NOT NULL

Store

  • store_id — BIGINT — Primary key — NOT NULL
  • store_name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(100) — NOT NULL
  • contract_status — VARCHAR(20) — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME

Warehouse

  • warehouse_id — BIGINT — Primary key — NOT NULL
  • warehouse_name — VARCHAR(100) — NOT NULL
  • location_info — VARCHAR(255) — NOT NULL
  • operation_status — VARCHAR(20) — NOT NULL
  • target_temp — VARCHAR(20) — NOT NULL
  • Field (capacity) — INT — NOT NULL
  • created_by — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_by — BIGINT
  • updated_at — DATETIME