Forum Moderators: open
I am creating a very simplified hotel booking engine for the first time. As I'm mapping out what data I need to store and what the tables are going to look like in the database, I'm struggling a bit to see what the best way to store this is. Here's what needs to be done:
The administrator should be able to go in and change the pricing and rooms available for any day of the year. They will also need to add/edit the room types for a hotel. So they'll be able to set the price and availability based on the day and room type.
What I'm unlear on is how to store the info (price, availability) corresponding to each day as well as the room types in a sql database? Again, this is the first time Ive tried to create a booking engine, so I'm trying to learn the basics starting with the database organization. Any help is greatly appreciated. Thanks.
Your problem is pretty complex. Room booking is an interesting task, especially over the web, because it's very possible for two or more people to login and start the process, thinking that a room is available.
But that's a whole different issue than just creating the database.
The general rule of thumb in designing the database is "normalization." that means thinking through the layout so that the absolute minimum of data is duplicated across the tables.
I like to do it on paper. BIG paper, with room to sketch and draw arrows.
Start with the basics - Room, price, dates.
One option is to create a table with all the rooms as fields, plus one column for the date. The field data could be the price.
You could have two columns for each room - price and a flag for availability.
OR, create a table with room number, a price, b price, c price.
then create another table with dates and flags for availability.
As always, try to KISS and really, really, really think it through before you start entering data. it's much harder to FIX than it is to start right.
Good luck with it!
Many are more than happy to give you 1 rate for the entire year, although most probably have on-season and off-season rates. Others want different prices for weekday nights and weekend nights. Others want the flexibility to change prices for each specific date, perhaps several times/day, because of more dynamic pricing/availability.
I've flip flopped and changed our system a number of times as we expanded and I learned new things. I'm not happy with the current structure, but the amount of work involved to make it a little better just ain't worth it to me at this point (considering all the other projects wanting to move to the front burner and considering that 6 months from now I may see an even better way to do things :) ).
A quick question about storing dates in this situation. This may seem obvious (i don't know), but would it make sense to have a row for each day of the year with the corresponding information (price, number of rooms available, etc)?
The downside is that we end up with a lot of extra data (if we have 1000 rooms ever day and make 5 reservations, that's 995 "dead" rows). Of course, you can periodically go through and flush out past data.
Thats a really simple start.
You then calculate things from the query results, like number of rooms free on saturday the 1st :)
