Question

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

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
}

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:

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}

希望问题足够明确......

我已更新了我的问题,使之更加清楚。会议室比如,“Standard Room”或“Delosse Room”或“Family Room”,睡觉会包含其单一、Double Etc。我已经从问题中删除了市场类型,因为这一问题与市场集团(如国家)的意思无关。我的问题是,在提出费率表时,如果采用日期范围,那么迄今的仓储率会比使用日期范围更有成效。

Answer 1

请允许我假设,你的询问是在两个特定日期之间为所有旅馆提供一个特殊类型的房间。你们需要从数据库获得什么? 我建议:

The rate for that type of room for all hotels on the start date
The rate for that type of room for all hotels on the end date

You want avoid table scans, because these slow down your response to the user. So any lookup needs to use an index. Since the query is based on dates, the date field should be indexed. So if the user wants a room between 31/12/12 and 05/01/13, we can use range operators over the index as described in http://dev.mysql.com/doc/refman/5.1/en/range-optimization.html#range-access-single-part). (If you store the date value as a timestamp value, you reduce the storage size of the values, which should improve the performance of the index.) If you use your original table schema, you ll need two indexes - one on start date and one on end date - but fewer rows in the table, compared with the amended table you suggest, which will need one index but more table rows (one for each day etc.) It s too variable to suggest which one will perform better.

Answer 2

我认为,最好把你的日期和计划分开,把比率分成另一个表格。

room_tbl - room_id, room_type_id, market_type_id & whatever room information

会议室_rate_tbl - room_id ,从_date,到_date, 货币, 房间_rate

This way your room information is independent and you can easily add, edit, remove any room rate to a particular room_id.

友情链接