Availability for Hotels/Motels etc

I'm looking at making a simple reservations system for a an accommodation providor.

It will be for just one place but i'm also looking at using another program that will look for more than on providor.

What I'm stuck with is how to set up my database?

How can I set it up so you can search for a room in dates are see if it is available? would I need to set up a table for each providor and have each room in there? then how would I work with dates and when they are available etc?

What I've thought of so far is this.

Perhaps I would need one table with "rates" in various date ranges (holidays would be more than low season etc), "room type" and "room type count" etc. Then do a search when requested.. this would then say

I'm looking for "Standard Room" I know there are 4 of these the date requested is June 12 - June 14 the rate then is $84 a night and how many rows are there? if there are 3 then I have a free room? if there are 4 then i'm sold out.

Would this work? or what other ways are there of doing this ?

Check first what data you can pull from other systems and how they will receive your data/updates before proceeding with low level design (top to bottom).

Does anyone know if three is a standard format that hotels/motels send/recieve information?

I didn't consider this side of it. It was for a single direct book provider but if I build a system i may wish to sell in future it might as well send/recieve information in the correct format so others can use the providers sales data.

How would I find such an answer if no one knows?

There are many different formats that are sent... (I work for a company the does this kind of work).

Are you able to point me in the right direction? I realise you will not be able to share trade secrets with me but just give me a starting point??

This is more a fun project than a business project.

We have different specs from Hiltion, Holiday Inn.... they supply us what then need to feed their systems and we generate that from our database and send them to theirs.

Is it a simply web service or XML or is it via another system all together?

We send/recive from one customer XML (their format) and another we send CSV for..... All our stuff is generated from a webservice that will gather data from our SQL Server databases and pass to the customer.