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 ?