관광데이터활용공모전

테이블 (34)

categories

  • category_id — BIGINT — 기본 키 — NOT NULL
  • category_code — VARCHAR(50) — NOT NULL
  • category_kind — ENUM('BASE', 'DERIVED') — NOT NULL
  • derivation_rule — JSON
  • sort_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

category_translations

  • category_translation_id — BIGINT — 기본 키 — NOT NULL
  • category_id — BIGINT — 외래 키 → categories.category_id — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • display_name — VARCHAR(100) — NOT NULL

content_media

  • media_id — BIGINT — 기본 키 — NOT NULL
  • content_id — BIGINT — 외래 키 → contents.content_id — NOT NULL
  • content_place_id — BIGINT — 외래 키 → content_places.content_place_id
  • media_type — ENUM('IMAGE', 'VIDEO', 'AUDIO', 'GIF') — NOT NULL
  • media_url — VARCHAR(1000) — NOT NULL
  • title — VARCHAR(255)
  • artist — VARCHAR(255)
  • start_second — INT
  • end_second — INT
  • is_highlight — BOOLEAN — NOT NULL

content_places

  • content_place_id — BIGINT — 기본 키 — NOT NULL
  • content_id — BIGINT — 외래 키 → contents.content_id — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • relation_type — ENUM('FILMING_LOCATION', 'BACKGROUND', 'INSPIRATION', 'OTHER') — NOT NULL
  • scene_description — TEXT
  • is_anchor — BOOLEAN — NOT NULL
  • is_representative — BOOLEAN — NOT NULL
  • display_order — INT — NOT NULL

content_translations

  • content_translation_id — BIGINT — 기본 키 — NOT NULL
  • content_id — BIGINT — 외래 키 → contents.content_id — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT

contents

  • content_id — BIGINT — 기본 키 — NOT NULL
  • content_type — ENUM('DRAMA', 'MOVIE', 'MUSIC', 'VARIETY', 'WEBTOON', 'OTHER') — NOT NULL
  • original_language_code — VARCHAR(5) — 외래 키 → languages.language_code
  • release_year — INT
  • thumbnail_url — VARCHAR(1000)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

featured_places

  • featured_place_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • content_id — BIGINT — 외래 키 → contents.content_id
  • feature_type — VARCHAR(50) — NOT NULL
  • priority — INT — NOT NULL
  • start_at — DATETIME
  • end_at — DATETIME
  • active — BOOLEAN — NOT NULL

inquiries

  • inquiry_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • category — VARCHAR(50)
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • status — VARCHAR(30) — NOT NULL
  • created_at — DATETIME — NOT NULL

languages

  • language_code — VARCHAR(5) — 기본 키 — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • is_active — BOOLEAN — NOT NULL

place_categories

  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • category_id — BIGINT — 외래 키 → categories.category_id — NOT NULL
  • assignment_source — ENUM('TOUR_API', 'DERIVED', 'MANUAL') — NOT NULL
  • confidence_score — DECIMAL(5,4)
  • computed_at — DATETIME

place_events

  • place_event_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • event_start_date — DATE — NOT NULL
  • event_end_date — DATE — NOT NULL
  • event_start_time — TIME
  • event_end_time — TIME
  • booking_url — VARCHAR(1000)
  • synced_at — DATETIME — NOT NULL

place_images

  • place_image_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • provider — ENUM('TOUR_API', 'KAKAO_LOCAL', 'PHOTO_KOREA', 'MANUAL') — NOT NULL
  • external_image_id — VARCHAR(255)
  • original_url — VARCHAR(1000) — NOT NULL
  • thumbnail_url — VARCHAR(1000)
  • license_type — VARCHAR(100)
  • attribution — VARCHAR(500)
  • is_primary — BOOLEAN — NOT NULL
  • display_order — INT — NOT NULL
  • synced_at — DATETIME

place_operating_hours

  • operating_hour_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • day_of_week — TINYINT — NOT NULL
  • opens_at — TIME
  • closes_at — TIME
  • closes_next_day — BOOLEAN — NOT NULL
  • is_closed — BOOLEAN — NOT NULL
  • valid_from — DATE
  • valid_to — DATE
  • parse_status — VARCHAR(30) — NOT NULL
  • source_text — TEXT

place_sources

  • place_source_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • source_key — VARCHAR(500) — NOT NULL
  • provider — ENUM('TOUR_API', 'KAKAO_LOCAL', 'PHOTO_KOREA', 'MANUAL') — NOT NULL
  • service_name — VARCHAR(50)
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code
  • external_id — VARCHAR(255) — NOT NULL
  • content_type_id — VARCHAR(20)
  • cat1 — VARCHAR(20)
  • cat2 — VARCHAR(20)
  • cat3 — VARCHAR(20)
  • raw_payload — JSON
  • source_modified_at — DATETIME
  • synced_at — DATETIME — NOT NULL

place_translations

  • place_translation_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(500)
  • overview — TEXT
  • homepage_url — VARCHAR(1000)
  • translation_source — ENUM('TOUR_API', 'AUTO_TRANSLATED', 'MANUAL') — NOT NULL
  • is_fallback — BOOLEAN — NOT NULL
  • synced_at — DATETIME

place_visit_info

  • place_visit_info_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • parking_available — BOOLEAN
  • check_in_time — TIME
  • check_out_time — TIME
  • raw_intro_payload — JSON
  • synced_at — DATETIME — NOT NULL

place_visit_info_translations

  • place_visit_info_translation_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • opening_hours_text — TEXT
  • rest_date_text — TEXT
  • parking_text — TEXT
  • admission_fee_text — TEXT
  • use_time_text — TEXT
  • synced_at — DATETIME

places

  • place_id — BIGINT — 기본 키 — NOT NULL
  • latitude — DECIMAL(10,7) — NOT NULL
  • longitude — DECIMAL(10,7) — NOT NULL
  • area_code — VARCHAR(20)
  • sigungu_code — VARCHAR(20)
  • legal_dong_code — VARCHAR(30)
  • recommended_stay_minutes — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • last_synced_at — DATETIME

plan_category_preferences

  • plan_id — BIGINT — 외래 키 → travel_plans.plan_id — NOT NULL
  • category_id — BIGINT — 외래 키 → categories.category_id — NOT NULL
  • preference_weight — DECIMAL(5,2) — NOT NULL

plan_days

  • plan_day_id — BIGINT — 기본 키 — NOT NULL
  • plan_id — BIGINT — 외래 키 → travel_plans.plan_id — NOT NULL
  • day_no — INT — NOT NULL
  • plan_date — DATE — NOT NULL
  • day_start_time — TIME
  • day_end_time — TIME
  • max_travel_minutes — INT

plan_places

  • plan_place_id — BIGINT — 기본 키 — NOT NULL
  • plan_day_id — BIGINT — 외래 키 → plan_days.plan_day_id — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • slot_type — VARCHAR(50) — NOT NULL
  • visit_order — INT — NOT NULL
  • planned_arrival_at — DATETIME
  • planned_departure_at — DATETIME
  • stay_minutes — INT — NOT NULL
  • source_type — ENUM('ANCHOR', 'RECOMMENDED', 'USER_SELECTED', 'FESTIVAL_SURPRISE') — NOT NULL
  • selection_score — DECIMAL(10,4)
  • score_breakdown — JSON
  • hours_check_status — VARCHAR(30)
  • notes — TEXT

refresh_tokens

  • refresh_token_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • token_hash — VARCHAR(255) — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • revoked_at — DATETIME
  • created_at — DATETIME — NOT NULL

regional_metrics

  • regional_metric_id — BIGINT — 기본 키 — NOT NULL
  • area_code — VARCHAR(20) — NOT NULL
  • sigungu_code — VARCHAR(20)
  • metric_month — DATE — NOT NULL
  • nationality_code — VARCHAR(20)
  • foreign_visitor_count — BIGINT
  • spending_amount — DECIMAL(18,2)
  • source_name — VARCHAR(100) — NOT NULL
  • fetched_at — DATETIME — NOT NULL

route_legs

  • route_leg_id — BIGINT — 기본 키 — NOT NULL
  • plan_id — BIGINT — 외래 키 → travel_plans.plan_id — NOT NULL
  • route_query_id — BIGINT — 외래 키 → route_queries.route_query_id
  • from_plan_place_id — BIGINT — 외래 키 → plan_places.plan_place_id — NOT NULL
  • to_plan_place_id — BIGINT — 외래 키 → plan_places.plan_place_id — NOT NULL
  • leg_order — INT — NOT NULL
  • transport_mode — ENUM('CAR', 'PUBLIC_TRANSIT') — NOT NULL
  • distance_meters — INT
  • duration_seconds — INT
  • fare — INT
  • route_polyline — TEXT
  • external_map_url — VARCHAR(1000)
  • created_at — DATETIME — NOT NULL

route_queries

  • route_query_id — BIGINT — 기본 키 — NOT NULL
  • provider — ENUM('KAKAO_MOBILITY', 'ODSAY') — NOT NULL
  • transport_mode — ENUM('CAR', 'PUBLIC_TRANSIT') — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code
  • request_hash — VARCHAR(64) — NOT NULL
  • request_payload — JSON — NOT NULL
  • response_payload — JSON
  • total_distance_meters — INT
  • total_duration_seconds — INT
  • total_fare — INT
  • taxi_fare — INT
  • toll_fare — INT
  • route_polyline — TEXT
  • status — ENUM('PENDING', 'SUCCESS', 'FAILED') — NOT NULL
  • error_code — VARCHAR(100)
  • created_at — DATETIME — NOT NULL
  • fetched_at — DATETIME
  • expires_at — DATETIME

saved_places

  • saved_place_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • created_at — DATETIME — NOT NULL

social_accounts

  • social_account_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • provider — ENUM('GOOGLE', 'KAKAO', 'NAVER', 'APPLE') — NOT NULL
  • provider_user_id — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL

sync_jobs

  • sync_job_id — BIGINT — 기본 키 — NOT NULL
  • provider — ENUM('TOUR_API', 'KAKAO_LOCAL', 'PHOTO_KOREA', 'MANUAL') — NOT NULL
  • service_name — VARCHAR(50) — NOT NULL
  • operation_name — VARCHAR(100) — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code
  • area_code — VARCHAR(20)
  • sigungu_code — VARCHAR(20)
  • status — ENUM('RUNNING', 'SUCCESS', 'PARTIAL', 'FAILED') — NOT NULL
  • last_page_no — INT
  • fetched_count — INT — NOT NULL
  • inserted_count — INT — NOT NULL
  • updated_count — INT — NOT NULL
  • error_message — TEXT
  • started_at — DATETIME — NOT NULL
  • finished_at — DATETIME

terms

  • term_id — BIGINT — 기본 키 — NOT NULL
  • term_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • version — VARCHAR(30) — NOT NULL
  • required — BOOLEAN — NOT NULL
  • content — TEXT — NOT NULL

tour_category_mappings

  • tour_category_mapping_id — BIGINT — 기본 키 — NOT NULL
  • category_id — BIGINT — 외래 키 → categories.category_id — NOT NULL
  • service_name — VARCHAR(50) — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • content_type_id — VARCHAR(20)
  • cat1 — VARCHAR(20)
  • cat2 — VARCHAR(20)
  • cat3 — VARCHAR(20)
  • extra_filter_rule — JSON

travel_plans

  • plan_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • anchor_content_place_id — BIGINT — 외래 키 → content_places.content_place_id — NOT NULL
  • title — VARCHAR(255)
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • default_transport_mode — ENUM('CAR', 'PUBLIC_TRANSIT') — NOT NULL
  • status — ENUM('DRAFT', 'GENERATING', 'COMPLETED', 'FAILED') — NOT NULL
  • total_distance_meters — INT
  • total_duration_minutes — INT
  • generation_version — VARCHAR(50)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_agreements

  • agreement_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • term_id — BIGINT — 외래 키 → terms.term_id — NOT NULL
  • agreed — BOOLEAN — NOT NULL
  • agreed_at — DATETIME — NOT NULL

user_settings

  • user_setting_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • language_code — VARCHAR(5) — 외래 키 → languages.language_code — NOT NULL
  • notification_enabled — BOOLEAN — NOT NULL

users

  • user_id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255)
  • nickname — VARCHAR(100) — NOT NULL
  • profile_image_url — VARCHAR(1000)
  • email_verified_at — DATETIME
  • status — ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'WITHDRAWN') — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME