관광데이터활용공모전

테이블 (28)

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

content_translations

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

contents

  • content_id — BIGINT — 기본 키 — NOT NULL
  • content_type — ENUM('DRAMA', 'MOVIE', 'MUSIC', 'VARIETY') — NOT NULL
  • original_language_code — VARCHAR(5) — NOT NULL
  • release_year — INT
  • thumbnail_url — VARCHAR(1000) — 방송사 포스터 금지. 촬영지 사진 또는 CC 이미지만
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

email_verifications

  • email_verification_id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • code — VARCHAR(10) — NOT NULL
  • purpose — ENUM('SIGNUP','PASSWORD_RESET') — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • verified_at — DATETIME
  • attempt_count — INT — NOT NULL
  • created_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
  • is_active — BOOLEAN — NOT NULL

inquiries

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

place_categories

  • place_category_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • category — VARCHAR(30) — NOT NULL
  • assignment_source — ENUM('TOUR_API','DERIVED','MANUAL') — NOT NULL
  • 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
  • booking_url — VARCHAR(1000)
  • synced_at — DATETIME

place_images

  • place_image_id — BIGINT — 기본 키 — NOT NULL
  • is_primary — BOOLEAN — 기본 키 — 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)
  • display_order — INT
  • synced_at — DATETIME

place_sources

  • place_source_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • provider — ENUM('TOUR_API', 'KAKAO_LOCAL', 'PHOTO_KOREA', 'MANUAL') — NOT NULL
  • external_id — VARCHAR(255) — NOT NULL — TourAPI contentid, 카카오 place id 등
  • service_name — VARCHAR(50) — KorService2 / EngService2 ...
  • content_type_id — VARCHAR(20)
  • cat1 — VARCHAR(20)
  • cat2 — VARCHAR(20)
  • cat3 — VARCHAR(20)
  • raw_payload — JSON — 원본 응답 전체. 재호출 없이 필드 추가 대응
  • synced_at — DATETIME

place_translations

  • place_translation_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • language_code — VARCHAR(5) — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(500)
  • overview — TEXT — <br> 등 HTML 태그 제거 후 저장
  • 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
  • raw_intro_payload — JSON — detailIntro2 응답 전체
  • synced_at — DATETIME

place_visit_info_translations

  • place_visit_info_translation_id — BIGINT — 기본 키 — NOT NULL
  • place_id — BIGINT — 외래 키 → places.place_id — NOT NULL
  • language_code — VARCHAR(5) — NOT NULL
  • opening_hours_text — TEXT — usetime 원문. 파싱하지 않고 그대로 표시
  • rest_date_text — TEXT
  • parking_text — TEXT
  • admission_fee_text — TEXT
  • use_time_text — TEXT
  • synced_at — DATETIME

places

  • place_id — BIGINT — 기본 키 — NOT NULL
  • tour_content_id — BIGINT — TourAPI contentid 직결. 카카오 Local 전용 장소는 NULL
  • latitude — DECIMAL(10, 7) — NOT NULL — TourAPI mapy
  • longitude — DECIMAL(10, 7) — NOT NULL — TourAPI mapx
  • area_code — VARCHAR(20)
  • sigungu_code — VARCHAR(20)
  • recommended_stay_minutes — INT — API 미제공. 카테고리별 기본값 사용
  • is_active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME
  • last_synced_at — DATETIME

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 — ENUM('ANCHOR', 'SPOT', 'BREAKFAST', 'LUNCH', 'DINNER', 'CAFE', 'NIGHT_VIEW', 'SHOPPING', 'STAY') — NOT NULL
  • visit_order — INT — NOT NULL — TSP 결과 방문 순서
  • planned_arrival_at — DATETIME
  • planned_departure_at — DATETIME
  • stay_minutes — INT
  • source_type — ENUM('ANCHOR', 'RECOMMENDED', 'USER_SELECTED', 'FESTIVAL_SURPRISE') — NOT NULL
  • selection_score — DECIMAL(10, 4)
  • score_breakdown — JSON — 추천 근거. 발표 시 설명용
  • 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

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)
  • request_hash — VARCHAR(64) — 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 — vertexes
  • 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) — KorService2 등
  • operation_name — VARCHAR(100) — areaBasedSyncList2 등
  • language_code — VARCHAR(5)
  • area_code — VARCHAR(20)
  • sigungu_code — VARCHAR(20)
  • status — ENUM('RUNNING', 'SUCCESS', 'PARTIAL', 'FAILED') — NOT NULL
  • last_page_no — INT — 재시작 지점. 없으면 매번 처음부터 긁게 됨
  • fetched_count — INT
  • inserted_count — INT
  • updated_count — INT
  • 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

travel_plans

  • plan_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — 게스트 모드 지원을 위해 NULL 허용
  • anchor_content_place_id — BIGINT — 외래 키 → content_places.content_place_id
  • title — VARCHAR(255)
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • language_code — VARCHAR(5) — 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
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME

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_content_interests

  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • content_id — BIGINT — 외래 키 → contents.content_id — NOT NULL
  • created_at — DATETIME — NOT NULL

user_settings

  • user_setting_id — BIGINT — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → users.user_id — NOT NULL
  • language_code — VARCHAR(5) — NOT NULL — KO/EN/ZH/JA
  • notification_enabled — BOOLEAN — NOT NULL

users

  • user_id — BIGINT — 기본 키 — NOT NULL
  • email — VARCHAR(255) — 게스트는 NULL
  • password_hash — VARCHAR(255) — 소셜/게스트는 NULL
  • nickname — VARCHAR(100)
  • 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
  • deleted_at — DATETIME