dbdb

Tables (11)

campaign

  • campaign_id — int — Primary key — NOT NULL
  • campaign_name — varchar(100) — NOT NULL
  • campaign_type — enum('Promotion','Sale','Coupon') — NOT NULL
  • start_date — date — NOT NULL
  • end_date — date — NOT NULL
  • campaign_goal — text
  • campaign_kpi — text
  • target_audience — text
  • total_revenue — decimal(15,2)

coupon

  • coupon_id — int — Primary key — NOT NULL
  • customer_id — int — Foreign key → customer.customer_id — NOT NULL
  • promotion_id — int — Foreign key → promotion.promotion_id — NOT NULL
  • issue_date — date — NOT NULL
  • expiry_date — date — NOT NULL
  • is_used — tinyint(1) — NOT NULL
  • used_date — date

customer

  • customer_id — int — Primary key — NOT NULL
  • customer_name — varchar(100)
  • customer_gender — enum('M','F')
  • customer_age_group — enum('20s','30s','40s','50s','60s+')
  • customer_phone_number — varchar(15)
  • customer_email — varchar(255)
  • membership_status — enum('Regular','VIP')
  • is_membership_joined — enum('Yes','No')
  • preferred_purchase_channel — enum('Online','Offline')
  • customer_registration_date — date
  • customer_purchase_frequency — enum('High','Medium','Low')
  • customer_spending_level — enum('High','Medium','Low')
  • preferred_product_category — varchar(50)
  • customer_last_purchase_date — date
  • customer_total_spent — decimal(10,2)
  • coupon_usage_count — int
  • campaign_participation_count — int

customer_flow

  • customer_flow_id — int — Primary key — NOT NULL
  • customer_id — int — Foreign key → customer.customer_id — NOT NULL
  • store_id — int — Foreign key → store.store_id — NOT NULL
  • visit_date — date — NOT NULL
  • time_slot — time — NOT NULL
  • zone_path — varchar(255)
  • zone_time_spent — text

employee

  • employee_id — int — Primary key — NOT NULL
  • store_id — int — Foreign key → store.store_id — NOT NULL
  • employee_name — varchar(100) — NOT NULL
  • position — varchar(50) — NOT NULL
  • employee_phone_number — varchar(15) — NOT NULL

pos_data

  • transaction_id — int — Primary key — NOT NULL
  • store_id — int — Foreign key → store.store_id — NOT NULL
  • employee_id — int — Foreign key → employee.employee_id — NOT NULL
  • purchase_time — datetime — NOT NULL
  • transaction_amount — decimal(15,2) — NOT NULL
  • payment_method — enum('Cash','Card','Mobile') — NOT NULL
  • customer_id — int — Foreign key → customer.customer_id — NOT NULL

product

  • product_id — int — Primary key — NOT NULL
  • product_name — varchar(100) — NOT NULL
  • product_category — varchar(50) — NOT NULL
  • price — decimal(15,2) — NOT NULL

promotion

  • promotion_id — int — Primary key — NOT NULL
  • campaign_id — int — Foreign key → campaign.campaign_id — NOT NULL
  • promotion_type — enum('Discount','BuyOneGetOne','Bundle') — NOT NULL
  • product_category — varchar(50)
  • discount_rate — decimal(5,2)
  • start_date — date — NOT NULL
  • end_date — date — NOT NULL

purchase_history

  • purchase_id — int — Primary key — NOT NULL
  • customer_id — int — Foreign key → customer.customer_id — NOT NULL
  • purchase_date — datetime — NOT NULL
  • purchase_channel — enum('Online','Offline') — NOT NULL
  • purchase_amount — decimal(15,2) — NOT NULL

purchase_product

  • purchase_product_id — int — Primary key — NOT NULL
  • purchase_id — int — Foreign key → purchase_history.purchase_id — NOT NULL
  • product_id — int — Foreign key → product.product_id — NOT NULL
  • quantity — int — NOT NULL

store

  • store_id — int — Primary key — NOT NULL
  • store_name — varchar(100) — NOT NULL
  • store_location — text
  • store_type — enum('Flagship','Outlet','Pop-up') — NOT NULL