Hotel Reservation DB Structure
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.