Database Design of Hotel Booking Application

Back to: ASP.NET Core Web API Tutorials

Database Design of Hotel Booking Application

In this article, I will discuss the Database Design of a Hotel Booking Application. Please read our previous articles discussing the Different Modules in a Hotel Booking Application.

Database Design of Hotel Booking Application

Designing a database for a hotel booking application involves creating schemas that can manage everything from room reservations and guest information to payments and staff administration. The following are the Key Entities involved in the Hotel Booking Application.

• UserRoles: This entity manages different roles within the system, such as Administrator, Manager, or Guest. It can include fields such as RoleID, RoleName, IsActive, and Description.
• Users: This entity stores information about users who can log into the system, possibly including staff and guests if they have accounts. It can include fields such as UserID, RoleID, Email, PasswordHash, CreatedAt, LastLogin, IsActive, etc.
• Countries: This entity holds information about countries, potentially for address management in user profiles or reservations. It can include fields such as CountryID, CountryName, CountryCode, and IsActive.
• States: This entity manages state information linked to countries, which is used similarly for address management. It can include fields such as StateID, StateName, CountryID, and IsActive.
• RoomTypes: This entity defines the types of rooms available in the hotel, such as standard, deluxe, and suite, each possibly with different amenities. It can include fields such as RoomTypeID, TypeName, AccessibilityFeatures, Description, IsActive, etc.
• Rooms: Details of individual rooms available at the hotel are linked to their Room Types. This entity can include fields such as RoomID, RoomNumber, RoomTypeID, Price, BedType, ViewType, Status, IsActive, etc.
• Amenities: This entity describes amenities available in the hotel that can be linked to room types or rooms. It can include fields such as AmenityID, Name, Description, IsActive, etc.
• RoomAmenities: Links room types to amenities, indicating which amenities are available in which room types.
• Guests: This entity stores information about hotel guests, potentially linked to users if they have accounts. It can include fields such as GuestID, UserID, FirstName, LastName, Email, Phone, AgeGroup, Address, CountryID, StateID, etc.
• Reservations: This entity manages booking information for hotel rooms, including guest details and stay duration. It can include fields such as ReservationID, UserID, RoomID, BookingDate, CheckInDate, CheckOutDate, NumberOfGuests, Status, etc.
• PaymentBatches: This entity manages batches of payments made by users, possibly for group bookings or corporate clients. It can include fields such as PaymentBatchID, UserID, PaymentDate, TotalAmount, PaymentMethod, etc.
• Payments: This entity tracks individual payments linked to reservations and batches. It can include fields such as PaymentID, ReservationID, Amount, PaymentBatchID, etc.
• Cancellations: Records details about reservation cancellations, including fees and status. This entity can include fields such as CancellationID, ReservationID, CancellationDate, Reason, CancellationFee, CancellationStatus, etc.
• RefundMethods: Stores different methods available for processing refunds. This entity can include fields such as MethodID, MethodName, IsActive, etc.
• Refunds: This entity manages refund transactions linked to specific payments. It can include fields such as RefundID, PaymentID, RefundAmount, RefundDate, RefundReason, RefundMethodID, ProcessedByUserID, RefundStatus, etc.
• Feedback: This entity collects feedback from guests regarding their stay linked to their reservation. It can include fields such as FeedbackID, ReservationID, GuestID, Rating, Comment, FeedbackDate, etc.

Creating Hotel Booking Database:

Please execute the following SQL Script on the SQL Server Database, which will create the required Database with the required tables. As per your requirements, you can add a few more columns to the database tables. In fact, as we progress in this Hotel Application development, we may add a few more columns. Let us first create the database, then we will understand the Role and responsibilities of each column of each database tables.

CREATE DATABASE HotelDB;
GO

USE HotelDB;
GO

-- UserRoles Table
CREATE TABLE UserRoles (
    RoleID INT PRIMARY KEY IDENTITY(1,1),
    RoleName NVARCHAR(50),
    IsActive BIT DEFAULT 1,
    Description NVARCHAR(255)
);
GO

-- User Information
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    RoleID INT,
    Email NVARCHAR(100) UNIQUE,
    PasswordHash NVARCHAR(255),
    CreatedAt DATETIME DEFAULT GETDATE(),
    LastLogin DATETIME,
    IsActive BIT DEFAULT 1,
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME,
    FOREIGN KEY (RoleID) REFERENCES UserRoles(RoleID)
);
GO

-- Countries and States Tables
CREATE TABLE Countries (
    CountryID INT PRIMARY KEY IDENTITY(1,1),
    CountryName NVARCHAR(50),
    CountryCode NVARCHAR(10),  -- ISO country codes
    IsActive BIT DEFAULT 1
);
GO

CREATE TABLE States (
    StateID INT PRIMARY KEY IDENTITY(1,1),
    StateName NVARCHAR(50),
    CountryID INT,
    IsActive BIT DEFAULT 1,
    FOREIGN KEY (CountryID) REFERENCES Countries(CountryID)
);
GO

-- Room Types Master Data
CREATE TABLE RoomTypes (
    RoomTypeID INT PRIMARY KEY IDENTITY(1,1),
    TypeName NVARCHAR(50),
    AccessibilityFeatures NVARCHAR(255),
    Description NVARCHAR(255),
    IsActive BIT DEFAULT 1,
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME
);
GO

-- Rooms of the Hotel
CREATE TABLE Rooms (
    RoomID INT PRIMARY KEY IDENTITY(1,1),
    RoomNumber NVARCHAR(10) UNIQUE,
    RoomTypeID INT,
    Price DECIMAL(10,2),
    BedType NVARCHAR(50),
    ViewType NVARCHAR(50),
    Status NVARCHAR(50) CHECK (Status IN ('Available', 'Under Maintenance', 'Occupied')),
    IsActive BIT DEFAULT 1,
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME,
    FOREIGN KEY (RoomTypeID) REFERENCES RoomTypes(RoomTypeID)
);
GO

-- Amenities Available in the hotel
CREATE TABLE Amenities (
    AmenityID INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Description NVARCHAR(255),
    IsActive BIT DEFAULT 1,
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME
);
GO

-- Linking room types with amenities
CREATE TABLE RoomAmenities (
    RoomTypeID INT,
    AmenityID INT,
    FOREIGN KEY (RoomTypeID) REFERENCES RoomTypes(RoomTypeID),
    FOREIGN KEY (AmenityID) REFERENCES Amenities(AmenityID),
    PRIMARY KEY (RoomTypeID, AmenityID) -- Composite Primary Key to avoid duplicates
);
GO

-- The Guests who are going to stay in the hotel
CREATE TABLE Guests (
    GuestID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Phone NVARCHAR(15),
    AgeGroup NVARCHAR(20) CHECK (AgeGroup IN ('Adult', 'Child', 'Infant')),
    Address NVARCHAR(255),
    CountryID INT,
    StateID INT,
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME,
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (CountryID) REFERENCES Countries(CountryID),
    FOREIGN KEY (StateID) REFERENCES States(StateID)
);
GO

-- Storing Reservation Information
CREATE TABLE Reservations (
    ReservationID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT,
    RoomID INT,
    BookingDate DATE,
    CheckInDate DATE,
    CheckOutDate DATE,
    NumberOfGuests INT,
    Status NVARCHAR(50) CHECK (Status IN ('Reserved', 'Checked-in', 'Checked-out', 'Cancelled')),
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME,
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (RoomID) REFERENCES Rooms(RoomID),
    CONSTRAINT CHK_CheckOutDate CHECK (CheckOutDate > CheckInDate)  
);
GO

-- Mapping table for guests linked to reservations
CREATE TABLE ReservationGuests (
    ReservationGuestID INT PRIMARY KEY IDENTITY(1,1),
    ReservationID INT,
    GuestID INT,
    FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID),
    FOREIGN KEY (GuestID) REFERENCES Guests(GuestID)
);
GO

-- Table for tracking batch payments
CREATE TABLE PaymentBatches (
    PaymentBatchID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT,
    PaymentDate DATETIME,
    TotalAmount DECIMAL(10,2),
    PaymentMethod NVARCHAR(50),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
GO
-- Individual payments Linked to Reservations and Batch Payment
CREATE TABLE Payments (
    PaymentID INT PRIMARY KEY IDENTITY(1,1),
    ReservationID INT,
    Amount DECIMAL(10,2),
    PaymentBatchID INT,
    FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID),
    FOREIGN KEY (PaymentBatchID) REFERENCES PaymentBatches(PaymentBatchID)
);
GO

-- Cancellations tracking with a fee
CREATE TABLE Cancellations (
    CancellationID INT PRIMARY KEY IDENTITY(1,1),
    ReservationID INT,
    CancellationDate DATETIME,
    Reason NVARCHAR(255),
    CancellationFee DECIMAL(10,2),
    CancellationStatus NVARCHAR(50) CHECK (CancellationStatus IN ('Pending', 'Approved', 'Denied')),
    CreatedBy NVARCHAR(100),
    CreatedDate DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(100),
    ModifiedDate DATETIME,
    FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID)
);
GO

-- Table for Storing Refund Methods
CREATE TABLE RefundMethods (
    MethodID INT PRIMARY KEY IDENTITY(1,1),
    MethodName NVARCHAR(50),
    IsActive BIT DEFAULT 1,
);
GO

-- Table for tracking Refunds
CREATE TABLE Refunds (
    RefundID INT PRIMARY KEY IDENTITY(1,1),
    PaymentID INT,
    RefundAmount DECIMAL(10,2),
    RefundDate DATETIME DEFAULT GETDATE(),
    RefundReason NVARCHAR(255),
    RefundMethodID INT,
    ProcessedByUserID INT,
    RefundStatus NVARCHAR(50),
    FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
    FOREIGN KEY (RefundMethodID) REFERENCES RefundMethods(MethodID),
    FOREIGN KEY (ProcessedByUserID) REFERENCES Users(UserID)
);
GO

-- Feedback Table
CREATE TABLE Feedbacks (
    FeedbackID INT PRIMARY KEY IDENTITY(1,1),
    ReservationID INT,
    GuestID INT,
    Rating INT CHECK (Rating BETWEEN 1 AND 5),  -- Rating scale from 1 to 5
    Comment NVARCHAR(1000),  -- Optional detailed comment
    FeedbackDate DATETIME,  -- The date and time the feedback was submitted
    FOREIGN KEY (ReservationID) REFERENCES Reservations(ReservationID),
    FOREIGN KEY (GuestID) REFERENCES Guests(GuestID)
);
GO

Once you execute the above SQL Script, your HotelDB database should be created in SQL Server with the following structure.

Understanding the Hotel Database Tables and Columns:

Let us understand each database table, the purpose of each column, and how these tables are interconnected through relationships:

UserRoles Table

This table Stores different user roles within the hotel management system.

• RoleID: Unique identifier for each role, auto-incremented.
• RoleName: Name of the role (e.g., Admin, Guest, Manager).
• IsActive: Boolean indicates whether the role is active.
• Description: Description of what the role entails.

Users Table

This Table stores information about Users, including guests, staff, and administrators. It’s used to manage login credentials and access rights.

• UserID: Unique identifier for each user, auto-incremented.
• RoleID: Links to the UserRoles table to define the user’s role.
• Email: The user’s email address must be unique.
• PasswordHash: Hash of the user’s password for secure storage.
• CreatedAt: The date and time the user account was created defaults to the current date and time.
• LastLogin: Date and time of the user’s last login.
• IsActive: Boolean indicating whether the user’s account is active.
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: These are audit columns that track who created and last modified the user’s record and when.

Countries Table

Stores list of countries.

• CountryID: Unique identifier for each country, auto-incremented.
• CountryName: Name of the country.
• CountryCode: The ISO code for the country.
• IsActive: Boolean indicates whether the country is active.

States Table

Stores list of states/provinces within countries.

• StateID: Unique identifier for each state, auto-incremented.
• StateName: Name of the state.
• CountryID: Links to the Countries table.
• IsActive: Boolean indicating whether the State is active.

RoomTypes Table

Defines different types of rooms available in the hotel, which helps manage room features and pricing.

• RoomTypeID: A unique identifier for each room type (auto-incremented).
• TypeName: The name of the room type, e.g., Standard, Deluxe, Executive, Family.
• AccessibilityFeatures: Describes features available for disabled access.
• Description: A description of the room type detailing what makes each type unique or special.
• IsActive: Boolean indicates whether the room type is active.
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: Audit columns.

Rooms Table

Records details of individual rooms in the hotel, including their availability, type, and pricing.

• RoomID: Unique identifier for each room, auto-incremented.
• RoomNumber: The room number must be unique.
• RoomTypeID: Links to the RoomTypes table.
• Price: Daily price for the room.
• BedType: Type of beds in the room.
• ViewType: Type of view from the room (e.g., sea, city).
• Status: Status of the room (Available, Under Maintenance, Occupied).
• IsActive: Boolean indicates whether the room is active.
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: Audit columns.

Amenities Table

This table lists the amenities available at the hotel, which can be linked to specific room types.

• AmenityID: A unique identifier for each amenity (auto-incremented).
• Name: The name of the amenity, e.g., “Wi-Fi”, “Pool”.
• Description: A detailed description of the amenity.
• IsActive: Boolean indicating whether the amenity is active.
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: Audit columns.

RoomAmenities Table

This table establishes a relationship between room types and amenities, indicating which amenities are available for which room types. To avoid duplicates, the composite primary key on both columns is used.

• RoomTypeID: Foreign key to RoomTypes.
• AmenityID: Foreign key to Amenities.

Guests Table

This table is used to store information about guests.

• GuestID: Unique identifier for each guest, auto-incremented.
• UserID: Links to the Users table.
• FirstName, LastName: Guest’s name.
• Email: Guest’s email.
• Phone: Guest’s phone number.
• AgeGroup: Age group of the guest (Adult, Child, Infant).
• Address: Guest’s address.
• CountryID, StateID: Links to the Countries and States tables.
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: Audit columns.

Reservations Table

This table is used to track room bookings.

• ReservationID: Unique identifier for each reservation, auto-incremented.
• UserID: User who made the booking.
• RoomID: Reserved room.
• BookingDate, CheckInDate, CheckOutDate: Dates related to the booking.
• NumberOfGuests: Number of guests for the booking.
• Status: The booking status (Reserved, Checked-in, Checked-out, Cancelled).
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: Audit columns.
• CHK_CheckOutDate: Constraint ensuring check-out is after check-in.

ReservationGuests Table

Links guests to reservations.

• ReservationGuestID: Unique identifier, auto-incremented.
• ReservationID, GuestID: Link to the Reservations and Guests tables.

PaymentBatches Table

This table aggregates guest payments and is often used for financial reporting and reconciliation. If the user is booking multiple reservations or rooms, this table stores the total amount and payment information, like who, how, and when the payment was made.

• PaymentBatchID: Unique identifier, auto-incremented.
• UserID: User who processed the payment batch.
• PaymentDate: Date of payment processing.
• TotalAmount: Total amount in the batch.
• PaymentMethod: Method used for payment.

Payments Table

This table records Individual payments linked to reservations, which are linked to payment batches for accounting purposes.

• PaymentID: Unique identifier, auto-incremented.
• ReservationID: Linked reservation.
• Amount: Amount of the payment.
• PaymentBatchID: Linked payment batch.

Cancellations Table

This table is used to manage and record details about reservation cancellations, including reasons and fees involved.

• CancellationID: A unique identifier for each cancellation (auto-incremented).
• ReservationID: The reservation is being canceled.
• CancellationDate: The date of cancellation.
• Reason: The reason for cancellation.
• CancellationFee: Fee charged for cancellation.
• CancellationStatus: Status of the cancellation (Pending, Approved, Denied).
• CreatedBy, CreatedDate, ModifiedBy, ModifiedDate: Audit columns.

RefundMethods Table:

Lists possible methods for refunds. This table is used to store a predefined list of refund methods like Cash, Credit Card, Online Transfer, Check, etc.

• MethodID: A unique identifier for each refund method (auto-incremented).
• MethodName: The name of the refund method, e.g., Cash, Credit Card, Online Transfer, Check.
• IsActive: Boolean indicating whether the Refund Method is active.

Refunds Table:

This table is used to track all refunds issued by the hotel. This includes the amount, the specific payments being refunded, the reasons for the refunds, and the staff member who processed the refund.

• RefundID: A unique identifier for each refund (auto-incremented).
• PaymentID: Links the refund to a specific payment from the Payments table, indicating which payment is being refunded.
• RefundAmount: The amount of money being refunded.
• RefundDate: The date on which the refund was processed. Defaults to the current date and time.
• RefundReason: A text description explaining why the refund was issued.
• RefundMethodID: The Refund Method ID is used to process the refund.
• ProcessedByUserID: Identifies the user who processed the refund, linking to the Users table to capture who is responsible for the transaction.
• RefundStatus: Status of the refund which can be Pending, Approved, and Cancelled.

Feedbacks Table

Collects feedback from guests regarding their stay.

• FeedbackID: Unique identifier, auto-incremented.
• ReservationID, GuestID: Links to reservations and guests.
• Rating: Rating given by the guest.
• Comment: Optional detailed comment by the guest.
• FeedbackDate: Date feedback was submitted.

Inserting Master Data into the Master Database Table:

Let us insert some master data into the Master database table. You can add the data as per your hotel requirements. Please execute the following SQL Statements sequentially in your database system to ensure the integrity of foreign key relationships.

-- Inserting Data into UserRoles table
INSERT INTO UserRoles (RoleName, Description) VALUES
('Admin', 'Administrator with full access'),
('Guest', 'Guest user with limited access'), -- You can replace Guest with User also
('Manager', 'Hotel manager with extended privileges');

-- Inserting Data into Countries and States tables
-- Insert Countries
INSERT INTO Countries (CountryName, CountryCode) VALUES
('India', 'IN'),
('USA', 'US'),
('UK', 'GB');

-- Assuming the IDs for countries are 1 for India, 2 for USA, and 3 for UK
-- Insert States
INSERT INTO States (StateName, CountryID) VALUES
('Maharashtra', 1),
('Delhi', 1),
('Texas', 2),
('California', 2),
('England', 3),
('Scotland', 3);

-- Inserting Data into RoomTypes table
INSERT INTO RoomTypes (TypeName, AccessibilityFeatures, Description, CreatedBy, ModifiedBy) VALUES
('Standard', 'Wheelchair ramps, Grab bars in bathroom', 'Basic room with essential amenities', 'System', 'System'),
('Deluxe', 'Wheelchair accessible, Elevator access', 'High-end room with luxurious amenities', 'System', 'System'),
('Executive', 'Wide door frames, Accessible bathroom', 'Room for business travelers with a work area', 'System', 'System'),
('Family', 'Child-friendly facilities, Safety features', 'Spacious room for families with children', 'System', 'System');

-- Inserting Data into Rooms table
-- Assuming the IDs for room types are 1 for Standard, 2 for Deluxe, 3 for Executive, and 4 for Family
INSERT INTO Rooms (RoomNumber, RoomTypeID, Price, BedType, ViewType, Status, CreatedBy, ModifiedBy) VALUES
('101', 1, 100.00, 'Queen', 'Sea', 'Available', 'System', 'System'),
('102', 1, 100.00, 'Queen', 'City', 'Under Maintenance', 'System', 'System'),
('201', 2, 150.00, 'King', 'Garden', 'Occupied', 'System', 'System'),
('301', 3, 200.00, 'King', 'Sea', 'Available', 'System', 'System'),
('401', 4, 250.00, 'Twin', 'Pool', 'Occupied', 'System', 'System');

-- Inserting Data into Amenities table
INSERT INTO Amenities (Name, Description, CreatedBy, ModifiedBy) VALUES
('Wi-Fi', 'High-speed wireless internet access', 'System', 'System'),
('Pool', 'Outdoor swimming pool with lifeguard', 'System', 'System'),
('SPA', 'Full-service spa and wellness center', 'System', 'System'),
('Fitness Center', 'Gym with modern equipment', 'System', 'System');

-- Linking Room Types with Amenities
-- Assuming the IDs for amenities are 1 for Wi-Fi, 2 for Pool, 3 for SPA, and 4 for Fitness Center
INSERT INTO RoomAmenities (RoomTypeID, AmenityID) VALUES
(1, 1), (1, 4),  -- Standard rooms have Wi-Fi and access to Fitness Center
(2, 1), (2, 2), (2, 3), (2, 4),  -- Deluxe rooms have all amenities
(3, 1), (3, 4),  -- Executive rooms have Wi-Fi and Fitness Center
(4, 1), (4, 2), (4, 3), (4, 4);  -- Family rooms have all amenities

-- Inserting Data into RefundMethods table
INSERT INTO RefundMethods (MethodName) VALUES
('Cash'),
('Credit Card'),
('Online Transfer'),
('Check');

In this article, I explain the Database Design of a Hotel Booking Application and provide the required SQL Script. So, please be ready with your database before proceeding with the next article. Please take a few minutes to give your valuable feedback about this article. Your feedback means a lot to me and motivates me to give better examples and explanations. 

What Next? Next, we are going to set up the ASP.NET Core Web API Project to develop the hotel booking application.