cafe

Tables (15)

admin

  • admin_id — BIGINT — Primary key — NOT NULL
  • username — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

cart

  • cart_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
  • quantity — INT — NOT NULL
  • added_at — TIMESTAMP — NOT NULL

cart_option

  • cart_option_id — BIGINT — Primary key — NOT NULL
  • cart_id — BIGINT — Foreign key → cart.cart_id — NOT NULL
  • option_item_id — BIGINT — Foreign key → option_item.option_item_id — NOT NULL
  • quantity — INT — NOT NULL

category

  • category_id — BIGINT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • display_order — INT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

coupon_log

  • log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • stamp_log_id — BIGINT — Foreign key → stamp_log.log_id
  • change_type — ENUM('issue','use') — NOT NULL
  • change_amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • processed_at — TIMESTAMP — NOT NULL

menu

  • menu_id — BIGINT — Primary key — NOT NULL
  • category_id — BIGINT — Foreign key → category.category_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • price — INT — NOT NULL
  • image_url — VARCHAR(500)
  • status — ENUM('sale','sold_out') — NOT NULL
  • display_order — INT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

option_group

  • option_group_id — BIGINT — Primary key — NOT NULL
  • menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • select_type — ENUM('single','multi','quantity') — NOT NULL
  • is_required — TINYINT(1) — NOT NULL
  • display_order — INT — NOT NULL

option_item

  • option_item_id — BIGINT — Primary key — NOT NULL
  • option_group_id — BIGINT — Foreign key → option_group.option_group_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • extra_price — INT — NOT NULL
  • max_quantity — INT
  • display_order — INT — NOT NULL

order

  • order_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • order_number — VARCHAR(36) — NOT NULL
  • order_type — ENUM('dine_in','pickup') — NOT NULL
  • status — ENUM('pending','accepted','completed', 'rejected','cancelled','picked_up') — NOT NULL
  • total_price — INT — NOT NULL
  • reject_reason — VARCHAR(255)
  • ordered_at — TIMESTAMP — NOT NULL

order_item

  • order_item_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → order.order_id — NOT NULL
  • menu_id — BIGINT — Foreign key → menu.menu_id — NOT NULL
  • menu_name_snapshot — VARCHAR(100) — NOT NULL
  • unit_price_snapshot — INT — NOT NULL
  • quantity — INT — NOT NULL

order_item_option

  • order_item_option_id — BIGINT — Primary key — NOT NULL
  • order_item_id — BIGINT — Foreign key → order_item.order_item_id — NOT NULL
  • option_item_id — BIGINT — Foreign key → option_item.option_item_id — NOT NULL
  • option_group_name_snapshot — VARCHAR(100) — NOT NULL
  • option_name_snapshot — VARCHAR(100) — NOT NULL
  • option_price_snapshot — INT — NOT NULL
  • quantity — INT — NOT NULL

order_status_log

  • log_id — BIGINT — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → order.order_id — NOT NULL
  • prev_status — ENUM('pending','accepted','completed', 'rejected','cancelled','picked_up')
  • next_status — ENUM('pending','accepted','completed', 'rejected','cancelled','picked_up') — NOT NULL
  • reject_reason — VARCHAR(255)
  • changed_at — TIMESTAMP — NOT NULL

prepaid_log

  • log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • change_type — ENUM('charge','use') — NOT NULL
  • change_amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • processed_at — TIMESTAMP — NOT NULL

stamp_log

  • log_id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • order_id — BIGINT — Foreign key → order.order_id
  • change_type — ENUM('earn','deduct') — NOT NULL
  • change_amount — INT — NOT NULL
  • balance_after — INT — NOT NULL
  • processed_by — ENUM('staff','system') — NOT NULL
  • processed_at — TIMESTAMP — NOT NULL

user

  • user_id — BIGINT — Primary key — NOT NULL
  • phone_number — VARCHAR(11) — NOT NULL
  • status — ENUM('normal','blacklist') — NOT NULL
  • stamp_count — INT — NOT NULL
  • coupon_count — INT — NOT NULL
  • prepaid_balance — INT — NOT NULL
  • created_at — TIMESTAMP — NOT NULL