A Concept
Moderator: General Moderators
A Concept
Hi all,
I am creating a website which will allow visitors to search and book their hotels.It's like a http://www.makemytrip.com,www.starasiatravel.com or any other travel/hotel sites which will allow users to book their hotels online.
i know, to make users to searh/book their hotel a hotel should be added in the database.Adding the hotel in database is no problem. But i encountered a problem while adding rates for hotel.It is not any programming Problem,but it is a concept ual problem. Please tell me how can i add hotel rates for at least one year in a convenient way.
Let me,expalin my problem more clearly.Please visit http://www.makemytrip.com and search for some hotels,it will display the hotels based on ur checkin and check out dates along with room types and hotel rates.Now, the hotel rates will be based on your check in and check out dates. For example if your checkin and checkout date is jan 14 2006,jan 20 2006 respectively then the rate from jan 14 2006 to jan 20 2006 will be displayed.Likewise if your date is jan 20 to jan 22 then the rate from jan 20 to jan 22 will be displayed.
I went through various websites and found that based on check in date and check out date the rates for hotels will be dispalayed. On http://www.ratestogo.com,msn,yahoo similar kind of work can be viewed.Now my problem is adding hotel rates in an efficient manner for at least one year.I know adding rates in database is not so difficult job.But my problem is adding the bulk rates.The person who is adding hotel will be get annoyed if he/she has to add rate for 365 times.
I am trying to make it more clear.Let's suppose,while adding hotelrates if there is two fields A and B. A allows to enter a particular date and B allows to Enter hotel rate.Now the problem is adding hotel rates for one year.The person who will add hotel rate has to enter the rate for 365 times. The hotel rate will be different on different dates.some times it will be more than the previous rate or sometimes the rate will be less than the previous one.For this reason i can't use loop in anyway.
Please,Suggest me how can i solve this problem.I don't need any programming code or database code. I just need a concept.
Thanx in advance.
I am creating a website which will allow visitors to search and book their hotels.It's like a http://www.makemytrip.com,www.starasiatravel.com or any other travel/hotel sites which will allow users to book their hotels online.
i know, to make users to searh/book their hotel a hotel should be added in the database.Adding the hotel in database is no problem. But i encountered a problem while adding rates for hotel.It is not any programming Problem,but it is a concept ual problem. Please tell me how can i add hotel rates for at least one year in a convenient way.
Let me,expalin my problem more clearly.Please visit http://www.makemytrip.com and search for some hotels,it will display the hotels based on ur checkin and check out dates along with room types and hotel rates.Now, the hotel rates will be based on your check in and check out dates. For example if your checkin and checkout date is jan 14 2006,jan 20 2006 respectively then the rate from jan 14 2006 to jan 20 2006 will be displayed.Likewise if your date is jan 20 to jan 22 then the rate from jan 20 to jan 22 will be displayed.
I went through various websites and found that based on check in date and check out date the rates for hotels will be dispalayed. On http://www.ratestogo.com,msn,yahoo similar kind of work can be viewed.Now my problem is adding hotel rates in an efficient manner for at least one year.I know adding rates in database is not so difficult job.But my problem is adding the bulk rates.The person who is adding hotel will be get annoyed if he/she has to add rate for 365 times.
I am trying to make it more clear.Let's suppose,while adding hotelrates if there is two fields A and B. A allows to enter a particular date and B allows to Enter hotel rate.Now the problem is adding hotel rates for one year.The person who will add hotel rate has to enter the rate for 365 times. The hotel rate will be different on different dates.some times it will be more than the previous rate or sometimes the rate will be less than the previous one.For this reason i can't use loop in anyway.
Please,Suggest me how can i solve this problem.I don't need any programming code or database code. I just need a concept.
Thanx in advance.
Last edited by namitjung on Sat Jan 14, 2006 4:35 am, edited 1 time in total.
plz help me out
it's been so long. 11 person view this topic and no body reply.plz help me to solve this problem
- shiznatix
- DevNet Master
- Posts: 2745
- Joined: Tue Dec 28, 2004 5:57 pm
- Location: Tallinn, Estonia
- Contact:
first, you must wait 24 hours before bumping. nobody here is required to help anyone so don't take it for granted.
what i think you might need to do is just have the extra rates in the database and have a default rate set. more understandable:
- default rate is 25 dollars a night
- you want from november 12 - december 12 the rate to be 50 dollars
- put that in the database with the start and stop dates for the higher rate
- check if the users desired booking dates fall in that category
- if so, calculate how many days are in that category for the higher rate
- display the remaing days as the default rate then the days in the higher rate
did that make sence?
what i think you might need to do is just have the extra rates in the database and have a default rate set. more understandable:
- default rate is 25 dollars a night
- you want from november 12 - december 12 the rate to be 50 dollars
- put that in the database with the start and stop dates for the higher rate
- check if the users desired booking dates fall in that category
- if so, calculate how many days are in that category for the higher rate
- display the remaing days as the default rate then the days in the higher rate
did that make sence?
i didn't get it properly
I didn't get your idea properly
.If u have time can u explain it to me more clearly....
Thank You
Thank You
No need to loop, you can use nested periods of time.
So timeline representation of your database might look like this:
You will need to prevent insertion of overlapping dates, though.
Code: Select all
SELECT rate FROM rates
WHERE begins < timestamp_for_december_22
AND ends > timestamp_for_december_22
SORT BY begins ASC
LIMIT 1Code: Select all
[rate1begins-------[rate2begins-----rate2ends]-----rate1ends]Code: Select all
bad:
[rate1begins-------[rate2begins-------rate1ends]---rate2ends]- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
let's say you have special rates for a period of time or for a day
SpecialRates_tbl
from_date to_date rate
30/10/2006 30/10/2006 50
11/11/2006 28/11/2006 75
You can use this table to fill in special rates
Store the standard rate somewhere, normal_rate = 40
Now develop an admin page,
1. create two fields, from_date, to_date
2. if the special rate is for only one day, give option, "use from_date for to_date", a button that
autofills from_date value into to_date text field.
3. While adding any special rate, check whether that date is already within the range of from_date to
to_date of any record so overlapping is avoided
Now coming to logic of booking hotel:
INPUT: get check in and check out dates.
(Note: Values in SpecialRates_tbl defined above is used)
1. If check in date is not within any from_date of any record but check out date falls within the
range of one of the records, do the following
ex: 8/11/2006 - 12/11/2006
a)get from_date for this record and calculate special rate = (check out date - from_date) *
special_rate
b)standard rate = (from_date - check in date) * standard rate
c)Now add both of them
2. If check in date is in one of the ranges but check out date is not within any to_date of any
record, do the following
ex: 26/11/2006 - 30/11/2006
a)get to_date for this record and calculate special rate = (to_date - check in date) * special_rate
b)standard rate = (check out date - to_date) * standard rate
c)Now add both of them
3. Now if the check in and check out dates are within the range of some record, do the following
ex: 15/11/2006 - 18/11/2006
a) get special rate
b) (Check out date - Check in date) * special rate
4. If check in and check out dates both do not fall in one of the ranges, do
ex: 6/11/2006 - 9/11/2006
a) get standard rate
b) (checkout date - check in date) * standard rate
SpecialRates_tbl
from_date to_date rate
30/10/2006 30/10/2006 50
11/11/2006 28/11/2006 75
You can use this table to fill in special rates
Store the standard rate somewhere, normal_rate = 40
Now develop an admin page,
1. create two fields, from_date, to_date
2. if the special rate is for only one day, give option, "use from_date for to_date", a button that
autofills from_date value into to_date text field.
3. While adding any special rate, check whether that date is already within the range of from_date to
to_date of any record so overlapping is avoided
Now coming to logic of booking hotel:
INPUT: get check in and check out dates.
(Note: Values in SpecialRates_tbl defined above is used)
1. If check in date is not within any from_date of any record but check out date falls within the
range of one of the records, do the following
ex: 8/11/2006 - 12/11/2006
a)get from_date for this record and calculate special rate = (check out date - from_date) *
special_rate
b)standard rate = (from_date - check in date) * standard rate
c)Now add both of them
2. If check in date is in one of the ranges but check out date is not within any to_date of any
record, do the following
ex: 26/11/2006 - 30/11/2006
a)get to_date for this record and calculate special rate = (to_date - check in date) * special_rate
b)standard rate = (check out date - to_date) * standard rate
c)Now add both of them
3. Now if the check in and check out dates are within the range of some record, do the following
ex: 15/11/2006 - 18/11/2006
a) get special rate
b) (Check out date - Check in date) * special rate
4. If check in and check out dates both do not fall in one of the ranges, do
ex: 6/11/2006 - 9/11/2006
a) get standard rate
b) (checkout date - check in date) * standard rate
[quote="Gambler"]No need to loop, you can use nested periods of time.
The LIMIT has to be useless per definition
No one wants to define two rates to the same day 
Anyway, presume that someone books from dayA until dayB (preferably with a change of the rate in the period) How would your query look like?
Code: Select all
SELECT rate FROM rates
WHERE begins < timestamp_for_december_22
AND ends > timestamp_for_december_22
SORT BY begins ASC
LIMIT 1Anyway, presume that someone books from dayA until dayB (preferably with a change of the rate in the period) How would your query look like?
I've considerd this approach too, but this way you have to add for every year new pairs (period - rate).raghavan20 wrote:let's say you have special rates for a period of time or for a day
SpecialRates_tbl
from_date to_date rate
30/10/2006 30/10/2006 50
A simple dayofyear - rate (or day , month - rate) could remain valid (unmanaged) until something changes..
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
timvw is right but by any chance you have the doubt that rates would persist for the coming year as well...you can always extend the same values for the next year by executing this statement...
Code: Select all
mysql> select date(sysdate());
+-----------------+
| date(sysdate()) |
+-----------------+
| 2006-01-15 |
+-----------------+
1 row in set (0.00 sec)
mysql> select date(sysdate()) + interval 1 year;
+-----------------------------------+
| date(sysdate()) + interval 1 year |
+-----------------------------------+
| 2007-01-15 |
+-----------------------------------+
1 row in set (0.00 sec)I think you need one table for normal booking i.e in which no special events are there for atleast one month. Say you have standard price for booking on the spot say 100$(hoping this as the maximum). From this you calculate the Rate of the hotel based on the rating of the Hotel and the number of the days from today to the till date he reserves.
Next one More table for special events in the year . This table also should have the same concept as above.
Hope I Could make my idea clear there...
Next one More table for special events in the year . This table also should have the same concept as above.
Hope I Could make my idea clear there...
Anyway, presume that someone books from dayA until dayB (preferably with a change of the rate in the period) How would your query look like?
Code: Select all
SELECT rate FROM rates
WHERE begins < dayA
AND ends > dayB
SORT BY begins ASCSimple bit of advice: FIND SOMEONE IN THE TOURISM INDUSTRY AND TALK TO THEM.
I've written two hotel room booking applications, and I can assure you that this is NOT simply a case of having one price per room per night. If you have a year of rates stored in your database you're going to need a hell of a lot more data than simply 365 prices. There's the rack rate (standard room charge), discount rates (people very, very rarely pay the standard price for a room), weekend rates, long weekend rates, seasonal changes, single occupancy charge, special event bundle rates, and stacks more. The front end booking system for the user is really, really simple compared to what you need to create for the hotel staff to admin everything.
I've written two hotel room booking applications, and I can assure you that this is NOT simply a case of having one price per room per night. If you have a year of rates stored in your database you're going to need a hell of a lot more data than simply 365 prices. There's the rack rate (standard room charge), discount rates (people very, very rarely pay the standard price for a room), weekend rates, long weekend rates, seasonal changes, single occupancy charge, special event bundle rates, and stacks more. The front end booking system for the user is really, really simple compared to what you need to create for the hotel staff to admin everything.