title: Hotel Reservation system Database schema
body: <p>I am about to develop a online hotel reservation system...using php and mysql... I have some doubts about my current database schema and the business logic to get the hotels in which rooms are free between two particular dates...</p>
  
  <p>Does anyone know of some kind of tutorial where i can get some idea about the hotel reservation schema and the business logics that should be used in the system...?</p>
  
  <p>Thanks for your suggestions....</p>
  
  <p>Edit :
     I've figured out most of the logic... The points i am not clear about are the following...</p>
  
  <ol>
  <li><p>If a user selects more than one room in a particular hotel between two particular dates how can i represent in the following reservation table...?</p>
  
  <p>Table : Reservation</p>
  
  <pre><code> Field 1 : reservation_id
   Field 2 : room_id
   Field 3 : no. of Rooms
   Field 4 : check-in date
   Field 5 : check-out date
   Field 6 : Customer id
  </code></pre></li>
  <li><p>How can i check what rooms are available between two dates based on the reservation table and the following rooms table...?</p>
  
  <p>Table : Room</p>
  
  <pre><code>     Field 1 : hotel_id
       Field 2 : room_id
       Field 3 : total_num_rooms
  </code></pre></li>
  </ol>
  
  <p>Note : The db contains more than one hotel... So it's like a user can select a city and look for rooms available in hotels in that area between two particular dates...</p>
  
  <p>Also say if there are 10 numbers of room of a particular type in a hotel, i need to show only the number of rooms that are free in that particular time period..... </p>