중앙해커톤

테이블 (13)

Alert_Log

  • alert_id — INT — 기본 키
  • user_id — INT — NOT NULL
  • alert_type — ENUM('shelter','traffic','community','system') — NOT NULL
  • message — TEXT — NOT NULL
  • created_at — DATETIME
  • read_status — BOOLEAN

Chatbot_Log

  • log_id — INT — 기본 키
  • user_id (user_id2) — INT — 외래 키 → User.user_id
  • message_type — ENUM('user','bot') — NOT NULL
  • message_content — TEXT — NOT NULL
  • created_at — DATETIME

Community_Comment

  • comment_id — INT
  • post_id (post_id2) — INT — 외래 키 → Community_Post.post_id
  • user_id — INT — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME

Community_Post

  • post_id — INT — 기본 키
  • user_id (user_id2) — INT — 외래 키 → User.user_id
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • category — ENUM('notice','emergency','general')
  • likes — INT
  • created_at — DATETIME
  • updated_at — DATETIME

Live_Map_Community

  • post_id (post_id2) — INT — 외래 키 → Community_Post.post_id
  • post_count — INT
  • top_keywords — TEXT
  • source_post_ids — TEXT

Live_Map_Shelter

  • shelter_id (shelter_id2) — INT — 외래 키 → Shelter.shelter_id
  • shelter_type — ENUM('earthquake','flood','heatwave','etc')
  • capacity — INT
  • facilities — TEXT
  • source_id — INT

Live_Map_Traffic

  • traffic_id (traffic_id2) — INT — 외래 키 → Traffic_Current_Info.traffic_id
  • road_name — VARCHAR(100)
  • congestion_level — ENUM('low','medium','high')
  • source_id — INT

Shelter

  • shelter_id — INT — 기본 키
  • name — VARCHAR(100) — NOT NULL
  • latitude — DECIMAL(10,6) — NOT NULL
  • longitude — DECIMAL(10,6) — NOT NULL
  • address — VARCHAR(255)
  • shelter_type — ENUM('earthquake','flood','heatwave','etc')
  • capacity — INT
  • facilities — TEXT
  • contact_number — VARCHAR(20)
  • updated_at — DATETIME

Traffic_Current_Info

  • traffic_id — INT — 기본 키
  • road_name — VARCHAR(100)
  • congestion_level — ENUM('low','medium','high')
  • latitude — DECIMAL(10,6) — NOT NULL
  • longitude — DECIMAL(10,6) — NOT NULL
  • updated_at — DATETIME

Traffic_Future_Info

  • traffic_id — INT — 외래 키 → Traffic_Current_Info.traffic_id
  • road_name — VARCHAR(100)
  • congestion_level — ENUM('low','medium','high')
  • source_id — INT
  • time_set — DATETIME

User

  • user_id — INT — 기본 키
  • username — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • role — ENUM('admin','user')
  • created_at — DATETIME
  • last_login — DATETIME

Weather_Current_Info

  • weather_id — INT — 기본 키
  • location_name — VARCHAR(100)
  • latitude — DECIMAL(10,6) — NOT NULL
  • longitude — DECIMAL(10,6) — NOT NULL
  • temperature — DECIMAL(4,1)
  • humidity — DECIMAL(4,1)
  • wind_speed — DECIMAL(4,1)
  • uv_index — DECIMAL(3,1)
  • weather_condition — VARCHAR(50)
  • updated_at — DATETIME

Weather_Future_Info

  • location_name — VARCHAR(100)
  • latitude — DECIMAL(10,6) — NOT NULL
  • longitude — DECIMAL(10,6) — NOT NULL
  • temperature — DECIMAL(4,1)
  • humidity — DECIMAL(4,1)
  • wind_speed — DECIMAL(4,1)
  • uv_index — DECIMAL(3,1)
  • weather_condition — VARCHAR(50)
  • time_set — DATETIME