title: Online travel agent database design (rates table) for hotels in mysql
body: <p>I have developed a system to manage hotel rates, allotments and bookings using php and mysql for a travel agent with round 150 hotels.
  As the hotel room rates differ by date table design is as follows roughly</p>
  
  <pre><code>hotel_tbl{
   hotel_id,
   hotel_name
  }    
  
  room_type_tbl{
   room_type_id,
   room_type_name,
   hotel_id 
  }
  
  
  room_rates_tbl{
    room_type_id,
    **from_date,
    **till_date,
    meal_basis,//(Breakfast,halfboard,fullboard) BB,HB,FB
    sleeps,//(Single,Double,Triple) SGL,DBL,TPL
    currency,
    room_rate
  }
  </code></pre>
  
  <p>As the hotel rates fluctuate by date i noticed when I query the table for complex tour package calculations(involving multiple hotels) the performance is slow than expected. My question is do you think the performance can improve if I have a rate row for each date instead of using a date range (my system contains around 150 hotels) something like below:</p>
  
  <pre><code>room_rates_tbl { // A row for each day as opposed to using a date range
    room_type_id,
    **date,
    meal_basis,//(Breakfast,halfboard,fullboard) BB,HB,FB
    sleeps,//(Single,Double,Triple) SGL,DBL,TPL
    currency,
    room_rate}
  </code></pre>
  
  <p>Hope the question is clear enough... </p>
  
  <p>I have updated my question to make it more clear. Room Type is for example "Standard Room" or "Deluxe Room" or "Family Room", Sleeps would contain whether its a Single,Double Etc. I have removed the market_type from the question as it is not relevant it was meant for market group(such as countries) the rate is targarted at. My question wether was storing rates for date can be more efficient than using date ranges when it comes to quering the rates table.</p>