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