Hello everybody!
I’m learning yii and trying to develop a website for hotel booking (not room booking of a hotel). In my case we offer many rooms in many different hotels.
Now I’m at step of designing a table named tbl_reservation for reservation.
This is my sql script for creating the table:
CREATE TABLE 'hotelbooking'.'tbl_reservation' (
'reservation_id' INT UNSIGNED NOT NULL AUTO_INCREMENT ,
'reservation_user_id' INT(10) UNSIGNED NULL COMMENT 'to book the customer must first register an account',
'reservation_hotel_id' INT(5) UNSIGNED NULL ,
'reservation_room_type_id' TINYINT(2) UNSIGNED NULL ,
'reservation_room_quantity' TINYINT NULL COMMENT 'The number of unit that customer wanna order',
'reservation_checkin_date' DATE NOT NULL ,
'reservation_checkout_date' DATE NOT NULL ,
'reservation_arrive_date' DATE NULL ,
'reservation_depart_date' DATE NULL ,
'reservation_booking_time' TIMESTAMP NULL COMMENT 'In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037.' ,
'reservation_customer_firstname' VARCHAR(45) NOT NULL ,
'reservation_customer_lastname' VARCHAR(45) NOT NULL ,
'reservation_customer_email' VARCHAR(45) NOT NULL ,
'reservation_customer_street_adress' VARCHAR(255) NULL ,
'reservation_customer_postal_code' VARCHAR(45) NULL ,
'reservation_customer_city' VARCHAR(45) NULL ,
'reservation_customer_country_id' INT(3) UNSIGNED NULL ,
'reservation_customer_telephone' TINYTEXT NULL ,
'reservation_bed_type' TINYINT(1) UNSIGNED NULL COMMENT '1 - one double bed, 2 - two sigle beds' ,
'reservation_smoking_preference' TINYINT(1) NULL COMMENT '1 - smoking, 0 - non-smoking' ,
'reservation_view_type' TINYINT(2) NULL DEFAULT 0 COMMENT '0 - not important, 1 - sea view, 2 - mountain view, 3 - city view' ,
'reservation_customer_additional_requirement' MEDIUMTEXT NULL ,
PRIMARY KEY ('reservation_id') ,
INDEX 'FK_reservation_user_id_idx' ('reservation_user_id' ASC) ,
INDEX 'FK_reservation_hotel_id_idx' ('reservation_hotel_id' ASC) ,
INDEX 'FK_reservation_country_id_idx' ('reservation_customer_country_id' ASC) ,
CONSTRAINT 'FK_reservation_user_id'
FOREIGN KEY ('reservation_user_id' )
REFERENCES 'hotelbooking'.'tbl_user' ('user_id' )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT 'FK_reservation_room_type_id'
FOREIGN KEY ('reservation_room_type_id' )
REFERENCES 'hotelbooking'.'tbl_room_type' ('room_type_id' )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'FK_reservation_hotel_id'
FOREIGN KEY ('reservation_hotel_id' )
REFERENCES 'hotelbooking'.'tbl_hotel' ('hotel_id' )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'FK_reservation_customer_country_id'
FOREIGN KEY ('reservation_customer_country_id' )
REFERENCES 'hotelbooking'.'tbl_country' ('country_id' )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
COMMENT = 'Specifies a reservation';
Now you can see that I have a column named reservation_room_quantity. It’s used to store the number of unit, that the customer wanna order. Let’s say customer wants 3 rooms.
Now is the point:
Suppose that the customer wants 3 rooms. I want to give him/her the ability to chose some features such as: smoking preferences, landscape view (sea view, city view,…), bed type (single or double), for each room in particular. Off course I can’t create more columns to store this information because I don’t know how many rooms the customer is going to order.
Another problem the reservation record could hold information about a particular type of room (deluxe, standard,…). What if the customer wants to have let’s say 1 deluxe suite room and two standard rooms?
Off course the customer wants to fill all needed information just in one go. So I think using 3 records to hold the information is not a good idea.
Sorry me to let you read lots of words.
But if you have ideas or suggestions just let me know.
Anyway thank you for reading my post.
Cheers!