week1

Tables (7)

missions

  • mission_id — INT — Primary key
  • mission_description — TEXT — NOT NULL
  • points — INT — NOT NULL
  • store_id (store_id2) — INT — Foreign key → stores.store_id

notifications

  • notification_id — INT — Primary key
  • user_id — INT
  • notification_type — ENUM('new_mission', 'review_request')
  • content — TEXT
  • created_at — TIMESTAMP
  • is_read — BOOLEAN
  • user_id (user_id2) — INT — Foreign key → users.user_id

regions

  • region_id — INT — Primary key
  • region_name — VARCHAR(50) — NOT NULL

reviews

  • review_id — INT — Primary key
  • rating — INT
  • review_text — TEXT
  • created_at — TIMESTAMP
  • user_id (user_id2) — INT — Foreign key → users.user_id
  • mission_id (mission_id2) — INT — Foreign key → missions.mission_id
  • store_id (store_id2) — INT — Foreign key → stores.store_id

stores

  • store_id — INT — Primary key
  • store_name — VARCHAR(100) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • region_id (region_id2) — INT — Foreign key → regions.region_id

user_missions

  • user_mission_id — INT — Primary key
  • status — ENUM('ongoing', 'completed', 'reviewed')
  • completed_at — TIMESTAMP
  • user_id (user_id2) — INT — Foreign key → users.user_id
  • mission_id (mission_id2) — INT — Foreign key → missions.mission_id

users

  • user_id — INT — Primary key
  • username — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP
  • points — INT