sol-flight

테이블 (17)

airline

  • airline_id — CHAR(36) — 기본 키 — NOT NULL
  • airline_name — VARCHAR(100) — NOT NULL
  • airline_code — VARCHAR(20) — NOT NULL
  • country — VARCHAR(100) — NOT NULL
  • logo_url — VARCHAR(500)

airplane

  • airplane_id — CHAR(36) — 기본 키 — NOT NULL
  • airline_id — CHAR(36) — NOT NULL
  • airplane_type — VARCHAR(100) — NOT NULL

airplane_info

  • airplane_info_id — CHAR(36) — 기본 키 — NOT NULL
  • total_seat — INT — NOT NULL
  • discount_rate — DECIMAL(5,2)
  • checked_baggage_included — CHAR(1)
  • meal_included — CHAR(1)
  • first_class_seat — INT
  • business_class_seat — INT
  • economy_class_seat — INT

airport

  • airport_id — CHAR(36) — 기본 키 — NOT NULL
  • country — VARCHAR(100) — NOT NULL
  • city — VARCHAR(100) — NOT NULL
  • airport_code — VARCHAR(20) — NOT NULL

class_info

  • class_info_id — CHAR(36) — 기본 키 — NOT NULL
  • airplane_info_id — CHAR(36) — NOT NULL
  • seat_class — VARCHAR(20) — NOT NULL
  • base_price — BIGINT — NOT NULL

flight_info

  • flight_id — CHAR(36) — 기본 키 — NOT NULL
  • airplane_info_id — CHAR(36) — NOT NULL
  • departure_airport — CHAR(36) — NOT NULL
  • arrival_airport — CHAR(36) — NOT NULL
  • departure_time — DATETIME — NOT NULL
  • arrival_time — DATETIME — NOT NULL
  • terminal_no — VARCHAR(20) — NOT NULL

layover

  • layover_id — CHAR(36) — 기본 키 — NOT NULL
  • airport_id — CHAR(36) — NOT NULL
  • flight_id — CHAR(36) — NOT NULL
  • departed_at — DATETIME
  • arrived_at — DATETIME

passenger

  • passenger_id — CHAR(36) — 기본 키 — NOT NULL
  • reservation_id — CHAR(36) — NOT NULL
  • first_name — VARCHAR(100) — NOT NULL
  • last_name — VARCHAR(100) — NOT NULL
  • passport_no — VARCHAR(20) — NOT NULL
  • birth — DATE — NOT NULL
  • country — VARCHAR(100) — NOT NULL
  • gender — CHAR(1) — NOT NULL — M(남) | F(여)
  • email — VARCHAR(320) — NOT NULL
  • insurance_enrolled — CHAR(1) — NOT NULL
  • checked_baggage_applied — CHAR(1) — NOT NULL

passenger_seat

  • passenger_seat_id — CHAR(36) — 기본 키 — NOT NULL
  • reservation_segment_id — CHAR(36) — NOT NULL
  • passenger_id — CHAR(36) — NOT NULL
  • seat_id — CHAR(36) — NOT NULL

payment

  • payment_id — CHAR(36) — 기본 키 — NOT NULL
  • reservation_id — CHAR(36) — NOT NULL
  • transaction_id — CHAR(36) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • paid_at — DATETIME — NOT NULL
  • amount — BIGINT — NOT NULL
  • payment_method — VARCHAR(20) — NOT NULL — CARD | BANK_TRANSFER | KAKAO_PAY | NAVER_PAY
  • status — VARCHAR(20) — NOT NULL — PENDING | PAID | FAILED | CANCELLED | REFUNDED

price_history

  • price_history_id — CHAR(36) — 기본 키 — NOT NULL
  • airplane_seat_id — CHAR(36) — NOT NULL
  • created_at — DATETIME
  • price — BIGINT

reservation

  • reservation_id — CHAR(36) — 기본 키 — NOT NULL
  • user_id — CHAR(36) — NOT NULL
  • reserved_at — DATETIME — NOT NULL
  • status — VARCHAR(255) — NOT NULL — HELD | CONFIRMED | EXPIRED

reservation_segment

  • reservation_segment_id — CHAR(36) — 기본 키 — NOT NULL
  • reservation_id — CHAR(36) — NOT NULL
  • flight_id — CHAR(36) — NOT NULL
  • segment_order — INT

seat

  • seat_id — CHAR(36) — 기본 키 — NOT NULL
  • airplane_seat_id — CHAR(36) — NOT NULL
  • class_info_id — CHAR(36) — NOT NULL
  • reservation_id — CHAR(36) — NOT NULL
  • seat_status — VARCHAR(20) — NOT NULL — AVAILABLE | HELD | RESERVED

user_profile

  • user_id — CHAR(36) — 외래 키 → users.user_id — NOT NULL
  • passport_no — VARCHAR(20) — NOT NULL
  • country — VARCHAR(100) — NOT NULL
  • gender — CHAR(1) — NOT NULL
  • first_name — VARCHAR(100) — NOT NULL
  • last_name — VARCHAR(100) — NOT NULL

users

  • user_id — CHAR(36) — 기본 키 — NOT NULL
  • email — VARCHAR(320) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • login_type — VARCHAR(20) — NOT NULL — LOCAL | KAKAO | NAVER | GOOGLE | APPLE
  • role — VARCHAR(20) — NOT NULL

클래스조건 (class_case)

  • 조건id (case_id) — BIGINT — 외래 키 → class_info.class_info_id — NOT NULL — 가격 상황별 분류
  • 등급 (class) — VARCHAR(20) — NOT NULL
  • 성수기 (peak) — boolean — NOT NULL — 0 pkak | 1 nonPeak
  • 선호시간 (preference_time) — boolean — NOT NULL — 0 preference | 1 nonPreference