Department Store

Tables (8)

Campaign

  • campaign_id — INT — Primary key
  • campaign_name — VARCHAR(100)
  • start_date — DATE
  • end_date — DATE

Campaign_Participation

  • participation_id — INT — Primary key
  • customer_id — INT — Foreign key → Customer.customer_id
  • campaign_id — INT — Foreign key → Campaign.campaign_id
  • participation_date — DATE

Coupon

  • coupon_id — INT — Primary key
  • coupon_name — VARCHAR(100)
  • discount — DECIMAL(5, 2)
  • valid_from — DATE
  • valid_to — DATE

Coupon_Usage

  • usage_id — INT — Primary key
  • customer_id — INT — Foreign key → Customer.customer_id
  • coupon_id — INT — Foreign key → Coupon.coupon_id
  • purchase_id — INT — Foreign key → Purchase.purchase_id
  • usage_date — DATE

Customer

  • customer_id — INT — Primary key
  • name (customer_name) — VARCHAR(100)
  • gender — ENUM('남성', '여성')
  • age — INT
  • phone_number — VARCHAR(255)
  • address — VARCHAR(255)
  • join_date — DATE
  • line (channel) — ENUM('온라인', '오프라인')

Log

  • log_id — INT — Primary key — NOT NULL
  • customer_id — INT — Foreign key → Customer.customer_id
  • channel — ENUM('온라인', '오프라인');
  • visit_log — TIMESTAMP

Product

  • product_id — INT — Primary key
  • product_name — VARCHAR(100) — NOT NULL
  • price — DECIMAL(10, 2
  • brand_name — VARCHAR(100)

Purchase

  • purchase_id — INT — Primary key
  • customer_id — INT — Foreign key → Customer.customer_id
  • product_id — INT — Foreign key → Product.product_id
  • purchase_date — DATE
  • channel — ENUM('매장', '온라인')
  • count — INT
  • total_spent — DEMICAL(10, 2)