CVS

Tables (16)

customer

  • customer_id — INT — Primary key — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • name — VARCHAR(30) — NOT NULL
  • joined_at — DATETIME — NOT NULL

employee

  • employee_id — INT — Primary key — NOT NULL
  • store_id — INT — Foreign key → store.store_id — NOT NULL
  • name — VARCHAR(30) — NOT NULL
  • phone — VARCHAR(20)
  • hired_at — DATE — NOT NULL

event_notification_log

  • notification_id — INT — Primary key — NOT NULL
  • customer_id — INT — Foreign key → customer.customer_id — NOT NULL
  • item_type_id — INT — Foreign key → item_type.item_type_id — NOT NULL
  • sent_at — DATETIME — NOT NULL

goods_receipt

  • receipt_id — INT — Primary key — NOT NULL
  • purchase_order_detail_id — INT — Foreign key → purchase_order_detail.purchase_order_detail_id — NOT NULL — 발주 건이 아닌 발주상세(품목 단위)를 참조하여 분할입고 지원
  • received_quantity — INT — NOT NULL
  • received_at — DATETIME — NOT NULL

inventory

  • inventory_id — INT — Primary key — NOT NULL
  • item_id — INT — Foreign key → item.item_id — NOT NULL
  • store_id — INT — Foreign key → store.store_id — NOT NULL
  • location_type — ENUM('매장','창고') — NOT NULL
  • quantity — INT — NOT NULL
  • updated_at — DATETIME — NOT NULL

item

  • item_id — INT — Primary key — NOT NULL
  • item_type_id — INT — Foreign key → item_type.item_type_id — NOT NULL
  • item_name — VARCHAR(100) — NOT NULL
  • barcode — VARCHAR(13) — NOT NULL — EAN-8/13, UPC-A/E 가변길이 대응. 앞자리 0 보존을 위해 문자열 저장
  • price — INT — NOT NULL — 원화, 소수점 없음

item_type

  • item_type_id — INT — Primary key — NOT NULL
  • type_name — VARCHAR(30) — NOT NULL

point_history

  • point_id — INT — Primary key — NOT NULL
  • customer_id — INT — Foreign key → customer.customer_id — NOT NULL
  • sale_id — INT — Foreign key → sale.sale_id — 적립/사용이 발생한 매출 건, 이벤트성 적립은 NULL
  • type — ENUM('적립', '사용') — NOT NULL
  • amount — INT — NOT NULL — 이 건의 적립/사용량
  • balance_after — INT — NOT NULL — 이 거래 직후 잔액 스냅샷, 매번 전체합산 방지 목적
  • created_at — DATETIME — NOT NULL

promotion

  • promotion_id — INT — Primary key — NOT NULL
  • promotion_name — VARCHAR(50) — NOT NULL
  • promotion_type — ENUM('1+1','묶음할인') — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL

promotion_item

  • promotion_item_id — INT — Primary key — NOT NULL
  • promotion_id — INT — Foreign key → promotion.promotion_id — NOT NULL
  • item_id — INT — Foreign key → item.item_id — NOT NULL
  • required_quantity — INT — NOT NULL — 이 프로모션에서 이 품목이 몇 개 필요한지

purchase_order

  • purchase_order_id — INT — Primary key — NOT NULL
  • store_id — INT — Foreign key → store.store_id — NOT NULL
  • status — ENUM('발주','입고완료') — NOT NULL
  • ordered_at — DATETIME — NOT NULL

purchase_order_detail

  • purchase_order_detail_id — INT — Primary key — NOT NULL
  • purchase_order_id — INT — Foreign key → purchase_order.purchase_order_id — NOT NULL
  • item_id — INT — Foreign key → item.item_id — NOT NULL
  • order_quantity — INT — NOT NULL
  • unit_cost — INT — NOT NULL

sale

  • sale_id — INT — Primary key — NOT NULL
  • store_id — INT — Foreign key → store.store_id — NOT NULL
  • employee_id — INT — Foreign key → employee.employee_id — NOT NULL — 계산 처리한 판매원
  • customer_id — INT — Foreign key → customer.customer_id — 비회원 구매 허용
  • total_amount — INT — NOT NULL — 계산된 필드지만 사건형 테이블이라 의도적 유지
  • status — ENUM('정상', '취소') — NOT NULL
  • sold_at — DATETIME — NOT NULL

sale_cancel

  • cancel_id — INT — Primary key — NOT NULL
  • sale_id — INT — Foreign key → sale.sale_id — NOT NULL
  • cancelled_by — INT — Foreign key → employee.employee_id — NOT NULL — 취소 처리한 판매원
  • cancel_reason — VARCHAR(200)
  • cancelled_at — DATETIME — NOT NULL

sale_detail

  • sale_detail_id — INT — Primary key — NOT NULL
  • sale_id — INT — Foreign key → sale.sale_id — NOT NULL
  • item_id — INT — Foreign key → item.item_id — NOT NULL
  • quantity — INT — NOT NULL
  • unit_price — INT — NOT NULL — 판매 시점 단가 스냅샷, item.price와 별개로 보존
  • promotion_id — INT — Foreign key → promotion.promotion_id — 적용된 프로모션, 없으면 NULL

store

  • store_id — INT — Primary key — NOT NULL
  • store_name — VARCHAR(50) — NOT NULL
  • address — VARCHAR(200) — NOT NULL
  • manager_name — VARCHAR(30) — NOT NULL
  • opened_at — DATE — NOT NULL
  • phone — VARCHAR(20)