DB수업
테이블 (18)
결제 (payment)
- payment_id — VARCHAR(36)
- order_id — VARCHAR(36) — 외래 키 → 주문.order_id — NOT NULL
- method — VARCHAR(50) — 신용카드/카카오페이/네이버페이 등
- amount — DECIMAL(12, 0) — NOT NULL
- status — ENUM('APPROVED', 'CANCELLED') — NOT NULL
- paid_at — DATETIME — NOT NULL
- Field
맴버 (member)
- member_id — VARCHAR(36)
- member_name — VARCHAR(50) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL — bcrypt 암호화
- phone_number — VARCHAR(15)
- address_main — VARCHAR(200)
- address_detail — VARCHAR(100)
- postal_code — VARCHAR(10)
- created_at — DATETIME — NOT NULL
- status — ENUM('ACTIVE', 'LOCKED', 'WITHDRAWN') — NOT NULL
배달 (delivery)
- delivery_id — VARCHAR(36)
- order_id — VARCHAR(36) — 외래 키 → 주문.order_id — NOT NULL
- carrier — VARCHAR(50)
- tracking_number — VARCHAR(50)
- status — ENUM('READY','IN_TRANSIT','DELIVERED') — NOT NULL
- shipped_at — DATETIME
- delivered_at — DATETIME
브랜드 (brand)
- brand_id — VARCHAR(36)
- brand_name — VARCHAR(100) — NOT NULL
- brand_logo_url — VARCHAR(500)
- created_at — DATETIME — NOT NULL
사이즈 (size)
- size_id — VARCHAR(36)
- size_label — VARCHAR(10) — S/M/L/XL/2XL
- size_value — VARCHAR(20) — 85/90/95/265mm
- sort_order — INT — NOT NULL
상품 (product)
- product_id — VARCHAR(36)
- product_name — VARCHAR(200) — NOT NULL
- brand_id — VARCHAR(36) — 외래 키 → 브랜드.brand_id
- category_id — VARCHAR(36) — 외래 키 → 카테고리.category_id
- price — DECIMAL(10, 0) — NOT NULL — 정가
- discount_rate — DECIMAL(5, 2) — NOT NULL — 예: 10.50 = 10.5%
- description — TEXT
- created_at — DATETIME — NOT NULL
상품 옵션 (product_option)
- option_id — VARCHAR(36)
- product_id — VARCHAR(36) — 외래 키 → 상품.product_id — NOT NULL
- color_id — VARCHAR(36) — 외래 키 → 색깔.color_id — NOT NULL
- size_id — VARCHAR(36) — 외래 키 → 사이즈.size_id — NOT NULL
- stock_qty — INT — NOT NULL
- additional_price — DECIMAL(10,0) — NOT NULL
- is_available — BOOLEAN — NOT NULL
상품 이미지 (product_image)
- image_id — VARCHAR(36)
- product_id — VARCHAR(36) — 외래 키 → 상품.product_id — NOT NULL
- image_url — VARCHAR(500) — NOT NULL
- is_main — BOOLEAN — NOT NULL
- sort_order — INT — NOT NULL
상품옵션수정이력 (product_option_history)
- history_id — VARCHAR(36)
- option_id — VARCHAR(36) — 외래 키 → 상품 옵션.option_id — NOT NULL
- changed_at — DATETIME — NOT NULL
- changed_by — VARCHAR(100)
- field_name — VARCHAR(50)
- before_value — VARCHAR(500)
- after_value — VARCHAR(500)
색깔 (color)
- color_id — VARCHAR(36)
- color_name — VARCHAR(50) — NOT NULL
- hex_code — CHAR(7) — #RRGGBB
세션 (session)
- session_id — VARCHAR(36)
- member_id — VARCHAR(36) — 외래 키 → 맴버.member_id — NOT NULL
- created_at — DATETIME — NOT NULL
- expires_at — DATETIME — NOT NULL
- ip_address — VARCHAR(45)
- user_agent — VARCHAR(500)
- is_active — BOOLEAN — NOT NULL
주문 (order)
- order_id — VARCHAR(36)
- member_id — VARCHAR(36) — 외래 키 → 맴버.member_id — NOT NULL
- total_amount — DECIMAL(12,0) — NOT NULL
- delivery_fee — DECIMAL(10,0) — NOT NULL
- status — ENUM('PAID','PREPARING','SHIPPING','DELIVERED','CONFIRMED','CANCELLED','RETURNED') — NOT NULL
- ordered_at — DATETIME — NOT NULL
- recipient_name — VARCHAR(50) — NOT NULL
- recipient_phone — VARCHAR(15) — NOT NULL
- delivery_address — VARCHAR(300) — NOT NULL
- delivery_postal_code — VARCHAR(10)
주문 상품 (order_item)
- order_item_id — VARCHAR(36)
- order_id — VARCHAR(36) — 외래 키 → 주문.order_id — NOT NULL
- option_id — VARCHAR(36) — 외래 키 → 상품 옵션.option_id — NOT NULL
- quantity — INT — NOT NULL
- price_at_order — DECIMAL(10, 0) — NOT NULL — 주문 당시 단가 고정
- discount_amount — DECIMAL(10, 0) — NOT NULL
카테고리 (category)
- category_id — VARCHAR(36)
- parent_category_id — VARCHAR(36) — 외래 키 → 카테고리.category_id
- category_name — VARCHAR(100) — NOT NULL
- gender — ENUM('M', 'F', 'U') — NOT NULL — M:남성 F:여성 U:공용
- depth — INT — NOT NULL — 0:대분류 1:소분류
- sort_order — INT — NOT NULL
카트 (cart)
- cart_id — VARCHAR(36)
- member_id — VARCHAR(36) — 외래 키 → 맴버.member_id — NOT NULL
- created_at — DATETIME — NOT NULL
카트 아이템 (cart_item)
- cart_item_id — VARCHAR(36)
- cart_id — VARCHAR(36) — 외래 키 → 카트.cart_id — NOT NULL
- option_id — VARCHAR(36) — 외래 키 → 상품 옵션.option_id — NOT NULL
- quantity — INT — NOT NULL
- is_selected — BOOLEAN — NOT NULL
- added_at — DATETIME — NOT NULL
쿠폰 (coupon)
- coupon_id — VARCHAR(36)
- member_id — VARCHAR(36) — 외래 키 → 맴버.member_id — NOT NULL
- discount_type — ENUM('FIXED', 'RATE') — NOT NULL — FIXED:정액 RATE:정률
- discount_value — DECIMAL(10, 2) — NOT NULL
- min_order_amount — DECIMAL(10, 0) — NOT NULL
- expires_at — DATETIME — NOT NULL
- is_used — BOOLEAN — NOT NULL
포인트 (point)
- point_id — VARCHAR(36)
- member_id — VARCHAR(36) — 외래 키 → 맴버.member_id — NOT NULL
- amount — DECIMAL(10,0) — NOT NULL
- reason — VARCHAR(200)
- expires_at — DATETIME
- created_at — DATETIME — NOT NULL