Etsub Feleke.
SHIPPEDAug 14, 2024

Building Database Management System Using SQL

DBMSSQLPostgreSQLDatabase DesignBackend Development

Building a Hotel Management Database System: A Learning Journey

In the dynamic world of hospitality, efficient data management is key to seamless operations, from guest reservations to staff scheduling. As part of my hands-on journey to deepen my backend development and SQL skills, I embarked on designing and implementing a comprehensive Hotel Management System. This project wasn't just about building a system; it was about immersing myself in the practical aspects of relational database design, query optimization, and the intricacies of bringing business logic to life with SQL.

This experience gave me invaluable insights into:

  • Relational Database Design: Learning to structure data effectively using normalization principles.
  • SQL Query Optimization: Discovering how to write efficient queries for reporting and daily operations.
  • Stored Procedures (Functions in PostgreSQL): Encapsulating complex business logic for reusability and efficiency.
  • Performance Tuning: Understanding the critical role of indexing and careful query construction in real-world systems.

My goal was to build a system capable of managing real-time hotel operations, enhancing efficiency, and ensuring data integrity.

Step 1: Designing the Database Schema – My First Dive into Relational Design

I started by meticulously designing the database schema, a foundational step that taught me the importance of a well-structured database. I used an Entity-Relationship (ER) diagram, much like the Crow's Foot model, to visually map out the hotel's core entities and their relationships. This involved carefully considering:

  • Guests: Basic guest information and their historical interactions.
  • Rooms: Details about each room, its type, rate, and current status.
  • Reservations: Linking guests to rooms for specific periods.
  • Payments: Tracking financial transactions.
  • Staff (Employees) & Departments: Organizing hotel personnel and their roles.
  • Employee Schedules: Managing work shifts.
  • Guest Reviews: Capturing customer feedback.

I focused on applying 3rd Normal Form (3NF) principles to minimize data redundancy and enforce referential integrity. This ensures that data is consistent and reliable across the entire system.

Here are the CREATE TABLE statements I learned to write, tailored for a PostgreSQL environment (as initially planned, though my original exploration used MySQL Workbench for visual design and initial scripting):

-- Creating the Guests table to store customer information
CREATE TABLE Guests (
    guest_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(200) NOT NULL,
    guest_email VARCHAR(200) NOT NULL,
    phone_no VARCHAR(20) NOT NULL, -- Using VARCHAR for phone numbers for flexibility
    address VARCHAR(200) NOT NULL,
    preferences VARCHAR(200), -- Guest preferences (e.g., non-smoking, high floor)
    stay_history VARCHAR(200) -- History of previous stays
);

-- Creating the Departments table for organizing hotel departments
CREATE TABLE Departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(200) NOT NULL,
    number_of_employees INT NOT NULL
);

-- Creating the Employees table to store staff details
CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    employee_role VARCHAR(200) NOT NULL, -- e.g., 'Receptionist', 'Manager'
    employee_phone_no VARCHAR(20) NOT NULL,
    employee_email VARCHAR(200) NOT NULL,
    department_id INT NOT NULL,
    CONSTRAINT fk_department_id FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

-- Creating the Employee_Schedules table to manage staff work shifts
CREATE TABLE Employee_Schedules (
    schedule_id SERIAL PRIMARY KEY,
    employee_id INT NOT NULL,
    schedule_type VARCHAR(50) NOT NULL, -- e.g., 'Full-Time', 'Part-Time', 'Temporary'
    schedule_date DATE NOT NULL,
    clock_in_time TIME NOT NULL,
    CONSTRAINT fk_employee_id FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

-- Creating the Rooms table to store room details
CREATE TABLE Rooms (
    room_no SERIAL PRIMARY KEY,
    room_type VARCHAR(50) NOT NULL, -- e.g., 'Deluxe', 'Standard', 'Suite'
    room_price DECIMAL(10,2) NOT NULL,
    room_category VARCHAR(100) NOT NULL, -- e.g., 'Standard Suite', 'Presidential Suite'
    room_status VARCHAR(50) NOT NULL, -- e.g., 'Available', 'Occupied', 'Under Maintenance'
    schedule_id INT NOT NULL, -- This foreign key links to an employee schedule, possibly for cleaning or maintenance assignment
    CONSTRAINT fk_room_schedule_id FOREIGN KEY (schedule_id) REFERENCES Employee_Schedules(schedule_id)
);

-- Creating the Reservations table to manage guest bookings
CREATE TABLE Reservations (
    reservation_id SERIAL PRIMARY KEY,
    guest_id INT NOT NULL,
    room_no INT NOT NULL,
    check_in_date TIMESTAMP NOT NULL,
    check_out_date TIMESTAMP NOT NULL,
    confirmation_no VARCHAR(50) UNIQUE NOT NULL, -- Unique confirmation number for each reservation
    reservation_status VARCHAR(50) DEFAULT 'Pending', -- e.g., 'Confirmed', 'Pending', 'Cancelled'
    CONSTRAINT fk_reservation_guest_id FOREIGN KEY (guest_id) REFERENCES Guests(guest_id),
    CONSTRAINT fk_reservation_room_no FOREIGN KEY (room_no) REFERENCES Rooms(room_no)
);

-- Creating the Payments table to record financial transactions
CREATE TABLE Payments (
    payment_id SERIAL PRIMARY KEY,
    reservation_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    method VARCHAR(50) NOT NULL, -- e.g., 'Credit Card', 'Cash', 'PayPal'
    CONSTRAINT fk_payment_reservation_id FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id)
);

-- Creating the Guest_Reviews table for guest feedback
CREATE TABLE Guest_Reviews (
    review_id SERIAL PRIMARY KEY,
    reservation_id INT UNIQUE NOT NULL, -- Assuming one review per reservation for simplicity in this schema
    room_quality_rating INT NOT NULL CHECK (room_quality_rating >= 1 AND room_quality_rating <= 5),
    service_rating INT NOT NULL CHECK (service_rating >= 1 AND service_rating <= 5),
    cleanliness_rating INT NOT NULL CHECK (cleanliness_rating >= 1 AND cleanliness_rating <= 5),
    amenities_rating INT NOT NULL CHECK (amenities_rating >= 1 AND amenities_rating <= 5),
    comments TEXT NOT NULL,
    CONSTRAINT fk_review_reservation_id FOREIGN KEY (reservation_id) REFERENCES Reservations(reservation_id)
);

Step 2: Populating the Database – Bringing Data to Life

After defining the schema, the next challenge was populating the tables with data. I learned the process of preparing data in CSV files and then importing it into the database. This approach proved efficient for initial data loading and simulated real-world data migration. While the full INSERT statements for all rows are extensive, here's an example structure for how data would be inserted into one of the tables:

-- Example data insertion for the Departments table
INSERT INTO Departments (department_id, department_name, number_of_employees) VALUES
(1001, 'Housekeeping', 10),
(1002, 'Room Service', 15),
(1003, 'Reception', 5),
(1004, 'Guest Relations', 5),
(1005, 'Administration', 2);

Step 3: Implementing Business Logic with Stored Procedures and Queries – Beyond Basic SELECTs

This phase was particularly enlightening as I moved beyond simple data storage to implementing dynamic business logic. I experimented with stored procedures (functions in PostgreSQL terms) to automate common tasks and ensure data consistency.

Here are a couple of functions I learned to create, which encapsulate logic like calculating revenue and checking room availability:

-- Function to calculate total revenue within a specified date range
CREATE OR REPLACE FUNCTION calculate_revenue(start_date DATE, end_date DATE)
RETURNS NUMERIC AS $$
BEGIN
    RETURN (
        SELECT SUM(amount)
        FROM Payments
        WHERE payment_date BETWEEN start_date AND end_date
    );
END;
$$ LANGUAGE plpgsql;

-- Function to check if a specific room is available for a given period
CREATE OR REPLACE FUNCTION is_room_available(room_num INT, start_date DATE, end_date DATE)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1 FROM Reservations
        WHERE room_no = room_num
          AND reservation_status = 'Confirmed' -- Only consider confirmed bookings for availability
          AND (check_in_date, check_out_date) OVERLAPS (start_date, end_date)
    );
END;
$$ LANGUAGE plpgsql;

I also practiced writing various SELECT queries to retrieve specific operational data and generate reports. These queries taught me how to join tables and filter results efficiently:

-- Query 1: Check availability of rooms
SELECT Room_no, Room_type, Room_price
FROM Rooms
WHERE Room_status = 'Available';

-- Query 2: Retrieve employee data working in a specific role (e.g., 'Administration')
SELECT E.Employee_name, E.Employee_phone_no, D.Department_name, E.Employee_email
FROM Employees AS E
JOIN Departments AS D ON E.department_id = D.department_id
WHERE E.Employee_role = 'Administration';

-- Query 3: Get average ratings for services from guest reviews
SELECT
    ROUND(AVG(Room_quality_rating), 2) AS Avg_Room_Quality,
    ROUND(AVG(Service_rating), 2) AS Avg_Service,
    ROUND(AVG(Cleanliness_rating), 2) AS Avg_Cleanliness,
    ROUND(AVG(Amenities_rating), 2) AS Avg_Amenities
FROM Guest_Reviews;

-- Query 4: Get the details of employees who work full-time
SELECT *
FROM Employee_Schedules
WHERE Schedule_type = 'Full-Time';

-- Query 5: List the top-rated rooms based on guest reviews
SELECT R.Room_no,
       ROUND(AVG((GR.Room_quality_rating + GR.Service_rating + GR.Cleanliness_rating + GR.Amenities_rating) / 4.0)) AS Average_Rating
FROM Guest_Reviews AS GR
JOIN Reservations AS Res ON GR.reservation_id = Res.reservation_id
JOIN Rooms AS R ON Res.room_no = R.room_no
GROUP BY R.Room_no
ORDER BY Average_Rating DESC;

Step 4: Optimizing for Performance – Making It Fast

Understanding that a well-designed schema isn't enough for performance, I delved into optimization techniques. I learned about:

  • Indexing: Creating indexes on frequently queried columns (like room_no, guest_id, and payment_date) significantly speeds up data retrieval.
  • Query Refinement: Using PostgreSQL's EXPLAIN and ANALYZE tools helped me understand query execution plans, identify bottlenecks, and rewrite inefficient queries.
  • Materialized Views/Summary Tables: For complex or frequently run reports, I learned to consider creating pre-computed summary tables to reduce live query load.

These improvements were crucial for achieving a significant reduction in query runtime for common reports, ultimately enhancing the system's responsiveness.

Key Takeaways

This project provided me with invaluable end-to-end experience in database development. I gained a deep appreciation for:

  • Schema Design: The art of structuring data for efficiency and integrity.
  • SQL Fluency: Writing precise queries and understanding advanced SQL features like functions.
  • Database Management Systems: Practical interaction with PostgreSQL and its optimization tools.
  • Translating Requirements: Converting real-world business operations into efficient data structures and logic.

Why This Matters

In today's data-driven world, the ability to design, optimize, and query relational systems is a foundational skill for any data professional. This project showcases my strong grasp of SQL and PostgreSQL, and my practical experience in improving system performance through thoughtful database design. It's a testament to how hands-on coding and tool exploration directly translate into a deeper understanding of complex systems. `

← all field notes