Design a db for hotel booking

Hi,

Say a travel agency make block book for some rooms in a hotel
The agents will assign these rooms (just set status to assign)

Problem:
How to design the DB WHEN the block booking date is FROM 01/06/2003 to 30/06/2003
and room assigned to agent is FROM 02/06/2003 to 04/06/2003?

The same room will be available on 01/06/2003 and 05-30/06/2003.

How do check the availablity of the rooms now?

I am currently saving the block booking for per room together with the book from and book to date as one record in room table.

The assignment by agents are saved as room_no, assigned_date_from, assigned_date_to as assignment table.

Note the room_no is not the pk of room table. It is the number on the doorplate of a room.

Doing things the above way will save on no of rows but it is difficult to get data. If I would to save each day as individual row. There will be a lot of rows.

Thanks

I begin to think that for the ease and convient, I am better off inserting room per day per record instead of room per block date per records. Any one has got similar experiences?

hi amber what do you meant by block booking : "WHEN the block booking date is FROM 01/06/2003 to 30/06/2003
and room assigned to agent is FROM 02/06/2003 to 04/06/2003? " So have you decided on the solution please let me know because I am designing hotel database too(starting to) stil got no clue. cheers

Hi,
I put that idea on hold. Now working on other projects.
Well, block booking means to block a room for a period say from 1st of June to 31st of June by the tour agency. A tour guide may then book the room for a period within the timeframe of the agency booking? Got it?
As for the solution, sorry no solution at the moment. How about searching for a similar hotel booking application.
You can sneak a peep at their database design.
Try sourceforge.net
There are a lot of open source projects.

Regards,
amber

thanks for the reply