Design Table Tbl_Reservation For Hotel Reservation Project

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!

I think you need a separate record for each room rather than just a room count. This shouldn’t be too much of an obstacle for development. I imagine you could have a two stage process, where on the first page the user provides details and the number of rooms required, then on the next page they can customise the type of room and options for each room.

Agree with Keith,

you should really manage separately : users, rooms, orders …

That way, you’ll have an user_id, room_id, orders_id with general details, and another table with FK INT’s for order_id - user_id - room_id / order_id - user_id - room_id/…

Than have fun with JOIN - COUNT - etc

Hope it helps a litle

Hi guys!

Thank you for your help!