관광데이터활용공모전
테이블 (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