Hello and welcome to the Webmaster World!

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!

From someone who has been there/done that, keep in mind that different hotels have different ways they want to change prices. So sometimes you have to trade off what is good for your system vs. what the customer wants.

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 :) ).

Thanks for the replies, they were very helpful. Writing things out on paper is a great idea and allows me to see how involved everything is.

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)?

As I said, I'm not thrilled with the current setup as it has some inefficiency. For each date, we have 1 row for each available room. The row includes the date, hotelID, room type, price, status (available, booked), etc. So if we have 3 rooms from hotel A, the table would have 3 rows for Jan. 1, 3 rows for Jan. 2, etc. So it's easy to search for available dates.

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.

Normally (and I havent sat down and drawn this out) you would have:
- a table Rooms
- a table Dates (and there would have to be an annoying amount of management on this one to get future dates in)
- a table Bookings, which would be a many-to-many on Room-Dates
- in all probability, a table BookingDefaults which holds things like 'sat and sunday at +20%', or that may just be programmatic

Thats a really simple start.
You then calculate things from the query results, like number of rooms free on saturday the 1st :)