MESH-HACKATHON

Tables (13)

activities

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • volunteer_id — INT — Foreign key → volunteers.id
  • title — VARCHAR(200)
  • org_name — VARCHAR(100)
  • category_code — VARCHAR(10)
  • status — ENUM('pending','scheduled','completed','cancelled') — NOT NULL
  • scheduled_date — DATE
  • completed_at — DATETIME
  • hours — DECIMAL(4,1)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

badges

  • id — INT — Primary key — NOT NULL
  • code — VARCHAR(30) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • threshold_count — INT — NOT NULL
  • icon_url — VARCHAR(255)

bookmarks

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • volunteer_id — INT — Foreign key → volunteers.id — NOT NULL
  • created_at — DATETIME — NOT NULL

notification_settings

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • deadline_alert — BOOLEAN — NOT NULL
  • activity_reminder — BOOLEAN — NOT NULL
  • badge_alert — BOOLEAN — NOT NULL
  • updated_at — DATETIME — NOT NULL

notifications

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • type — ENUM('deadline','activity_reminder','badge','system') — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • body — VARCHAR(300) — NOT NULL
  • is_read — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

popular_keywords

  • id — INT — Primary key — NOT NULL
  • keyword — VARCHAR(100) — NOT NULL
  • category_code — VARCHAR(10)
  • search_rank — INT — NOT NULL
  • rank_change — INT — NOT NULL
  • is_new — BOOLEAN — NOT NULL
  • updated_at — DATETIME

search_history

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • keyword — VARCHAR(100) — NOT NULL
  • searched_at — DATETIME — NOT NULL

user_badges

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • badge_id — INT — Foreign key → badges.id — NOT NULL
  • earned_at — DATETIME — NOT NULL

user_interests

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • category_code — VARCHAR(10) — NOT NULL
  • created_at — DATETIME — NOT NULL

user_regions

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • sido_cd — INT — NOT NULL
  • gugun_cd — INT
  • created_at — DATETIME — NOT NULL

users

  • id — INT — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(100)
  • password_hash — VARCHAR(255)
  • provider — ENUM('local','kakao','naver','google') — NOT NULL
  • provider_id — VARCHAR(100)
  • age_group — ENUM('10s','20s','30s','40s','50s','60s+')
  • profile_image_url — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

volunteer_mbti

  • id — INT — Primary key — NOT NULL
  • user_id — INT — Foreign key → users.id — NOT NULL
  • mbti_type — VARCHAR(30) — NOT NULL
  • time_slot — ENUM('WEEKDAY_MORNING','WEEKDAY_AFTERNOON','WEEKEND','ANY')
  • frequency — ENUM('WEEKLY','TWO_THREE_MONTHLY','MONTHLY','IRREGULAR')
  • indoor_outdoor — ENUM('INDOOR','OUTDOOR')
  • group_style — ENUM('SOLO','GROUP')
  • region_mode — ENUM('NEAR_ME','CUSTOM_REGION','ONLINE')
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

volunteers

  • id — INT — Primary key — NOT NULL
  • source — ENUM('1365','vms') — NOT NULL
  • source_id — VARCHAR(50) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • org_name — VARCHAR(100)
  • act_place — VARCHAR(200)
  • sido_cd — INT
  • gugun_cd — INT
  • latitude — DECIMAL(10,7)
  • longitude — DECIMAL(10,7)
  • category_code — VARCHAR(10)
  • srvc_cl_code — VARCHAR(50)
  • start_date — DATE
  • end_date — DATE
  • notice_start_date — DATE
  • notice_end_date — DATE
  • act_begin_time — TIME
  • act_end_time — TIME
  • raw_time_text — VARCHAR(120)
  • time_parse_source — ENUM('API','CONTENT','TITLE','UNKNOWN') — NOT NULL
  • act_wkdy — VARCHAR(20)
  • recog_hours — DECIMAL(4,1)
  • content — TEXT
  • status — ENUM('RECRUITING','CLOSED','UNKNOWN') — NOT NULL
  • raw_status — VARCHAR(50)
  • adult_posbl — CHAR(1)
  • family_posbl — CHAR(1)
  • group_posbl — CHAR(1)
  • youth_posbl — CHAR(1)
  • recruit_num — INT
  • apply_total — INT
  • tel — VARCHAR(20)
  • email — VARCHAR(100)
  • url — VARCHAR(500)
  • detail_url — VARCHAR(500)
  • synced_at — DATETIME
  • crawled_at — DATETIME