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