title: Hotel booking system design - Reservation which uses multiple rooms
body: <p>I'm looking for some design advice on a hotel room like booking system.
  I've read a lot of stuff on this so far and I've got a query which returns a list of rooms which are available for a certain date range.</p>
  
  <p>My search currently only returns rooms which are available for the entire booking period the user is searching for. I'd like to expand this so I can fulfill the request with multiple rooms. I.e. User searches for a date range and Room 1 is available for the first 2 days, then they would need to be transferred to Room 2 for the last 2 days.</p>
  
  <p>I currently do my availability lookup using the following query;</p>
  
  <pre><code>Dim available = From k In rooms _
              Group Join r In db.Reservations.Where(Function(fx) (fx.DateIn &gt;= Date_From And fx.DateIn &lt; Date_To) Or (fx.DateOut &gt; Date_From And fx.DateOut &lt;= Date_To)) On k Equals r.Room Into Group _
              From r In Group.DefaultIfEmpty
              Where r Is Nothing
              Select k
  </code></pre>
  
  <p>How can I alter my design or expand on the current design to allow multiple rooms per booking? I'm a bit stumped!</p>
  
  <p>Thanks in advance!</p>