zzz

Tables (28)

additional_service

  • service_id — CHAR(36) — Primary key — NOT NULL
  • airline_id — CHAR(36) — Foreign key → airline.airline_id — NOT NULL
  • service_category — VARCHAR(20)
  • service_name — VARCHAR(100) — NOT NULL
  • base_price — BIGINT — NOT NULL
  • available_for_route — VARCHAR(20)
  • available_for_class — VARCHAR(20)
  • max_quantity_per_passenger — INT
  • image_url — VARCHAR(500)
  • is_available — CHAR(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

airline

  • airline_id — CHAR(36) — Primary key — NOT NULL
  • airline_name — VARCHAR(100) — NOT NULL
  • airline_code — VARCHAR(20) — NOT NULL
  • country — VARCHAR(100)
  • logo_url — VARCHAR(500)

airplane

  • airplane_id — CHAR(36) — Primary key — NOT NULL
  • airline_id — CHAR(36) — Foreign key → airline.airline_id — NOT NULL
  • airplane_type — VARCHAR(100) — NOT NULL

airplane_info

  • airplane_info_id — CHAR(36) — Primary key — NOT NULL
  • airplane_id — CHAR(36) — Foreign key → airplane.airplane_id — NOT NULL
  • total_seat — INT — NOT NULL
  • discount_rate — DECIMAL(10,4)
  • checked_baggage_included — CHAR(1)
  • meal_included — CHAR(1)
  • first_class_seat — INT
  • business_class_seat — INT
  • economy_class_seat — INT

airplane_seat

  • airplane_seat_id — CHAR(36) — Primary key — NOT NULL
  • airplane_info_id — CHAR(36) — Foreign key → airplane_info.airplane_info_id — NOT NULL
  • seat_no — VARCHAR(10) — NOT NULL

airport

  • airport_id — CHAR(36) — Primary key — NOT NULL
  • country — VARCHAR(100)
  • city — VARCHAR(100)
  • airport_code — VARCHAR(20) — NOT NULL

baggage_policy

  • policy_id — CHAR(36) — Primary key — NOT NULL
  • airline_id — CHAR(36) — Foreign key → airline.airline_id — NOT NULL
  • route_type — VARCHAR(20) — NOT NULL
  • cabin_class_code — VARCHAR(10) — Foreign key → cabin_class.cabin_class_code — NOT NULL
  • free_checked_bags — INT
  • free_carry_on_bags — INT
  • free_checked_weight_kg — INT
  • extra_bag_free — BIGINT
  • overweight_fee_per_kg — BIGINT
  • oversize_fee — BIGINT
  • max_weight_per_bag_kg — INT
  • max_total_weight_kg — INT
  • is_active — CHAR(1) — NOT NULL

cabin_class

  • cabin_class_code — VARCHAR(10) — Primary key — NOT NULL
  • cabin_class_name — VARCHAR(50) — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — CHAR(1) — NOT NULL

class_info

  • class_info_id — CHAR(36) — Primary key — NOT NULL
  • airplane_info_id — CHAR(36) — Foreign key → airplane_info.airplane_info_id — NOT NULL
  • cabin_class_code — VARCHAR(10) — Foreign key → cabin_class.cabin_class_code — NOT NULL

fare_price

  • fare_id — CHAR(36) — Primary key — NOT NULL
  • airline_id — CHAR(36) — Foreign key → airline.airline_id — NOT NULL
  • departure_airport — CHAR(36) — Foreign key → airport.airport_id — NOT NULL
  • arrival_airport — CHAR(36) — Foreign key → airport.airport_id — NOT NULL
  • route_type — VARCHAR(20) — NOT NULL
  • cabin_class_code — VARCHAR(10) — Foreign key → cabin_class.cabin_class_code — NOT NULL
  • fare_type — VARCHAR(20) — NOT NULL
  • day_type — VARCHAR(20)
  • season_type — VARCHAR(20)
  • air_fare — BIGINT — NOT NULL
  • fuel_surcharge — BIGINT — NOT NULL
  • tax — BIGINT — NOT NULL
  • total_fare — BIGINT — NOT NULL
  • currency — CHAR(3) — NOT NULL
  • effective_start_date — DATE — NOT NULL
  • effective_end_date — DATE — NOT NULL
  • is_active — CHAR(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

fare_price_rule

  • fare_price_rule_id — CHAR(36) — Primary key — NOT NULL
  • fare_id — CHAR(36) — Foreign key → fare_price.fare_id — NOT NULL
  • rule_id — CHAR(36) — Foreign key → fare_rule.rule_id — NOT NULL
  • is_active — CHAR(1) — NOT NULL

fare_rule

  • rule_id — CHAR(36) — Primary key — NOT NULL
  • airline_id — CHAR(36) — Foreign key → airline.airline_id — NOT NULL
  • rule_type — VARCHAR(20) — NOT NULL
  • priority — INT — NOT NULL
  • rule_note — VARCHAR(500)
  • is_active — CHAR(1) — NOT NULL
  • created_at — DATETIME — NOT NULL

fare_rule_condition

  • condition_id — CHAR(36) — Primary key — NOT NULL
  • rule_id — CHAR(36) — Foreign key → fare_rule.rule_id — NOT NULL
  • route_type — VARCHAR(20)
  • departure_airport — CHAR(36) — Foreign key → airport.airport_id
  • arrival_airport — CHAR(36) — Foreign key → airport.airport_id
  • direction — VARCHAR(10)
  • day_of_week — VARCHAR(50)
  • dep_time_from — TIME
  • dep_time_to — TIME
  • travel_start_date — DATE
  • travel_end_date — DATE

fare_rule_effect

  • effect_id — CHAR(36) — Primary key — NOT NULL
  • rule_id — CHAR(36) — Foreign key → fare_rule.rule_id — NOT NULL
  • apply_target — VARCHAR(20) — NOT NULL
  • adjust_type — VARCHAR(20) — NOT NULL
  • adjust_value — DECIMAL(12,4) — NOT NULL
  • rounding_policy — VARCHAR(20)

flight_info

  • flight_id — CHAR(36) — Primary key — NOT NULL
  • airplane_info_id — CHAR(36) — Foreign key → airplane_info.airplane_info_id — NOT NULL
  • departure_airport — CHAR(36) — Foreign key → airport.airport_id — NOT NULL
  • arrival_airport — CHAR(36) — Foreign key → airport.airport_id — NOT NULL
  • departure_time — DATETIME — NOT NULL
  • arrival_time — DATETIME — NOT NULL
  • terminal_no — VARCHAR(20)
  • flight_number — VARCHAR(10) — NOT NULL

flight_seat

  • flight_seat_id — CHAR(36) — Primary key — NOT NULL
  • flight_id — CHAR(36) — Foreign key → flight_info.flight_id — NOT NULL
  • airplane_seat_id — CHAR(36) — Foreign key → airplane_seat.airplane_seat_id — NOT NULL
  • cabin_class_code — VARCHAR(10) — Foreign key → cabin_class.cabin_class_code — NOT NULL
  • seat_status — VARCHAR(20) — NOT NULL
  • hold_reservation_segment_id — CHAR(36) — Foreign key → reservation_segment.reservation_segment_id
  • hold_expires_at — DATETIME
  • booked_at — DATETIME

layover

  • layover_id — CHAR(36) — Primary key — NOT NULL
  • airport_id — CHAR(36) — Foreign key → airport.airport_id — NOT NULL
  • flight_id — CHAR(36) — Foreign key → flight_info.flight_id — NOT NULL
  • departed_at — DATETIME — NOT NULL
  • arrived_at — DATETIME — NOT NULL

meal_option

  • meal_id — CHAR(36) — Primary key — NOT NULL
  • airline_id — CHAR(36) — Foreign key → airline.airline_id — NOT NULL
  • meal_category — VARCHAR(50)
  • meal_type — VARCHAR(50)
  • meal_name — VARCHAR(100) — NOT NULL
  • price — BIGINT — NOT NULL
  • available_for_route — VARCHAR(20)
  • available_for_class — VARCHAR(50)
  • requires_advance_order — CHAR(1)
  • image_url — VARCHAR(500)
  • is_active — CHAR(1) — NOT NULL

passenger

  • passenger_id — CHAR(36) — Primary key — NOT NULL
  • reservation_id — CHAR(36) — Foreign key → reservation.reservation_id — NOT NULL
  • first_name — VARCHAR(100) — NOT NULL
  • last_name — VARCHAR(100) — NOT NULL
  • passport_no — VARCHAR(20)
  • birth — DATE
  • country — VARCHAR(100)
  • gender — CHAR(1)
  • email — VARCHAR(320)
  • insurance_enrolled — CHAR(1)
  • checked_baggage_applied — CHAR(1)

passenger_seat

  • passenger_seat_id — CHAR(36) — Primary key — NOT NULL
  • reservation_segment_id — CHAR(36) — Foreign key → reservation_segment.reservation_segment_id — NOT NULL
  • passenger_id — CHAR(36) — Foreign key → passenger.passenger_id — NOT NULL
  • flight_seat_id — CHAR(36) — Foreign key → flight_seat.flight_seat_id — NOT NULL

passenger_service

  • ps_id — CHAR(36) — Primary key — NOT NULL
  • service_id — CHAR(36) — Foreign key → additional_service.service_id
  • passenger_id — CHAR(36) — Foreign key → passenger.passenger_id — NOT NULL
  • meal_id — CHAR(36) — Foreign key → meal_option.meal_id
  • quantity — INT — NOT NULL
  • unit_price — BIGINT — NOT NULL
  • total_price — BIGINT — NOT NULL
  • service_details — VARCHAR(2000)
  • status — VARCHAR(20) — NOT NULL
  • added_at — DATETIME — NOT NULL

payment

  • payment_id — CHAR(36) — Primary key — NOT NULL
  • reservation_id — CHAR(36) — Foreign key → reservation.reservation_id — NOT NULL
  • transaction_id — CHAR(36) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • paid_at — DATETIME
  • amount — BIGINT — NOT NULL
  • payment_method — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL

price_history

  • price_history_id — CHAR(36) — Primary key — NOT NULL
  • flight_id — CHAR(36) — Foreign key → flight_info.flight_id — NOT NULL
  • cabin_class_code — VARCHAR(10) — Foreign key → cabin_class.cabin_class_code — NOT NULL
  • fare_type — VARCHAR(20) — NOT NULL
  • observed_at — DATETIME — NOT NULL
  • price_total — BIGINT — NOT NULL
  • price_air_fare — BIGINT — NOT NULL
  • price_fuel_surcharge — BIGINT — NOT NULL
  • price_tax — BIGINT — NOT NULL
  • source — VARCHAR(50)
  • meta_json — VARCHAR(2000)

reservation

  • reservation_id — CHAR(36) — Primary key — NOT NULL
  • user_id — CHAR(36) — Foreign key → users.user_id — NOT NULL
  • reserved_at — DATETIME — NOT NULL
  • status — VARCHAR(255) — NOT NULL

reservation_segment

  • reservation_segment_id — CHAR(36) — Primary key — NOT NULL
  • reservation_id — CHAR(36) — Foreign key → reservation.reservation_id — NOT NULL
  • flight_id — CHAR(36) — Foreign key → flight_info.flight_id — NOT NULL
  • segment_order — INT — NOT NULL

user_fare_price_rule

  • user_id — CHAR(36) — Foreign key → users.user_id — NOT NULL
  • fare_price_rule_id — CHAR(36) — Foreign key → fare_price_rule.fare_price_rule_id — NOT NULL

user_profile

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

users

  • user_id — CHAR(36) — Primary key — NOT NULL
  • email — VARCHAR(320) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • login_type — VARCHAR(20) — NOT NULL
  • role — VARCHAR(20) — NOT NULL