title: Designing a high performing hotel room booking system
body: <p>I am facing a situation of designing a booking system of a hotel website that may handle thousand and thousands of hotel rooms, and their availability. Something like the Expedia website.</p>
  
  <p>This availability would need to be stored locally, and I would need to query these hotel rooms for availability.</p>
  
  <p>So far I have arrived in the following structure:</p>
  
  <ul>
  <li>A division of room_type, which would be a combination of its characteristics, such as if it has a view, or maximum people in the room, e.g.: with view/3 people would be a type alone, and other types would be: with view/4 people, no view 2 people, with view/4 people, etc.\</li>
  <li>Each room would be assigned to a room_type for presentation purposes in the frontend. E.g.: I wouldn't show the individual room record, (e.g.: room 201), but I would show that I have room available of that type.</li>
  <li>So I would have a room "table" and a room_type "table" and each room would have a type.</li>
  <li>Then there is a matrix of rooms (rows) and dates (columns) from today and up to 6 months in the future.</li>
  </ul>
  
  <p>The matrix would look like the following:
  <a href="https://i.stack.imgur.com/TAq7u.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TAq7u.png" alt="Availability matrix"></a></p>
  
  <p>Where 1 means that room is available on that date, and 0 means it is booked. So for me to detect if a room_type is available in a date range, I have to:</p>
  
  <ol>
  <li>For each room of this type;</li>
  <li>Scan all columns between the required dates;</li>
  <li>If there is a room in which no 0s were found in that row, it means it is available, so it means that this room_type can be shown as available for that date range.</li>
  </ol>
  
  <p>In the above table, suppose the date range was 10/10/2017 to 10/14/2017 (US format), the only available room would be room 101 at hotel C.</p>
  
  <p>I can then book a Customer to that room, so the customer won't need to change rooms during his/hers stay.</p>
  
  <p><strong>My questions are:</strong></p>
  
  <ul>
  <li>Is there any storage that can hold a big Matrix like that in memory? (MySQL, Redis, PostgreSQL, Cassandra, MongoDB, etc)</li>
  <li>If a RDBMS is what I should use, what kind? Should I use a traditional RDBMS? Or would this "matrix-like structure" fit better another flavour of newer NoSQL database.</li>
  <li>Is this "matrix-like structure" how these huge highly visited websites structure their data? (E.g.: Expedia, Booking.com, Trivago, etc) Or is there a better method that is faster to query for date ranges?</li>
  </ul>
  
  <p>I am worried about not picking the correct structure now, and stumble into performance problems later in the future when the website is under high demand and with an even higher volume of rooms.</p>
  
  <p>Thank you.</p>