쇼핑몰-ERD

테이블 (9)

Cart

  • cart_id — INT — 기본 키
  • user_id — INT — 외래 키 → User.user_id
  • product_id — INT
  • quantity_in_cart — INT — NOT NULL
  • item_total — DECIMAL(12, 2)

Category

  • category_id — INT — 기본 키
  • category_name — VARCHAR(50) — NOT NULL
  • category_description — TEXT

Order

  • order_id — INT — 기본 키
  • order_detail_id — INT — 외래 키 → OrderDetail.order_detail_id
  • user_id — INT — 외래 키 → User.user_id
  • order_date — DATETIME
  • total_amount — DECIMAL(12, 2) — NOT NULL
  • shipping_address — VARCHAR(255)
  • payment_status — ENUM('완료', '대기')
  • delivery_status — ENUM('준비중', '배송중', '배송완료')
  • special_instructions — TEXT

OrderDetail

  • order_detail_id — INT — 기본 키
  • quantity — INT — NOT NULL
  • item_price — DECIMAL(10, 2) — NOT NULL
  • subtotal — DECIMAL(12, 2)
  • order_id — INT
  • product_id — INT

Payment

  • payment_id — INT — 기본 키
  • order_id (order_id2) — INT — 외래 키 → Order.order_id
  • user_id — INT — 외래 키 → Order.user_id
  • payment_date — DATETIME
  • payment_method — ENUM('카드', '계좌이체', '현금', '간편결제')
  • payment_amount — DECIMAL(12, 2) — NOT NULL
  • payment_status — ENUM('성공', '실패')

Product

  • product_id — INT — 기본 키
  • category_id (category_id2) — INT — 외래 키 → Category.category_id
  • product_name — VARCHAR(100) — NOT NULL
  • unit_price — DECIMAL(10, 2) — NOT NULL
  • stock_quantity — INT
  • product_description — TEXT
  • manufacturer — VARCHAR(50)
  • product_status — ENUM('판매중', '품절')
  • cart_id — INT — 외래 키 → Cart.cart_id
  • user_id — INT — 외래 키 → Cart.user_id

Review

  • reviewer_id — INT — 기본 키
  • product_id (product_id2) — INT — 외래 키 → Product.product_id
  • user_id — INT — 외래 키 → User.user_id
  • review_content — TEXT
  • rating — TINYINT
  • review_date — DATETIME

Shipping

  • shipping_id — INT — 기본 키
  • order_id (order_id2) — INT — 외래 키 → Order.order_id
  • user_id — INT — 외래 키 → Order.user_id
  • order_id — INT
  • tracking_number — VARCHAR(50)
  • shipping_date — DATETIME
  • estimated_arrival — DATETIME
  • shipping_status — ENUM('배송중', '도착')

User

  • user_id — INT — 기본 키
  • username — VARCHAR(50) — NOT NULL
  • email_address — VARCHAR(100) — NOT NULL
  • user_password — VARCHAR(255) — NOT NULL
  • contact_number — VARCHAR(20)
  • home_address — VARCHAR(255)
  • user_role — ENUM('고객', '관리자')
  • registration_date — DATETIME
  • account_status — ENUM('활성', '비활성')