Hotel Reservation DB Structure

[nickcherryjiggz]

Hello there,

I'm pretty rusty with SQL (though I never had a great deal of experience to begin with), and I'm having a lot of trouble with slow search speeds of my database. I was hoping someone could tell me what I'm doing wrong as far as the schema goes.

The main function of this database will be to provide information to construct a calendar and reservation system for a hotel. When the user selects a new set of months (January-February, February-March, etc.), the application will query the database and return the following information:

For each day of the two months, and for both parts of each day (morning, night), the number of vacancies for each type of room (5 or 6 total).

Ex.
Date: 2007-05-22
Morning or Night: Morning
TypeAVacancies: 10, TypeBVacancies: 12, TypeCVacancies: 4, etc.
Date: 2007-05-22
Morning or Night: Night
TypeAVacancies: 15, TypeBVacancies: 4, TypeCVacancies: 8, etc.
Date: 2007-05-23
Morning or Night: Morning
TypeAVacancies: 6, TypeBVacancies: 2, TypeCVacancies: 7, etc.

Here's the current structure of my tables:

(CREATE TABLE Customer(
`CustNum` INT NOT NULL AUTO_INCREMENT,
`FirstName` VARCHAR(20) NOT NULL,
`LastName` VARCHAR(20) NOT NULL,
`Address` VARCHAR(40) NOT NULL,
`City` VARCHAR(30) NOT NULL,
`State` VARCHAR(30) NOT NULL,
`PostalCode` VARCHAR(11) NOT NULL,
`Country` VARCHAR(30) NOT NULL,
`Email` VARCHAR(50) NOT NULL,
`Phone` VARCHAR(20) NOT NULL,
`Fax` VARCHAR(20),
`CardHolder` VARCHAR(40) NOT NULL,
`CardNumber` VARCHAR(16) NOT NULL,
`CardExp` VARCHAR(5) NOT NULL,
`ArrivalInfo` VARCHAR(200),
`Comments` VARCHAR(200),
`EmailMe` TINYINT,

PRIMARY KEY(`CustNum`))
)

(CREATE TABLE Room(
`RoomNum` INT NOT NULL,
`Type` VARCHAR(40) NOT NULL,
`PriceBase` FLOAT NOT NULL,
`PricePerAdult` FLOAT NOT NULL,
`PricePerChild` FLOAT NOT NULL,
`PriceWeekendAddition` FLOAT NOT NULL,

PRIMARY KEY(`RoomNum`))
)

(CREATE TABLE Reservation(
`Date` DATE NOT NULL,
`RoomNum` INT NOT NULL,
`CustNum` INT,
`CheckOut` TINYINT NOT NULL,
`Vacant` TINYINT NOT NULL,
`ArrivalInfo` VARCHAR(100),
`Comments` VARCHAR(100),

PRIMARY KEY(`Date`, `RoomNum`, `CheckOut`),
FOREIGN KEY(`RoomNum`) REFERENCES Room(`RoomNum`),
FOREIGN KEY(`CustNum`) REFERENCES Customer(`CustNum`))
)

I'd really appreciate it if someone could lead me in the right direction.

 

[r937]

you should definitely index your foreign keys, as well as other columns involved in your queries in the WHERE and ORDER BY clauses

r937.com | rudy.ca

 

[nickcherryjiggz]

Thanks for the fast response, r937. What exactly do you mean by indexing my foreign keys? RoomNum and CustNum are both integers, and with Date, I'm not exactly sure how I would go about doing that. Or are you just talking about queries with that comment? Sorry if this is a dumb question.

 

[r937]

it is not a dumb question at all, not if you don't know what indexes are

do a web search for "optimizing mysql queries"

to readers of this forum: i'm sorry for recommending a specific product search, but there are a couple or three good articles with that title, and it does appear that nick is using mysql


r937.com | rudy.ca

 

[johnherman]

We need to know what rules define the Type A,B,C Vacancies to determine if the structure is adequate.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[nickcherryjiggz]

I've done some research on optimizing mysql queries (and yes, r937, mysql is what I am using), and I think it's starting to sink in a little bit. I read about an example of indexing surname and firstname, which makes sense to me. I'm having some trouble applying the logic to this particular case since I can't really see any 2 columns going together moreso than others. I still don't quite grasp the concept, but if I were to guess, I would say indexing the date and something else would be the most important part, because the queries that are most important and giving me the most trouble are the ones in which I request all the vacancies over a two month period.

And to johnherman, I just used TypeA, TypeB, TypeC to refer to the different types of hotel rooms available (see Room: type (primary key)). There are only 6 or so different types of hotel rooms. Let's say we're looking for "Standard" type rooms for the night of '2007-05-22'...It would, I think, be determined by counting the number of rows in which Reservation.Date = '2007-05-22', Reservation.CheckOut = 0 (meaning it's at night), Room.type = "Standard".

I'll try to do some more research on indexing, though. Thanks for the help so far everyone!

 

[nickcherryjiggz]

I was just looking over this site:

http://www.tizag.com/mysqlTutorial/mysql-index.php

So, aside from optimizing queries, the indexing is as easy as adding something like this, "INDEX (employeeID)" at the creation of the table?

 

[johnherman]

It would probably be beneficial to build a summary table stating how many rooms of each type are available if no one books a room. This could be recreated daily by reading the Rooms table, but it would be better to have the max number of rooms stored separately (so that we can subtract the bookings to yield the available rooms).

Also, please check out this model from the Library of Free Data Models for additional ideas/suggestions.

http://www.databaseanswers.org/data_models/hotels/index.htm

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright