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