Q2_Inventory_ERD_v6
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('SUPER_ADMIN', 'ADMIN', 'STAFF', 'MD', 'DESIGN') — 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 — Foreign key → inbound_orders.id — NOT NULL
- sku_id — BIGINT — Foreign key → skus.id — NOT NULL
- quantity — INT — NOT NULL — 기존 수량 컬럼 / received_quantity와 의미 중복 가능
- expected_quantity — INT — 예정 수량
- received_quantity — INT — 실 입고 수량
- defect_quantity — INT — NOT NULL — 불량 수량
- scanned_barcode — VARCHAR(100)
- inspection_method — ENUM('COUNT', 'MEASUREMENT')
- inspection_note — TEXT
- created_at — DATETIME — NOT NULL
- Field
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 — Foreign key → inventory_locations.id — NOT NULL
- supplier — VARCHAR(255)
- memo — TEXT
- reason — TEXT
- expected_arrival_date — DATE
- actual_arrival_date — DATE
- total_quantity — INT — NOT NULL
- expected_total_quantity — INT — 예정 총 수량
- inspection_status — ENUM('PENDING', 'PARTIAL', 'MATCHED', 'MISMATCH') — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') — NOT NULL
- created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
- created_by_type — ENUM('ADMIN', 'STAFF', 'SYSTEM') — NOT NULL
- confirmed_by — BIGINT — Foreign key → admin_users.id
- 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', 'DEFECT', 'ETC') — NOT NULL — DEFECT = 불량 격리 창고
- 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 — 계약 / 협업 종료일
- 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 — Foreign key → product_categories.id
- 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
- sale_price — DECIMAL(12, 2) — 판매가 / 카페24 동기화
- cost_price — DECIMAL(12, 2) — 매입가 / 우리 시스템 기준
- lifecycle_status — ENUM('ACTIVE', 'DISCONTINUED', 'OUT_OF_STOCK', 'RESERVED') — NOT NULL
- launched_at — DATE
- discontinued_at — DATE
- box_quantity — INT — 박스당 수량
- unit — ENUM('EA', 'BOX', 'SET', 'PACK') — NOT NULL
- weight_g — INT — 무게(g)
- has_expiry — BOOLEAN — NOT NULL — 유통기한 관리 여부
- 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 — 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 — NOT NULL
sales_uploads
- id — BIGINT — Primary key — NOT NULL
- upload_no — VARCHAR(50) — NOT NULL
- location_id — BIGINT — Foreign key → inventory_locations.id — 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 — Foreign key → admin_users.id — 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) — 기존 바코드 컬럼 / deprecated
- internal_barcode — VARCHAR(100) — 내부 발급 바코드 / 유일
- external_barcode — VARCHAR(100) — 업체 원본 바코드 / 중복 가능
- option_name — VARCHAR(255)
- cost_price — DECIMAL(12, 2) — SKU 옵션 단위 원가
- image_url — VARCHAR(500) — 옵션별 이미지. NULL이면 product.main_image_url 사용
- is_active — BOOLEAN — NOT NULL
- registration_status — ENUM('REGISTERED', 'PENDING', 'TEMPORARY') — NOT NULL
- temp_created_by — BIGINT — Foreign key → admin_users.id — 임시 SKU 생성자
- temp_resolved_at — DATETIME — 정식 SKU 전환 시각
- 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', 'DEFECT_OUT', 'RESEND_IN' ) — 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_by_type — ENUM('ADMIN', 'STAFF', 'SYSTEM') — 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 — Foreign key → admin_users.id
- completed_by — BIGINT — Foreign key → admin_users.id
- cancelled_by — BIGINT — Foreign key → admin_users.id
- 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 — 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
- diff_reason — ENUM( 'UNKNOWN', 'MISSING', 'MIS_ENTRY', 'UNRECORDED_SALE', 'DEFECT', 'THEFT', 'OTHER' ) — 차이 발생 원인
- resolution_status — ENUM( 'PENDING', 'INVESTIGATING', 'RESOLVED', 'WRITTEN_OFF' ) — NOT NULL
- resolved_by — BIGINT — Foreign key → admin_users.id
- resolved_at — DATETIME
- resolution_note — TEXT
- created_at — DATETIME — NOT NULL
stocktakes
- id — BIGINT — Primary key — NOT NULL
- stocktake_no — VARCHAR(50) — NOT NULL
- location_id — BIGINT — Foreign key → inventory_locations.id — NOT NULL
- title — VARCHAR(255)
- reason — TEXT — NOT NULL
- status — ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') — NOT NULL
- created_by — BIGINT — Foreign key → admin_users.id — NOT NULL
- confirmed_by — BIGINT — Foreign key → admin_users.id
- confirmed_at — DATETIME
- created_at — DATETIME — NOT NULL