Q2_Inventory_ERD_v5
Tables (21)
admin_users
- id — BIGINT — Primary key — NOT NULL
- 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 — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
brands
- id — BIGINT — Primary key — NOT NULL
- brand_name — VARCHAR(100) — NOT NULL
- is_active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
business_types
- id — BIGINT — Primary key — NOT NULL
- 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 — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
cafe24_sync_logs
- id — BIGINT — Primary key — NOT NULL
- 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 — NOT NULL
- finished_at — DATETIME
inbound_items
- id — BIGINT — Primary key — NOT NULL
- inbound_order_id — BIGINT — NOT NULL
- sku_id — BIGINT — NOT NULL
- quantity — INT — NOT NULL
- scanned_barcode — VARCHAR(100)
- created_at — DATETIME — NOT NULL
inbound_orders
- id — BIGINT — Primary key — NOT NULL
- inbound_no — VARCHAR(50) — NOT NULL
- inbound_type — ENUM('BARCODE', 'MANUAL', 'EXCEL') — NOT NULL
- location_id — BIGINT — NOT NULL
- supplier — VARCHAR(255)
- memo — TEXT
- reason — TEXT
- expected_arrival_date — DATE
- actual_arrival_date — DATE
- total_quantity — INT — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') — NOT NULL
- created_by — BIGINT — NOT NULL
- confirmed_by — BIGINT
- confirmed_at — DATETIME
- created_at — DATETIME — NOT NULL
inventories
- id — BIGINT — Primary key — NOT NULL
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
- quantity — INT — NOT NULL
- safety_stock — INT
- last_movement_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
inventory_locations
- id — BIGINT — Primary key — NOT NULL
- location_name — VARCHAR(100) — NOT NULL
- location_type — ENUM('STORE', 'WAREHOUSE', 'POPUP', 'ONLINE', 'ETC') — NOT NULL
- is_active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
ip_brands
- id — BIGINT — Primary key — NOT NULL
- ip_id — BIGINT — Foreign key → ips.id — NOT NULL
- brand_id — BIGINT — Foreign key → brands.id — NOT NULL
- is_active — BOOLEAN — NOT NULL — 매핑 활성 여부 (계약 종료 시 비활성)
- contract_started_at — DATE — 계약/협업 시작일
- contract_ended_at — DATE — 계약/협업 종료일 (NULL = 진행중)
- note — VARCHAR(500) — 매핑 메모 (예: 한정판 1차 협업)
- created_by — BIGINT — Foreign key → admin_users.id
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
ips
- id — BIGINT — Primary key — NOT NULL
- ip_name — VARCHAR(100) — NOT NULL
- description — VARCHAR(500) — IP 설명/라이선스 정보
- is_active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
product_categories
- id — BIGINT — Primary key — NOT NULL
- parent_id — BIGINT
- category_name — VARCHAR(100) — NOT NULL
- sort_order — INT
- is_active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
product_images
- id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → products.id — NOT NULL
- image_url — VARCHAR(500) — NOT NULL
- thumbnail_url — VARCHAR(500)
- alt_text — VARCHAR(255)
- sort_order — INT — NOT NULL
- is_main — BOOLEAN — NOT NULL
- file_size — INT
- mime_type — VARCHAR(50)
- uploaded_by — BIGINT — Foreign key → admin_users.id — NOT NULL
- created_at — DATETIME — NOT NULL
products
- id — BIGINT — Primary key — NOT NULL
- cafe24_product_no — VARCHAR(50)
- 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
- main_image_url — VARCHAR(500) — 대표 이미지 URL (product_images의 is_main=TRUE 동기화)
- is_active — BOOLEAN — NOT NULL
- last_synced_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
sales_upload_items
- id — BIGINT — Primary key — NOT NULL
- sales_upload_id — BIGINT — NOT NULL
- row_number — INT — NOT NULL
- raw_barcode — VARCHAR(100)
- raw_product_name — VARCHAR(255)
- sku_id — BIGINT
- quantity — INT — NOT NULL
- sale_date — DATETIME
- match_status — ENUM('MATCHED', 'UNMATCHED', 'SKIPPED') — NOT NULL
- created_at — DATETIME — NOT NULL
sales_uploads
- id — BIGINT — Primary key — NOT NULL
- upload_no — VARCHAR(50) — NOT NULL
- location_id — BIGINT — 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') — NOT NULL
- error_message — TEXT
- uploaded_by — BIGINT — NOT NULL
- uploaded_at — DATETIME — NOT NULL
- completed_at — DATETIME
skus
- id — BIGINT — Primary key — NOT NULL
- product_id — BIGINT — Foreign key → products.id — NOT NULL
- cafe24_variant_code — VARCHAR(100)
- sku_code — VARCHAR(100) — NOT NULL
- barcode — VARCHAR(100)
- option_name — VARCHAR(255)
- cost_price — DECIMAL(12, 2)
- image_url — VARCHAR(500) — 옵션별 이미지 (NULL이면 product의 main_image_url 사용)
- is_active — BOOLEAN — NOT NULL
- last_synced_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
stock_movements
- id — BIGINT — Primary key — NOT NULL
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
- movement_type — ENUM( 'INIT', 'INBOUND', 'SALE', 'ADJUSTMENT_PLUS', 'ADJUSTMENT_MINUS', 'RETURN', 'TRANSFER_IN', 'TRANSFER_OUT' ) — 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 → admin_users.id — NOT NULL
- created_at — DATETIME — NOT NULL
stock_transfer_items
- id — BIGINT — Primary key — NOT NULL
- transfer_id — BIGINT — Foreign key → stock_transfers.id — NOT NULL
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- quantity — INT — NOT NULL
- stock_before_from — INT
- stock_before_to — INT
- created_at — DATETIME — NOT NULL
stock_transfers
- id — BIGINT — Primary key — NOT NULL
- transfer_no — VARCHAR(50) — NOT NULL
- from_location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
- to_location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
- reason — VARCHAR(255) — NOT NULL
- memo — TEXT
- expected_date — DATE
- shipping_method — VARCHAR(50)
- total_quantity — INT — NOT NULL
- status — ENUM('DRAFT', 'IN_TRANSIT', 'COMPLETED', 'CANCELLED') — NOT NULL
- created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
- shipped_by — BIGINT
- completed_by — BIGINT
- cancelled_by — BIGINT
- created_at — DATETIME — NOT NULL
- shipped_at — DATETIME
- completed_at — DATETIME
- cancelled_at — DATETIME
stocktake_items
- id — BIGINT — Primary key — NOT NULL
- stocktake_id — BIGINT — NOT NULL
- sku_id — BIGINT — NOT NULL
- system_qty — INT — NOT NULL
- actual_qty — INT — NOT NULL
- diff_qty — INT — NOT NULL
- item_reason — TEXT
- created_at — DATETIME — NOT NULL
stocktakes
- id — BIGINT — Primary key — NOT NULL
- stocktake_no — VARCHAR(50) — NOT NULL
- location_id — BIGINT — NOT NULL
- title — VARCHAR(255)
- reason — TEXT — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') — NOT NULL
- created_by — BIGINT — NOT NULL
- confirmed_by — BIGINT
- confirmed_at — DATETIME
- created_at — DATETIME — NOT NULL