쇼핑몰 ERD

테이블 (26)

Address (Untitled6)

  • id (address_id) — BIGINT — 기본 키 — NOT NULL
  • zipCode — VARCHAR(10)
  • city — VARCHAR(50)
  • street — VARCHAR(100)
  • detail — VARCHAR(100)
  • created_at — DATETIME

cart (Untitled2)

  • id (cart_id) — BIGINT — 기본 키 — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME

cart_porduct (Untitled3)

  • id (cart_product_id) — BIGINT — 기본 키 — NOT NULL
  • id (cart_id) — BIGINT — 외래 키 → cart.id — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • quantity — INT
  • created_at — DATETIME
  • updated_at — DATETIME
  • selectedColor — varchar(50)
  • selectedSize — varchar(20)

category

  • id (category_id) — BIGINT — 기본 키 — NOT NULL
  • id (parent_id) — BIGINT — 외래 키 → category.id — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL

category_product

  • id (category_product_id) — BIGINT — 기본 키 — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • id (category_id) — BIGINT — 외래 키 → category.id — NOT NULL

coupon_categories (Untitled18)

  • id (coupon_categories_id) — BIGINT — 기본 키 — NOT NULL
  • id (category_id) — BIGINT — 외래 키 → category.id — NOT NULL
  • id (coupon_template_id) — BIGINT — 외래 키 → coupons.id — NOT NULL
  • created_at — DATETIME

coupon_products (Untitled17)

  • id (coupon_products_id) — BIGINT — 기본 키 — NOT NULL
  • id (coupon_template_id) — BIGINT — 외래 키 → coupons.id — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • created_at — DATETIME

coupons (Untitled9)

  • id (coupon_template_id) — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(100)
  • discount_type — VARCHAR(20)
  • discount_value — INT
  • min_order_amount — DECIMAL
  • max_discount_amount — DECIMAL
  • valid_days — INT
  • start_date — TIMESTAMP
  • end_date — TIMESTAMP
  • usage_limit — INT
  • issue_count — INT
  • used_count — INT
  • code_prefix — VARCHAR(10)
  • application_type — VARCHAR(20)

delivery_addresses (Untitled4)

  • id (delivery_address_id) — BIGINT — 기본 키 — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • id (address_id) — BIGINT — 외래 키 → Address.id — NOT NULL
  • recipient_name — VARCHAR(100)
  • recipient_phone — VARCHAR(20)
  • recipient_email (Field) — VARCHAR(255)
  • is_default — BOOLEAN
  • created_at — DATETIME
  • updated_at — DATETIME
  • is_temporary — BOOLEAN

grade_coupon_policies (Untitled8)

  • id (policy_id) — BIGINT — 기본 키 — NOT NULL
  • id (grade_it) — BIGINT — 외래 키 → user_grade.id — NOT NULL
  • id (coupon_id) — BIGINT — 외래 키 → coupons.id — NOT NULL
  • issue_type — VARCHAR(20)

guest (Untitled15)

  • id (guest_id) — BIGINT — 기본 키 — NOT NULL
  • username — VARCHAR(50)
  • password_hash — VARCHAR(255)
  • phone — VARCHAR(20)
  • created_at — DATETIME
  • zipcode — VARCHAR(10)
  • city — VARCHAR(100)
  • street — VARCHAR(255)
  • detail — VARCHAR(255)

order_product

  • id (order_product_id) — BIGINT — 기본 키 — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • id (order_id) — BIGINT — 외래 키 → orders.id — NOT NULL
  • order_price — DECIMAL(15, 2) — NOT NULL
  • quantity — INT — NOT NULL
  • selected_color
  • selected_size

orders

  • id (order_id) — BIGINT — 기본 키 — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • id (delivery_address_id) — BIGINT — 외래 키 → delivery_addresses.id — NOT NULL
  • id (guest_id) — BIGINT — 외래 키 → guest.id — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • order_date — DATETIME — NOT NULL
  • order_seq — VARCHAR(100)
  • coupon_discount — DECIMAL
  • coupon_user_id — BIGINT
  • points_used — INT

point_histories (Untitled19)

  • id (point_histories_id) — BIGINT — 기본 키 — NOT NULL
  • id (order_id) — BIGINT — 외래 키 → orders.id — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • point_type — VARCHAR(20)
  • amount — INT
  • balance_after — INT
  • description — VARCHAR(255)
  • expires_at — TIMESTAMP
  • created_at — TIMESTAMP

product_images (Untitled16)

  • id (product_images_id) — BIGINT — 기본 키 — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • image_url — VARCHAR(500)
  • display_order — INT
  • is_thumbnail — BOOLEAN
  • created_at — DATETIME

product_options (Untitled5)

  • id (product_options_id) — BIGINT — 기본 키 — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • color — VARCHAR(50)
  • size — VARCHAR(20)
  • additional_price — DECIMAL(13 2)
  • stock — INT
  • stock_status — VARCHAR(20)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

products (product)

  • id (product_id) — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • price — DECIMAL(15, 2) — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • has_option — BOOLEAN
  • average_rating — DECIMAL
  • review_count — INT

refund (Untitled)

  • id (refund_id) — BIGINT — 기본 키 — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • id (order_id) — BIGINT — 외래 키 → orders.id — NOT NULL
  • status — VARCHAR(20)
  • reason — VARCHAR(500)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • processed_at — TIMESTAMP
  • refund_amount — DECIMAL

review_comments (Untitled13)

  • id (review_comments_id) — BIGINT — 기본 키 — NOT NULL
  • id (review_id) — BIGINT — 외래 키 → reviews.id — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • id (parent_comment_id) — BIGINT — 외래 키 → review_comments.id — NOT NULL
  • content — TEXT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

review_helpfuls (Untitled12)

  • id (review_helpfuls_id) — BIGINT — 기본 키 — NOT NULL
  • id (review_id) — BIGINT — 외래 키 → reviews.id — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • created_at — TIMESTAMP

review_images (Untitled14)

  • id (review_images_id) — BIGINT — 기본 키 — NOT NULL
  • id (review_id) — BIGINT — 외래 키 → reviews.id — NOT NULL
  • image_url — VARCHAR(500)
  • created_at — TIMESTAMP
  • display_order — INT

reviews (Untitled11)

  • id (review_id) — BIGINT — 기본 키 — NOT NULL
  • id (product_id) — BIGINT — 외래 키 → products.id — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • id (order_product_id) — BIGINT — 외래 키 → order_product.id — NOT NULL
  • rating — INT
  • content — TEXT
  • helpful_count — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • size_rating — INT
  • color_rating — INT
  • thickness_rating — INT

roles

  • id (role_id) — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • description — VARCHAR(255)

user_coupons (Untitled10)

  • id (user_coupon_id) — BIGINT — 기본 키 — NOT NULL
  • id (user_id) — BIGINT — 외래 키 → users.id — NOT NULL
  • id (coupon_id) — BIGINT — 외래 키 → coupons.id — NOT NULL
  • issued_at — DATETIME
  • expires_at — DATETIME
  • used_at — DATETIME
  • order_id — BIGINT
  • status — VARCHAR(20)
  • code — VARCHAR(50)

user_grade (Untitled7)

  • id (grade_it) — BIGINT — 기본 키 — NOT NULL
  • name — VARCHAR(20)
  • min_purchase_amount — INT
  • point_rate — DECIMAL
  • discount_rate — DECIMAL
  • free_shipping_threshold — INT
  • description — VARCHAR(200)
  • created_at — DATETIME
  • updated_at — DATETIME

users

  • id (user_id) — BIGINT — 기본 키 — NOT NULL
  • id (role_id) — BIGINT — 외래 키 → roles.id — NOT NULL
  • id (grade_it) — BIGINT — 외래 키 → user_grade.id — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(20)
  • password_hash — VARCHAR(255) — NOT NULL
  • created_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • total_purchase_amount — DECIMAL
  • grade_updated_at — DATETIME
  • status — VARCHAR(20)
  • withdrawnAt — DATETIME
  • points — INT