Hotel Reservation and Billing System Database Design

Hotel Reservation and Billing System Database Design

Description:

The purpose of this article is to provide you with a sample database design in developing your own hotel reservation and billing system. The content includes the list of tables together with their respective entities and data types.

Objectives of the Study

The aim of the system is to design, develop and implement a hotel reservation with billing system that implements a normalized database structure and will replace the manual process of reservation and billing.

Specifically, the study will:

1. To automate the reservation process of the hotel.
2. To automate the process of billing.
3. To provide an accurate records and information for the customers.

Features of the System

• Customer can also make a reservation through mobile application and website.
• The hotel will have their own website and mobile application with the user-friendly interface wherein customers can easily book a room through mobile application giving them a more easy way and hustle free reservation.
• After the reservation confirmed a notification message will be sent to the email address of the customer confirming that the reservation is successfully made, the notification will include details about the reservation.
• Each customer can create account.
• The system displays other services or add-ons offered by the hotel
• The system will determine if the room is occupied or not
• Cash on hand basis
• Admin will be able to update the following info:
  • Company profile
  • Policies
  • Location
  • Contact details
  • Services offered
• Manage the following:
  • Customers profile
  • Rooms (name of room, images)
  • Room types (name of room types, number of people allowed, price of room type)
  • Manage billing
  • Admin is allowed to approve and cancel online reservation

Database Design

tblbilling (id, reservationid, roomrate, numberOfnightstay, numofpax, discount, total, status, processedby)

Description: billing table will store information such as the rate of the room, number of nights stay, number of persons, discount percentage and amount, total amount of bill and the user who processed the transaction.

Create Query:

CREATE TABLE `tblbilling` (
  `id` int(11) NOT NULL,
  `reservationid` int(11) NOT NULL,
  `roomrate` double NOT NULL,
  `numberOfnightstay` int(11) NOT NULL,
  `numofpax` int(11) NOT NULL,
  `discount` double NOT NULL,
  `total` double NOT NULL,
  `status` int(11) NOT NULL,
  `processedby` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 tblcompanyinfo (id, companyname, address, contact, latitude, longitude)

Description: tblcompanyinfo is the table where the users can update the information of the company such as the name, address, contact person/number and as well as the latitude and longitude of the hotel.

Create Query:

CREATE TABLE `tblcompanyinfo` (
  `id` int(11) NOT NULL,
  `companyname` varchar(100) NOT NULL,
  `address` varchar(150) NOT NULL,
  `contact` varchar(11) NOT NULL,
  `latitude` varchar(50) NOT NULL,
  `longitude` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblcustomer (id, fname, mname, lname, address, contact, email, company, designation, dateofbirth, username, password, status)

Description: customer table will store the basic information of the customers such as the name, contact, email, the desired user and password.

Create Query:

CREATE TABLE `tblcustomer` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) NOT NULL,
  `mname` varchar(30) NOT NULL,
  `lname` varchar(30) NOT NULL,
  `address` varchar(100) NOT NULL,
  `contact` varchar(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  `company` varchar(100) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(30) NOT NULL,
  `status` int(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblgallery (id, picname, description, picturedirectory)

Description: tblgallery is the table where the users of the system can add and update images in the gallery section and slideshow part of the website. The table includes the picture and the image description.

Create Query:

CREATE TABLE `tblgallery` (
  `id` int(11) NOT NULL,
  `picname` varchar(50) NOT NULL,
  `description` varchar(100) NOT NULL,
  `picturedirectory` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblotherbilling (id, billingid, billname, rate)

Description: other billing table is intended for additional charges and it includes the billing id, the bill name and the rate of the charge.

Create Query:

CREATE TABLE `tblotherbilling` (
  `id` int(11) NOT NULL,
  `billingid` int(11) NOT NULL,
  `billname` varchar(30) NOT NULL,
  `rate` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblpolicy (id, policyname, policydescription)

Description: the users of the system can also update the policy of the company and it will be stored in the tblpolicy.

Create Query:

CREATE TABLE `tblpolicy` (
  `id` int(11) NOT NULL,
  `policyname` varchar(50) NOT NULL,
  `policydescription` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblreservation (id, roomid, customerid, datefrom, dateto)

Description: tblreservation is the table that stores the reservation details such as the customer info and the room information, it also includes the dates of reservation.

Create Query:

CREATE TABLE `tblreservation` (
  `id` int(11) NOT NULL,
  `roomid` int(11) NOT NULL,
  `customerid` int(11) NOT NULL,
  `datefrom` date NOT NULL,
  `dateto` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblroom (id, roomno, roomtypeid, capacity, rate, status)

Description: room information such as the room number, room type, capacity and rate will be stored in the tblroom.

Create Query:

CREATE TABLE `tblroom` (
  `id` int(11) NOT NULL,
  `roomno` int(11) NOT NULL,
  `roomtypeid` int(11) NOT NULL,
  `capacity` int(5) NOT NULL,
  `rate` double NOT NULL,
  `status` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblroomtype (id, name, description)

Description: tblroomtype is the table to store the different categories of the rooms like aircon room, deluxe rooms, etc.

Create Query:

CREATE TABLE `tblroomtype` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `description` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblroompicture (id, roomid, picturedirectory)

Description: this table will allow the users of the system to add multiple images to a certain room.

Create Query:

CREATE TABLE `tblroompicture` (
  `id` int(11) NOT NULL,
  `roomid` int(11) NOT NULL,
  `picturedirectory` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbluser (id, username, password, fullname, designation, contact)

Description: tbluser will store the information of the person who can access the system, such information includes the fullname, contact info, username and password.

Create Query:

CREATE TABLE `tbluser` (
  `id` int(11) NOT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(30) NOT NULL,
  `fullname` varchar(50) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `contact` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The hotel reservation system is available in Visual Basic, C# and PHP