Database Tables for Reservation site

Question

We are building an Reservation booking system. There are three modules

1. Customers
2. Rooms
3. Bookings

A customer can book one or many rooms and for varying dates. While booking a room(s) i want to search for which rooms are available to be booked between date A and date B.

Tables ( likely solution) customers(id, name, .....) rooms(id, roomNo, roomType, ....) bookings(id, room_id, fromDate, toDate)

Presently i have the tables like this

CREATE TABLE IF NOT EXISTS `bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `checkin` datetime NOT NULL,
  `checkout` datetime NOT NULL,
  `advance` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `booking_details_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `bookings_rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `booking_id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `nationality_id` int(11) NOT NULL,
  `mobile` int(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(4) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Need help to design the database and which is a better approach.

Solution 1

These schema structures already allow you to find the free rooms between two dates.

The following query will check if rooms booked before start-time will be free before start-time or, for those ones booked after, it checks if they're checkin is following end-date too.

select *
from rooms r
where id not in (
        select room_id
        from bookings_rooms br
            join bookings b on (br.booking_id=b.id)
        where (checkin < :dateA and checkout > :dateA)
            or (checkin > :dateA and checkin < :dateB)
    )

Solution 2

1. there is a confusion in the name of foreign keys - booking_id should be logically bookings_id

2. you have for each table duplicate keys - you can remove all booking_details_id etc as they play the role of id

3. customers are not referenced in any way

4. the way you implement rooms - it's fine but all depends on what you do with them. Possibly you could design it in a bit different way so that you can get easily the list of empty rooms (you can get the list of free rooms from this structure, but the performance is not spectacular)

Comments

[Harsha M V]

booking_id naming convention is bcos of the CakePHP framework. What do you thing about the first set of tables i have mentioned ?

[Alpha01]

First, what is the purpose of having id and booking_id in the same table??? The info in the other id is redundant! Apart from that you need to reference the customers somehow in the bookings table. apart from that there are alternative ways of designing the table for rooms. it depends on the typical operations you wish to do with the rooms. For example, you could have a table with dates and all rooms and there you would have flag if booked or not etc etc etc all depends on whatyou require to do with the rooms.

[Alessandro Rossi]

If records from booking table would be deleted as they expire that structure is optimal. Even with 1000000 customers and 1000 rooms that database would no exceed the 100 Mb of used space. Performance would be great also with more data than that.