CVS_2
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
- discount_method — ENUM('정률', '정액') — 할인 타입일 때만 사용. 정률=퍼센트, 정액=원화 고정금액
- discount_value — INT — 정률이면 40(%), 정액이면 5000(원) - discount_method로 해석 방식 결정
- 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
- discount_amount — INT — NOT NULL — 판매 시점에 실제 적용된 할인액(원) 스냅샷. promotion.discount_value가 나중에 바뀌어도 과거 기록 보존
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)