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