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.