#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0

    How to build this in an ER diagram?


    I'm making a booking application for a bed & breakfast, and my intention is to reuse it for any hotel.

    I've got everything tied, except for the way to handle the pricing per room and its relationship with the "rooms" entity. That is to say, a room has different prices depending on season, festivities and other events, and any day may vary along the year too.

    I've got 4 main entities: guests, related to bookings, which is related to rooms by means of another entity called bookings-rooms. I would add an image of the ERD but I'm not allowed to do that because I'm a new user.

    I'm not sure how to handle the pricing. I've made an entity called "calendar-prices", but I'm not sure if I would need one or more entities for it.

    Any suggestion would be greatly appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    calendar_prices sounds right

    there would be a start date, and end date, and a price

    and maybe different prices for different room types

    this table would be related to the bookings_room entity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    Thanks a lot for your reply!

    I still have a question about your approach:

    what if the price of the room varies between the start and end date? For instance, a booking which is made halfway between high and low season.

    And suppose someone makes a reservation one year in advance, at a reasonable cheap price, and another one makes a reservation for the same type of room very near the booking date and we previoulsy opted to rise its price.

    I want to prevent the pricing system from flaws at all costs.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by luismartin
    what if the price of the room varies between the start and end date?
    it wouldn't

    the start and end dates define a single price for that date range

    if the price changes, you add another row, with the new price for the new date range

    yes, this means your pricing table doesn't have a generic price for "december through february" but instead has one row for 2011-12-01 through 2012-02-29, then another row for the year after that, and another row for the year after that, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    In that case,

    if for instance I previously set 40 for a room in table calendar-prices this way (peak season):

    start_date : 2012-07-01
    end_date : 2012-08-31
    price : 40

    and later I want to increase its price to 45 for the last week of July and first of August (23rd of July to 5th of August):

    The new record would be

    start_date : 2012-07-23
    end_date : 2012-08-05
    price : 45

    So, should I instead search for any matching date (the previous one in this case), delete it, and rearrange it like this?:

    Code:
    start_date         end_date           price
    -----------------------------------------
    2012-07-01       2012-07-22        40
    2012-07-23       2012-08-05        45
    2012-08-06       2012-08-31        40
    or would it be better to leave any record as it is and somehow calculate the price for any day according to its higher ID or creation date?

    I'm still a bit confused about how to do it.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by luismartin
    So, should I instead search for any matching date (the previous one in this case), delete it, and rearrange it like this?:

    Code:
    start_date         end_date           price
    -----------------------------------------
    2012-07-01       2012-07-22        40
    2012-07-23       2012-08-05        45
    2012-08-06       2012-08-31        40
    yes, like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    OK, thank you! I needed somebody to help me assert which is the best solution.

    Looking into both approaches:

    - The first one (the one you chose) would represent more queries and calculations everytime a price was changed, in order to rearrange the date ranges and their prices, and it would be quicker in the frontend.

    - The second one wouldn't take more than one insertion whenever I changed a room price in the backend, but a bit more complex SELECT query would be needed in the frontend.

    On the whole I think the first one would represent more operations (including SQL + code), but I suppose it is better to delegate complexities to the backend in order to improve performance in the frontend, so I'll take your advice.

    Anyway if there is anything more to say to this, I'm open to it.

    By the way I forgot to ask this:
    Why relate this table calendar-prices to bookings-rooms? I think it should be related to table rooms, shouldn't it?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    it's related via the date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    Excuse my insistence, but I'm not sure that a date range for prices must be directly related to bookings-rooms:

    Check out this example:

    Table rooms:

    Code:
    room_id        name
    --------------------
    1           single room
    2           double room
    Table bookings-rooms:

    Code:
    booking_id    room_id         num_persons        price (not necessary)
    ------------------------------------------------------------
    1                2                 2            30 x 7 (June) + 35 x 2 (July) = 280 x 2 pers = 560
    
    1                1                 1           35 x 7 (June) + 40 x 2 (July) = 325 x 1 pers = 325
    Table bookings:

    Code:
    booking_id    guest_id       checkin_date      checkout_date        total_price
    -------------------------------------------------------------------------
    1                1         2012-06-25          2012-07-02         560 + 325 =  885

    Table calendar-prices:

    Code:
    start_date         end_date           price
    -----------------------------------------
    2012-01-01       2012-06-30        35
    2012-07-01       2012-07-22        40
    2012-07-23       2012-08-05        45
    2012-08-06       2012-08-31        40
    Calendar-prices would be independent from any booking. That is, I should already have the room prices specified for the next months, but it doesn't mean they all have a reservation, so I can't see the point in relating it to booking-rooms. I would add instead a foreign key called room_id pointing to table rooms. I'm not sure if I'm right, but I can't see your point. Thus table calendar-prices would be similar to this:

    Code:
    start_date         end_date           price*         room_id
    ------------------------------------------------------------
    2012-01-01          2012-06-30           35               1
    2012-07-01          2012-08-31           40               1
    2012-01-01          2012-06-30           30               2
    2012-07-01          2012-07-22           35               2
    2012-07-23          2012-08-05           45               2
    2012-08-06          2012-08-31           40               2
    * price per person per night


    I think this way would work.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by luismartin
    Excuse my insistence, but I'm not sure that a date range for prices must be directly related to bookings-rooms:
    okay, you're right

    try should be instead of must be

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    6
    Rep Power
    0
    Thank you!

    I'll do it finally this way.

IMN logo majestic logo threadwatch logo seochat tools logo