System Design – Booking/Reservation System

Posted on by user

Overview of the Ticket Booking System

  1. Components:
    • User Interface: Allows users to select events, view available tickets, and make bookings.
    • Booking Service: Manages the booking logic, including seat selection, payment processing, and ticket issuance.
    • Database: Stores information about events, tickets, and bookings.
    • Payment Gateway: Handles payment transactions.
  2. Concurrency Requirements:
    • Multiple users may try to book the same seat at the same time.
    • The system must prevent double booking of a single seat.
    • The system should handle a high volume of concurrent requests efficiently.

Event Data Schema

Creating a data schema for a ticket selling or reservation system requires a thoughtful approach to handle events, venues, tickets, customers, and reservations. Below is a simplified example of what the schema might look like:

  1. Events Table:
    • EventID: Unique identifier for the event (Primary Key).
    • Name: Name of the event.
    • VenueID: Identifier for the venue where the event is held (Foreign Key referencing Venues table).
    • DateTime: Date and time of the event.
    • Description: Brief description of the event.
  2. Venues Table:
    • VenueID: Unique identifier for the venue (Primary Key).
    • Name: Name of the venue.
    • Location: Location/address of the venue.
    • Capacity: Maximum capacity of the venue.
  3. Tickets Table:
    • TicketID: Unique identifier for the ticket (Primary Key).
    • EventID: Identifier for the event this ticket is for (Foreign Key referencing Events table).
    • Price: Price of the ticket.
    • SeatNumber: Seat number associated with the ticket (if applicable).
    • Status: Current status of the ticket (e.g., available, sold, reserved).
  4. Customers Table:
    • CustomerID: Unique identifier for the customer (Primary Key).
    • FirstName: Customer’s first name.
    • LastName: Customer’s last name.
    • Email: Customer’s email address.
    • Phone: Customer’s phone number.
  5. Reservations Table:
    • ReservationID: Unique identifier for the reservation (Primary Key).
    • CustomerID: Identifier for the customer who made the reservation (Foreign Key referencing Customers table).
    • TicketID: Identifier for the ticket reserved (Foreign Key referencing Tickets table).
    • ReservationDate: Date when the reservation was made.
    • Status: Status of the reservation (e.g., confirmed, cancelled, pending).
  6. Payments Table (Optional):
    • PaymentID: Unique identifier for the payment (Primary Key).
    • ReservationID: Identifier for the reservation this payment is for (Foreign Key referencing Reservations table).
    • Amount: Amount paid.
    • Date: Date of payment.
    • Method: Payment method (e.g., credit card, PayPal).

Steps of Making a Ticket Reservation

In a ticket-selling website, ensuring that each ticket is sold to only one person, especially in high-demand scenarios, requires a well-planned approach. This involves managing the ticket inventory and handling the user experience from the moment they click “buy” to the completion of the order. Here are the steps and considerations:

  1. Selecting the Ticket:
    • When a user selects a ticket and clicks “buy”, the system should first check if the ticket is still available.
    • If available, temporarily reserve the ticket for the user. This is typically done by marking the ticket as “held” in the database.
  2. Implementing a Hold with a Timeout:
    • The hold/reservation should have a timeout period (e.g., 10 minutes). This gives the user a fixed amount of time to complete the transaction.
    • The timeout ensures that if the user abandons the purchase or cannot complete the transaction within this period, the ticket is released back into the pool for others to buy.
  3. Starting the Transaction:
    • Initiate a transaction when the user proceeds with the purchase. Use database transactions to ensure that all steps in the booking process are completed successfully.
    • Consider using optimistic locking for the ticket record to prevent conflicts without locking the database row from the outset.
  4. Payment Processing:
    • Prompt the user to enter payment details. Validate and process the payment. This step might involve communication with external payment gateways.
    • If the payment is successful, confirm the reservation, change the ticket status from “held” to “sold”, and reduce the available ticket inventory.
    • Handling Payment Failures or Delays:
      • If the payment fails or if there is an issue with the payment gateway, inform the user and possibly allow them to retry.
      • If the payment cannot be processed within the timeout period, release the hold on the ticket.
  5. Completing the Transaction:
    • If everything is successful (including payment), complete the transaction. This typically involves finalizing the ticket sale in the database and sending a confirmation to the user (via email or on the website).
    • Ensure that all steps of the transaction are atomic – if any step fails, the entire transaction should roll back, releasing the ticket hold.
  6. Rolling Back on Timeout:
    • If the user does not complete the purchase within the designated hold period, automatically roll back the transaction.
    • Release the hold on the ticket so it becomes available for others to purchase.
    • This rollback should be handled both at the database level (reverting the ticket status) and in the user interface (informing the user that the hold has expired).
  7. User Feedback and Communication:
    • Throughout the process, keep the user informed of the status – e.g., when the ticket is held, when the payment is being processed, and when the transaction is complete or if it has failed.
    • Provide clear error messages and guidance for next steps in case of issues with the transaction.

Concurrency Control and Race Condition Resolution

  1. Pessimistic Locking:
    • Lock the record when a user selects a seat until the transaction is complete (booking or timeout).
      • Pros: Guarantees consistency by preventing concurrent modifications.
      • Cons: Can lead to bottlenecks, deadlock and reduced throughput, especially if users take a long time to complete transactions.
  2. Optimistic Locking:
    • Use versioning on ticket/seat records. When a user attempts to book a seat, the system checks if the version number has changed since it was read. If it has, it indicates another transaction has modified the record, and the current transaction is aborted.
      • Pros: Reduces lock contention, allowing more concurrency.
      • Cons: Can lead to a higher rate of transaction failures in a highly concurrent environment.
  3. Transaction Isolation Levels:
    • Adjust the database transaction isolation level to balance between concurrency and consistency. For example, REPEATABLE READ or SERIALIZABLE can be used depending on the database system.
    • Each level has its trade-offs between consistency, deadlock potential, and performance.
  4. Timeout Mechanism:
    • Implement a timeout for seat reservations. If a user does not complete the transaction within the allotted time, the seat is released for others to book.
    • This mechanism ensures that seats are not indefinitely blocked by users who have abandoned their booking process.
  5. Idempotency in Transactions:
    • Ensure that retrying transactions (like payment processing) does not result in duplicate actions. Use unique transaction IDs to detect and prevent repeated operations.
  6. Handling Failures and Rollbacks:
    • Implement robust error handling and rollback mechanisms. If a transaction fails at any stage, ensure that all changes made during that transaction are reverted.
  7. Load Balancing and Scaling:
    • Use load balancers to distribute requests evenly across multiple servers.
    • Apply horizontal scaling to the booking service to handle high loads, especially during peak times.
  8. Caching Strategies:
    • Implement caching for read-heavy operations, like viewing available seats. However, ensure cache invalidation happens promptly when data changes (like after a successful booking).
  9. Event Sourcing and CQRS (Command Query Responsibility Segregation):
    • Consider using Event Sourcing for booking transactions to record every change as a series of events. This can be coupled with CQRS to separate the read model (for querying seat availability) from the write model (for processing bookings).
    • This approach can improve performance and scalability but adds complexity to the system design.

Implementation of Hold

Most relational database management systems (RDBMS) do not natively support the concept of a “hold with a timeout” on records, such as what you might want for temporarily reserving items like tickets or hotel rooms. However, you can implement this functionality using a combination of database features and application logic. Here are a few common approaches:

  1. Application-Level Timeouts with Database Flags:
    • Use a status field in the database to mark an item as ‘held’ or ‘reserved’ and store a timestamp indicating when this hold was placed.
    • The application logic can check this timestamp and compare it with the current time. If the difference exceeds your timeout threshold (e.g., 10 minutes), the application considers the item as no longer held and can revert its status.
  2. Scheduled Jobs or Cron Tasks:
    • Implement a scheduled job (either within the application or using a database job, like a cron job) that regularly checks for held items and releases them if the hold has exceeded the timeout period.
    • This approach is effective but might not be real-time, depending on how frequently the job runs.
  3. Database Triggers:
    • Although not commonly recommended for complex business logic, database triggers can be used to implement time-based logic.
    • However, implementing timeouts directly within triggers can be complex and may lead to performance issues.
  4. Using a Cache with Expiry:
    • Implement the hold functionality in a caching layer like Redis, where you can easily set a timeout for each hold.
    • This approach is faster and more scalable but requires synchronization with the database to ensure consistency.
  5. Optimistic Locking:
    • While not a direct method for implementing holds with timeout, optimistic locking can be used to prevent conflicts without reserving the record. Each record has a version number, and before finalizing an update, the application checks if the version number has changed since it was last read.
  6. External Queueing Systems:
    • For high-demand scenarios, use a queueing system like RabbitMQ or Apache Kafka. When an item is selected, place a message in the queue with a timeout. The processing of the message (which finalizes the hold or purchase) must be completed within this timeout period.

Hotel Reservation Data Schema

Creating a data schema for a hotel reservation system involves several key tables to manage hotels, rooms, customers, and reservations. Here’s an example schema with some essential tables and their attributes:

  1. Hotels Table:
    • HotelID: Unique identifier for the hotel (Primary Key).
    • Name: Name of the hotel.
    • Address: Address of the hotel.
    • Phone: Contact phone number for the hotel.
    • Email: Contact email for the hotel.
  2. Rooms Table:
    • RoomID: Unique identifier for the room (Primary Key).
    • HotelID: Identifier for the hotel this room belongs to (Foreign Key referencing Hotels table).
    • Number: Room number or identifier.
    • Type: Type of the room (e.g., single, double, suite).
    • Price: Price per night for the room.
    • Status: Current status of the room (e.g., available, reserved, occupied).
  3. Customers Table:
    • CustomerID: Unique identifier for the customer (Primary Key).
    • FirstName: Customer’s first name.
    • LastName: Customer’s last name.
    • Email: Customer’s email address.
    • Phone: Customer’s phone number.
  4. Reservations Table:
    • ReservationID: Unique identifier for the reservation (Primary Key).
    • CustomerID: Identifier for the customer who made the reservation (Foreign Key referencing Customers table).
    • RoomID: Identifier for the reserved room (Foreign Key referencing Rooms table).
    • CheckInDate: Date of check-in.
    • CheckOutDate: Date of check-out.
    • TotalPrice: Total price for the stay.
    • Status: Status of the reservation (e.g., confirmed, cancelled, completed).
  5. Payments Table (Optional):
    • PaymentID: Unique identifier for the payment (Primary Key).
    • ReservationID: Identifier for the reservation this payment is for (Foreign Key referencing Reservations table).
    • Amount: Amount paid.
    • Date: Date of payment.
    • Method: Payment method (e.g., credit card, cash).
  6. Reviews Table (Optional):
    • ReviewID: Unique identifier for the review (Primary Key).
    • CustomerID: Identifier for the customer who left the review (Foreign Key referencing Customers table).
    • HotelID: Identifier for the hotel being reviewed (Foreign Key referencing Hotels table).
    • Rating: Rating given by the customer.
    • Comment: Optional comment by the customer.

How Airbnb Prevent Double-bookings

Airbnb, like many other online booking platforms, employs a combination of technological strategies and system design principles to prevent double bookings, i.e., two users reserving the same room for the same dates. While the exact mechanisms used by Airbnb are proprietary and not publicly detailed, we can discuss common industry practices that are likely to be relevant:

  1. Real-Time Availability Checks: When a user tries to book a room, the system performs a real-time check to confirm the availability of the room for the selected dates. This prevents a booking from proceeding if the room is already booked.
  2. Transaction and Locking
    • Database Transaction Management: Using atomic transactions in the database ensures that the process of checking availability and making a reservation is treated as a single, indivisible operation. This means that once a room is booked, other attempts to book the same room for the same dates will see the updated unavailability.
    • Optimistic Locking: This approach allows multiple transactions to proceed in parallel but checks at the time of finalizing a booking if another booking has been made in the interim. If a conflict is detected (i.e., another booking was made), the transaction is rolled back, and the user is informed that the booking could not be completed.
    • Locking at the Application Level: The system might temporarily lock the booking availability for a room while a user is in the process of completing the reservation. This lock would be short-lived but sufficient to prevent double bookings.
  3. Timeouts for Incomplete Reservations: If a user starts a booking process but doesn’t complete it within a certain timeframe, the system might automatically release the reservation to avoid blocking the room unnecessarily.
  4. Highly Consistent Data Replication: If the system uses multiple servers or databases, ensuring that data about room availability is replicated consistently and rapidly across the system is crucial to prevent double bookings.
  5. Queueing Systems for Requests: In high-load scenarios, a queueing mechanism can be used to process booking requests sequentially, thus avoiding the race condition where two bookings for the same room are processed simultaneously.
  6. User Interface Design: The user interface might be designed to update availability in real-time and to lock selections temporarily when a user is in the process of booking.
  7. Regular Audits and Monitoring: Regular system checks and monitoring to ensure that the booking process is functioning as intended and to quickly identify and rectify any issues that might lead to double bookings.

It’s important to note that while these methods are effective, no system is entirely foolproof, and edge cases might occur, particularly in scenarios with high traffic or technical glitches. However, with robust system design and constant monitoring, platforms like Airbnb manage to keep such incidents to a minimum.

Rate Limiting

Rate limiting is a critical technique used by online platforms like Airbnb to prevent abuse and ensure fair usage of their services. It involves setting a cap on the number of requests a user (or a system) can make to a server in a given period. Here’s how Airbnb might utilize rate limiting:

  1. API Throttling: If Airbnb exposes APIs (for example, to third-party developers or for internal use), they likely implement rate limits on how often those APIs can be called. This prevents overuse or misuse of their APIs, such as scraping, automated bookings, or spamming.
  2. Limiting Login Attempts: To prevent brute force attacks on user accounts, Airbnb could limit the number of login attempts per account or IP address over a specific period.
  3. Restricting Booking Attempts: To avoid automated scripts from making repeated booking attempts or cancellations, Airbnb could limit the number of bookings or cancellations a user can make in a day or hour.
  4. Preventing Spam in Messaging: In Airbnb’s messaging system, rate limits might be set to prevent users from sending too many messages in a short time, which could be indicative of spamming or harassing behavior.
  5. Controlling Search Requests: Rate limiting can be applied to search queries to prevent the system from being overwhelmed by automated queries, which could degrade performance for other users.
  6. Resource Usage Management: Limiting the rate of requests helps in managing server load and ensuring that resources are evenly distributed among users, thereby maintaining a smooth and responsive experience for everyone.
  7. Defending Against DDoS Attacks: By limiting the request rate, it becomes more challenging for attackers to overwhelm the system with a flood of requests, thereby offering some level of protection against Distributed Denial of Service (DDoS) attacks.
  8. Dynamic Rate Limits: Airbnb might implement dynamic rate limits that adjust in real-time based on usage patterns, server load, and other factors. For instance, during peak times or in case of an ongoing attack, the rate limits could be more stringent.

Use Redis to in the System

Using Redis, a powerful in-memory data store, to prevent double booking in a system like a hotel or event ticket reservation platform involves leveraging its fast read/write capabilities and atomic operations. Here’s a general approach:

1. Storing Availability Data in Redis:

  • Availability data (e.g., rooms or seats) can be stored in Redis with a structure that allows quick access. For example, using keys representing each bookable item and dates.
  • This data can be stored as simple key-value pairs, hashes, sets, or sorted sets, depending on the complexity of the requirements.

2. Atomic Check-and-Set Operations:

  • Redis provides atomic operations such as SETNX (SET if Not eXists) or GETSET. These can be used to implement a locking mechanism.
  • When a booking request comes in, the system first checks the availability for the desired dates/items. If available, it atomically sets a temporary lock (or marks the item as booked) in Redis.
  • This ensures that even if multiple requests come in simultaneously for the same item, only one will successfully set the lock and proceed with the booking.

3. Expiry for Locks:

  • To avoid permanent locks due to incomplete transactions, set an expiry time for each lock. Redis supports setting a time-to-live (TTL) for keys.
  • If the booking process is not completed within the TTL, the lock is automatically released, making the item available for booking again.

4. Transaction Support:

  • Redis transactions (MULTI/EXEC commands) can be used for scenarios where multiple operations need to be executed as a single atomic step.
  • For example, checking availability, setting a lock, and updating an inventory count can be grouped in a transaction.

5. Synchronization with Primary Database:

  • Redis can be used as a cache in front of a more persistent database. Ensure that the data in Redis is synchronized with your primary database to maintain consistency.
  • After a successful booking, update the main database and then update or clear the relevant data in Redis.