0

I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price.

Room Type |  01  |  02  |  03  |  04  |  05  | ... |  31  |
-----------------------------------------------------------
Single    | $100 | $100 | $200 | $200 | $200 | ... | $300 |
-----------------------------------------------------------
Double    | $150 | $150 | $200 | $250 | $350 | ... | $320 |

The best thing that I come accross is :

RoomsAvailable
--------------
 # ID
 room_type
 from_date
 to_date
 price

But, despite this table is flexible with the periodes, it is not with every single day's price.

Thanks in advance.

Improve this question
3
  • I would say that your specification is likely missing some elements such as a periodic calendar of holidays and other special events. Likely the daily rate is changed due to matters like that. (Think of a Spring Break location and how prices might change.)
    – RLF
    Commented May 28, 2015 at 17:05
  • the client will enter the price for every day
    – kali
    Commented May 28, 2015 at 17:26
  • Maybe it would be better to enforce that the client enters the date for every day at the application level as opposed to the database level, if that's what you're trying to accomplish.
    – Joe Borysko
    Commented Jun 2, 2015 at 17:41

1 Answer 1

Reset to default
1

Here's one possibility.

enter image description here

The idea is that every time the price for a room changes, a PricingChange row would be added. From that date on, until there is another PricingChange row for the same RoomType, the price for that RoomType would be set to that row's price.

Sample data for this model:

enter image description here

With this sample data, Single rooms ware set at the price of $100 for June 1 2015, $50 for June 2 2015 to June 4 2015, and then back to $100 from June 5 2015 onwards. Double rooms are set to the price of $200 from June 1 2015 onwards.

Improve this answer
3
  • 1
    Rather than a single date, these sorts of things are ususlly handled by having both a FromDate and a ToDate in the table. The extra UPDATE when the price changes is more than worth it for simpler SELECTs and better indexing.
    – Michael Green
    Commented Jun 2, 2015 at 12:07
  • @MichaelGreen The reason I see having a single date as opposed to a from and to date would be that it prevents storing conflicting data. What scenario would create a complicated select with having one date?
    – Joe Borysko
    Commented Jun 2, 2015 at 17:24
  • The determination of the current price: with a single date it requires a MAX() or EXISTS(). With two dates the check is >= .. AND .. <, which tends to be faster.
    – Michael Green
    Commented Jun 2, 2015 at 23:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.