Hotel Room Availability Table

Can't get my head around it.

[neophyte]

Hello all -

I've been tasked to build an on-line hotel reservation system for a local resort. So far so good with the design of all necessary tables (MYSQL).

But...the room availability table has got me baffled!

Here's a breif overview of the scenario which is common to all major hotel reservation systems:

1) User clicks reservations link
2) user inputs check-in and check-out dates (to check what rooms are available during the dates input)
3) database is queried for all room categories AVAILABLE within the dates indicated
4) User is served another page showing which room categories are available during those dates.

Steps 1, 2, and 4 are no problem. But I can't figure out how to design a table that would hold all CURRENT reservations for each room in each room category.

Example: there are 9 seperate "standard" rooms in this hotel. Would this mean that I should create 9 seperate "standard room" tables with a month/day/year/room_number matrix and then check each of these 9 tables against the dates the user has input? Yuck!

Anyway, all the major hotels already have this done - and (no doubt) more efficiently/effectively than what I'm suggesting above - but I just feel like I'm getting into water that's way over my head. Not to mention that at my level of experience in db design (not much!), I don't want to re-invent the wheel.

Can anyone guide me in the right direction on how I should construct this table? Or sticky me a URL that has an example of how to accomplish this task?

As always, any assistant GREATLY appreciated!

Neophyte

[aspdaddy]

I wrote a bookings site a while back and had a script for the ownner load availability in at the start of each season. Its basically a form that takes twoi dates and a price per night. Behind is a function that creates day records in the range entered

This allows you to block out maintenanace and private bookings etc.

If a records availability flag is 0, it is available, if 1 its booked. If no record exists its not available and not booked on the system.

[physics]

Example: there are 9 seperate "standard" rooms in this hotel. Would this mean that I should create 9 seperate "standard room" tables with a month/day/year/room_number matrix and then check each of these 9 tables against the dates the user has input? Yuck!

Nope. If you want nice normalized tables I imagine you'd have something like:

table: room_types
id,name,...
1,basic single,...
2,basic double,...
...
7,honeymoon,...
...
table: rooms
id,id_room_type,room_number,smoking,...
1,2,1,0,...
...
table: reservations
id,id_room,date_start,date_end,...
1,1,200600401,20060408,...
...
Then to find free rooms your query would be something like
SELECT rooms.*
FROM rooms LEFT JOIN reservations 
ON reservations.id_room = rooms.id
WHERE date_start > '$date' OR date_end < '$date' 

(where ... indicates more info)

This is not tested or double checked so use at your own risk ;)

[neophyte]

aspdaddy and physics -

Thanks for your guidance. Physics, I understand where you're going with your example and will try it out - sure beats what I was thinking of!

Neophyte

[physics]

Great. Trying to write normalized tables is confusing at first but once you get the idea (seperate every separate type of thing into its own table and link them by id fields) it gets clearer. And hey, it actually works ;) I find it helpful to write all of the tables out on a page and show the links between them like a web...

[neophyte]

Thanks Physics -

I'm diagraming the db now, trying to get into a "normalized" state...and Geez, you're right, it is confusing to me when it comes to implementing foreign keys in order to reference information in a child table.

Example:

I've got a "Room Categories" table. In it, there are 3 fields:

1) "categoryID" tinyint, autoincrement (PK)
2) "roomName" varchar (15)
3) "roomDescription" varchar (255)

Then, I've got a "Room Location" table which holds both the room numbers for each room category (standard, deluxe, family, penthouse) as well as each rooms's location: 1st, 2nd, or 3rd floor.

This table has 3 fields:

1) "locationID" tinyint, autoincremetn (PK)
2) "roomNumber" varchar (2) -- room numbers are alpha/numeric
3) "roomLocation" tinyint
4) "categoryID_FK" (foreign key from the Room Categories table).

This is just a test for me, but what I want to do is query the room categories table for all standard rooms (which would be "1" in the categoryID field), and then draw in (JOIN?) the room number and room location from the Room Location table for each standard room (which would be "1" in the categoryID_FK field).

So, my thought was that the categoryID_FK would act as the index to the room numbers and locations for each category, so I could do a query like this:

select * from Room Categories where categoryID == 1 and from Room Location where categoryID_FK == 1.

I know that syntax won't work, but you see what I'm trying to illustrate.

So...if that is correct, now I'M ASSUMING that I will have to manually input/match the categoryID field from the Room categories table into the categoryID_FK field in the Room Location table.

Is that correct? Gosh, it's even confusing to me when I'm writing this!

If there's a good site that explains how this works, I'd appreciate a sticky, 'cause this is the part that's got me running around in circles!

Neophyte

[physics]

Have a look at this tutourial:
[databasejournal.com...]
It can be difficult to find introductory database design info since a lot of it is higher level. But this looks to be a good intro and other 'tutorial'/'example' style intros would probably be helpful as well.

[neophyte]

thanks Physics. I'll give it a look. Thanks for your patients with me.

Neophyte