Design a relational data model for a flight-booking service that must support these operations efficiently: - Book a passenger on a flight. - Retrieve the passenger list for a flight. - Retrieve all flights booked by a passenger. - Place a temporary hold on a seat before a booking is completed. Define tables, primary and foreign keys, uniqueness rules, indexes, transaction boundaries, and hold expiration behavior. Explain how the design prevents two users from holding or booking the same seat concurrently. ### Constraints & Assumptions - A flight instance represents one scheduled departure, not a reusable route definition. - Seat labels are unique within one flight instance. - A passenger may book many flights, and a flight has many passengers. - Holds expire automatically and do not become bookings without an explicit confirmation. - Client retries and concurrent requests are expected. ### Clarifying Questions to Ask - Can one booking contain multiple passengers and seats? - May passengers have bookings without assigned seats? - What states and cancellation rules apply to bookings? - How long do holds last, and may they be extended? ### What a Strong Answer Covers - Models the flight-passenger relationship with a booking or segment table rather than duplicating arrays. - Uses keys and indexes that directly serve both flight-to-passenger and passenger-to-flight queries. - Defines seat inventory and a database-enforced exclusivity strategy. - Gives holds explicit state and expiration, with safe confirmation and cleanup. - Handles retries, cancellation, and concurrent confirmation transactionally. - Discusses historical schedule changes, audit fields, and query pagination. ### Follow-up Questions 1. How would you support a multi-passenger reservation under one confirmation code? 2. What happens if a hold expires at the same moment confirmation arrives? 3. How would you model a flight cancellation or aircraft seat-map change without destroying history?