Hi,
1) General discription:
I have a problem translating what I want to do in to database_tabels.
I make contracts for a Tour Operator with hotels and I want to manage the stock of hotelrooms I contracted from those hotels.
This stock of rooms stay at my disposal until a specified date.
2) The environnement is as follows:
tabel 1: Hotel identity
product_id = hotel xxx
tabel 2: Room types within the hotel
product_id = hotel xxx
room_id= types of rooms
So in tabel 2 I can have a product_id with several room_id's.
In human language this means I have a hotel with several types of rooms.
3) Problem is to manage the stock within a date context:
The hotels are open 365 days a year.
I have 2000 hotels
A hotel has an average of 5 to 10 different types of rooms.
An example:
I have every day from the 01/01/03 until 30/06/03 a stock of 10 units of type_1 and 3 units of type_2, from ...ect...
A unit can be sold per day.
So if I sell 1 unit for type_1 on the 01/02/03 for 1 day the stock is a follows:
01/01/03-31/01/03: 10
01/02/03-01/02/03: 9
02/02/03-30/06/03: 10
3) Question:
How do you manage such a thing ?
For sure not creating a tabel with 365 fields for every day for the year and a matching stock per day per room ?
With 2000 hotels that have each 10 types of rooms, this would be madness.
Does any body have any suggestions ?
I hope a discribed the problem clearly. If not let me know.
Thanks in advance,
Gijs
Problem: stock management in a date determined environment
Moderator: General Moderators
You would store when a room is booked out (start and end dates), when someone requests a room in a particular hotel your query would just check if that room type had been booked already between the dates requested. The number of rooms returned that have been booked already could then be subtracted from the total number of rooms.
Say I want a room type 1 on 1/2/03, so query runs to find all rooms already booked out on this date, lets say it returns 2. You know your total stock of room type 1 is 10, therefore 8 left and it would let me book a room.
Hope this helps
Say I want a room type 1 on 1/2/03, so query runs to find all rooms already booked out on this date, lets say it returns 2. You know your total stock of room type 1 is 10, therefore 8 left and it would let me book a room.
Hope this helps
I think I expressed myself poorly
Thanks for you're reply,
It is not really what I'm trying to ask.
The query seems perfectly logical to me, but the tabel structure isn't.
If you f.e. would have the following tabel structure
hotel_id
room_id
period_start
period_end
number_of_rooms
With f.e. the following records
Record_1
hotel_id= 235
room_id= 1
period_start= 01-01-03
period_end= 30-06-03
number_of_rooms= 10
Record_2
hotel_id= 235
room_id= 1
period_start= 01-07-03
period_end= 31-12-03
number_of_rooms= 10
Now, if you query on this to find Room Type 1 for date 01/04/03, it wil return OK, you can book it.
Once booked you have to subtract the 1 room for the first of april.
But as the period spans 1/1-30/6, how can you parse this period into 3 new periods ? In casu 1/1-31/3 with 10 rooms, 1/4-1/4 with 9 rooms and the period 2/4-30/6 with 10 rooms ?
Is this possible to perform with an update query or is the database tabel structure bogus ?
Any one ?
Thanks in advance,
Greetings from Belgium and Goodnight to you all !
Gijs
But how does the database content will switch to a state that it becomes
It is not really what I'm trying to ask.
The query seems perfectly logical to me, but the tabel structure isn't.
If you f.e. would have the following tabel structure
hotel_id
room_id
period_start
period_end
number_of_rooms
With f.e. the following records
Record_1
hotel_id= 235
room_id= 1
period_start= 01-01-03
period_end= 30-06-03
number_of_rooms= 10
Record_2
hotel_id= 235
room_id= 1
period_start= 01-07-03
period_end= 31-12-03
number_of_rooms= 10
Now, if you query on this to find Room Type 1 for date 01/04/03, it wil return OK, you can book it.
Once booked you have to subtract the 1 room for the first of april.
But as the period spans 1/1-30/6, how can you parse this period into 3 new periods ? In casu 1/1-31/3 with 10 rooms, 1/4-1/4 with 9 rooms and the period 2/4-30/6 with 10 rooms ?
Is this possible to perform with an update query or is the database tabel structure bogus ?
Any one ?
Thanks in advance,
Greetings from Belgium and Goodnight to you all !
Gijs
But how does the database content will switch to a state that it becomes
What I was trying to get at is you need a second table storing the dates the actual room is booked out, so in that second table you would store
table: ROOM_BOOKING
hotel_id|room_id|date_booked
235|1|01/04/03
so your query would be something like
select *
from hotel h, room_booking r
where h.hotel_id = r.hotel_id and
h.room_id = r.room_id and
h.period_start <= '$DateIWantToBook' and
'$DateIWantToBook' <= h.period_end and
'$DateIWantToBook' <> r.date_booked;
Thats not the correct query (I don't think
) but hopefully you understand what I mean.
I think this would be easier than trying to adjust/split period start and period end in your original table, think about deletions and updates how complex would they become with your intended structure, nightmare
table: ROOM_BOOKING
hotel_id|room_id|date_booked
235|1|01/04/03
so your query would be something like
select *
from hotel h, room_booking r
where h.hotel_id = r.hotel_id and
h.room_id = r.room_id and
h.period_start <= '$DateIWantToBook' and
'$DateIWantToBook' <= h.period_end and
'$DateIWantToBook' <> r.date_booked;
Thats not the correct query (I don't think
I think this would be easier than trying to adjust/split period start and period end in your original table, think about deletions and updates how complex would they become with your intended structure, nightmare
Thanks mikeq,
You've trown some light into the darkness !
My initial idea was worthless, I know. That why I needed advice.
Problem is I haven't told you the full story!
Stock manipulations are not only booking rooms from the initial stock.
There are other manipulations to be performed:
1) Adding stock: a hotel can give you supplementary stock for specifick dates and room types
2) Removing stock: a hotel can demand the whole or partial stock back for specific dates and room types
3) release dates: at a certain date you automaticly give the non-sold stock back to the hotel.
Any way you turn it it's enormous volume of data to be stored an manipulated.
Never the less, your idea of using multiple tables is probebly the solution.
I'm thinking about the following database structure:
Tabel 1: INITIAL_STOCK
hotel_id|room_id|period_start|period_end|initial_units
This tabel would be used for:
a) knowing the initial stock
b) entering the initial stock data
Tabel 2: STOCK_TO_SELL
hotel_id|room_id|day|units_to_sell
This tabel would be used for:
a) storing the data of the INITIAL_STOCK by splitting the periods up into days
b) this tabel will be manipulated by 3 other tables to keep the selling stock up-to-date
Consequence: you'll be having a tabel of about 3,65 mio records !
( 1000 hotels x 10 room_types x 365 days)
Solution: making several of these tabels for productgroups
I could, f.e. make one of these tabel per region with would dived thenumber of recors by 6.
Tabel 3: ROOMS_BOOKED
hotel_id|room_id|period_start|period_end|units_booked
This tabel would be used for:
Store the data of a room that has been booked
Update the STOCK_TO_SELL table
Tabel 4: ADD_STOCK
hotel_id|room_id|period_start|period_end|units_added
This tabel would be used for:
Store the data of rooms that have been added
Update the STOCK_TO_SELL table
Tabel 5: REMOVE_STOCK
hotel_id|room_id|period_start|period_end|units_removed
This tabel would be used for:
Store the data of rooms that have been removed
Update the STOCK_TO_SELL table
Tabel 6: RELEASE STOCK
hotel_id|room_id|period_start|period_end|units_release
This tabel would be used for:
Update the stock to 0 for the periods and rooms that have been release back to the hotel
Advantage of of this method would be the possibility to store date and time of every stock manipulation in one of those last 4 tabels.
A) What do you think about this?
B) Is a datatabel with 3,65 mio records workable or not ?
C) Is a volume of 600.000 records workable ?
Finally, could you give me a hint on how to seperate periods into days ?
I mean, period_start and period_end splitting from the INITIAL_STOCK to days into the SELLING_STOCK ?
Thanks again !
Gijs
You've trown some light into the darkness !
My initial idea was worthless, I know. That why I needed advice.
Problem is I haven't told you the full story!
Stock manipulations are not only booking rooms from the initial stock.
There are other manipulations to be performed:
1) Adding stock: a hotel can give you supplementary stock for specifick dates and room types
2) Removing stock: a hotel can demand the whole or partial stock back for specific dates and room types
3) release dates: at a certain date you automaticly give the non-sold stock back to the hotel.
Any way you turn it it's enormous volume of data to be stored an manipulated.
Never the less, your idea of using multiple tables is probebly the solution.
I'm thinking about the following database structure:
Tabel 1: INITIAL_STOCK
hotel_id|room_id|period_start|period_end|initial_units
This tabel would be used for:
a) knowing the initial stock
b) entering the initial stock data
Tabel 2: STOCK_TO_SELL
hotel_id|room_id|day|units_to_sell
This tabel would be used for:
a) storing the data of the INITIAL_STOCK by splitting the periods up into days
b) this tabel will be manipulated by 3 other tables to keep the selling stock up-to-date
Consequence: you'll be having a tabel of about 3,65 mio records !
( 1000 hotels x 10 room_types x 365 days)
Solution: making several of these tabels for productgroups
I could, f.e. make one of these tabel per region with would dived thenumber of recors by 6.
Tabel 3: ROOMS_BOOKED
hotel_id|room_id|period_start|period_end|units_booked
This tabel would be used for:
Store the data of a room that has been booked
Update the STOCK_TO_SELL table
Tabel 4: ADD_STOCK
hotel_id|room_id|period_start|period_end|units_added
This tabel would be used for:
Store the data of rooms that have been added
Update the STOCK_TO_SELL table
Tabel 5: REMOVE_STOCK
hotel_id|room_id|period_start|period_end|units_removed
This tabel would be used for:
Store the data of rooms that have been removed
Update the STOCK_TO_SELL table
Tabel 6: RELEASE STOCK
hotel_id|room_id|period_start|period_end|units_release
This tabel would be used for:
Update the stock to 0 for the periods and rooms that have been release back to the hotel
Advantage of of this method would be the possibility to store date and time of every stock manipulation in one of those last 4 tabels.
A) What do you think about this?
B) Is a datatabel with 3,65 mio records workable or not ?
C) Is a volume of 600.000 records workable ?
Finally, could you give me a hint on how to seperate periods into days ?
I mean, period_start and period_end splitting from the INITIAL_STOCK to days into the SELLING_STOCK ?
Thanks again !
Gijs