How to build this in an ER diagram?

Dev Shed Forums Sponsor:

September 12th, 2011, 10:04 AM

luismartin

Registered User

Join Date: Sep 2011

Posts: 6

Time spent in forums: 1 h 11 m 29 sec
Reputation 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.

September 12th, 2011, 04:16 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

September 12th, 2011, 05:11 PM

luismartin

Registered User

Join Date: Sep 2011

Posts: 6

Time spent in forums: 1 h 11 m 29 sec
Reputation 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.

September 12th, 2011, 06:01 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

Quote:

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.

September 13th, 2011, 02:17 AM

luismartin

Registered User

Join Date: Sep 2011

Posts: 6

Time spent in forums: 1 h 11 m 29 sec
Reputation 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.

September 13th, 2011, 03:32 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

Quote:

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

September 13th, 2011, 04:56 AM

luismartin

Registered User

Join Date: Sep 2011

Posts: 6

Time spent in forums: 1 h 11 m 29 sec
Reputation 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?

September 13th, 2011, 05:49 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

it's related via the date

September 13th, 2011, 11:11 AM

luismartin

Registered User

Join Date: Sep 2011

Posts: 6

Time spent in forums: 1 h 11 m 29 sec
Reputation 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.