Hotel Management System

Clarify Requirements

A Hotel Management System lets managers handle online hotel operations — rooms, bookings, billing, housekeeping — from a single portal. Guests can search & book rooms online.

Actors & Use Cases

Core Entities & Enums

Before tables, identify the domain objects. Each maps to a class in code and (usually) a table in DB.

public enum RoomType {
  STANDARD, DELUXE,
  FAMILY_SUITE, PRESIDENTIAL
}

public enum RoomStatus {
  AVAILABLE, BOOKED,
  OCCUPIED, UNDER_MAINTENANCE
}

public enum BookingStatus {
  CONFIRMED, CHECKED_IN,
  CHECKED_OUT, CANCELLED
}

public enum PaymentMode {
  CREDIT_CARD, CASH, CHEQUE
}

Class Diagram

High-level OOP view — the classes, their fields, and relationships. Drives both the Java model layer and DB tables.

ER Diagram — MySQL Schema

Maps the class diagram into relational tables. PK / FK constraints and indexes shown.

MySQL Schema — Full DDL

Complete production-ready DDL with PKs, FKs, composite indexes, and constraints to catch overlapping bookings.

-- ════════════════ 1. HOTEL ════════════════
CREATE TABLE hotel (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(200) NOT NULL,
  address_line VARCHAR(500),
  city         VARCHAR(100) NOT NULL,
  state        VARCHAR(100),
  country      VARCHAR(100) NOT NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_hotel_location (city, country)
);

-- ════════════════ 2. USER ════════════════
CREATE TABLE user (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  email      VARCHAR(200) NOT NULL UNIQUE,
  phone      VARCHAR(20),
  password_hash VARCHAR(255),
  role       ENUM('GUEST','RECEPTIONIST','HOUSEKEEPER','MANAGER') NOT NULL DEFAULT 'GUEST',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ════════════════ 3. ROOM ════════════════
CREATE TABLE room (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  hotel_id        BIGINT NOT NULL,
  room_number     VARCHAR(10) NOT NULL,
  type            ENUM('STANDARD','DELUXE','FAMILY_SUITE','PRESIDENTIAL') NOT NULL,
  status          ENUM('AVAILABLE','BOOKED','OCCUPIED','UNDER_MAINTENANCE') DEFAULT 'AVAILABLE',
  price_per_night DECIMAL(10,2) NOT NULL,
  max_occupancy   INT DEFAULT 2,
  UNIQUE (hotel_id, room_number),
  FOREIGN KEY (hotel_id) REFERENCES hotel(id),
  INDEX idx_room_type_status (hotel_id, type, status)
);

-- ════════════════ 4. BOOKING ════════════════
CREATE TABLE booking (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  guest_id     BIGINT NOT NULL,
  room_id      BIGINT NOT NULL,
  check_in     DATE NOT NULL,
  check_out    DATE NOT NULL,
  status       ENUM('CONFIRMED','CHECKED_IN','CHECKED_OUT','CANCELLED') DEFAULT 'CONFIRMED',
  total_amount DECIMAL(10,2) NOT NULL,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (guest_id) REFERENCES user(id),
  FOREIGN KEY (room_id) REFERENCES room(id),
  CHECK (check_out > check_in),
  INDEX idx_room_dates (room_id, check_in, check_out),
  INDEX idx_guest (guest_id, status)
);

-- ════════════════ 5. PAYMENT ════════════════
CREATE TABLE payment (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id BIGINT NOT NULL UNIQUE,
  amount     DECIMAL(10,2) NOT NULL,
  mode       ENUM('CREDIT_CARD','CASH','CHEQUE') NOT NULL,
  status     ENUM('PENDING','COMPLETED','REFUNDED','FAILED') DEFAULT 'PENDING',
  txn_ref    VARCHAR(100),
  paid_at    DATETIME,
  FOREIGN KEY (booking_id) REFERENCES booking(id)
);

-- ════════════════ 6. SERVICE_CHARGE ════════════════
CREATE TABLE service_charge (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id  BIGINT NOT NULL,
  category    ENUM('FOOD','LAUNDRY','MINIBAR','SPA','AMENITY','OTHER'),
  description VARCHAR(300),
  amount      DECIMAL(10,2) NOT NULL,
  added_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES booking(id),
  INDEX idx_booking (booking_id)
);

-- ════════════════ 7. HOUSEKEEPING_LOG ════════════════
CREATE TABLE housekeeping_log (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  room_id      BIGINT NOT NULL,
  staff_id     BIGINT NOT NULL,
  task_type    ENUM('CLEANING','LAUNDRY','DAMAGE_REPORT','RESTOCK') NOT NULL,
  notes        TEXT,
  performed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (room_id) REFERENCES room(id),
  FOREIGN KEY (staff_id) REFERENCES user(id),
  INDEX idx_room_date (room_id, performed_at)
);

-- ════════════════ 8. NOTIFICATION ════════════════
CREATE TABLE notification (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT NOT NULL,
  booking_id BIGINT,
  channel    ENUM('EMAIL','SMS','PUSH') NOT NULL,
  message    TEXT,
  sent_at    DATETIME,
  status     ENUM('PENDING','SENT','FAILED') DEFAULT 'PENDING',
  FOREIGN KEY (user_id) REFERENCES user(id),
  FOREIGN KEY (booking_id) REFERENCES booking(id)
);

Schema Reference — At a Glance

Key Flows — Sequence Diagrams

🔍 Search & Book Room

❌ Cancel Booking (24hr Rule)

🔔 Check-in Reminder (Scheduled Job)

Design Patterns Used

BookingService — Core Logic

Transactional booking creation with pessimistic lock to prevent double-booking under concurrency.

@Service
public class BookingService {
  private final BookingRepository bookingRepo;
  private final RoomRepository    roomRepo;
  private final PaymentService    paymentService;
  private final EventPublisher    publisher;

  @Transactional
  public Booking createBooking(BookingRequest req) {
    // 1. Lock the room row for this transaction
    Room room = roomRepo.findByIdForUpdate(req.getRoomId())
        .orElseThrow(() -> new RoomNotFoundException());

    // 2. Verify no overlap
    boolean clash = bookingRepo.existsOverlap(
        room.getId(), req.getCheckIn(), req.getCheckOut());
    if (clash) throw new RoomUnavailableException();

    // 3. Compute total
    BigDecimal nights = BigDecimal.valueOf(
        ChronoUnit.DAYS.between(req.getCheckIn(), req.getCheckOut()));
    BigDecimal total = room.getPricePerNight().multiply(nights);

    // 4. Persist booking
    Booking booking = Booking.builder()
        .guestId(req.getGuestId())
        .roomId(room.getId())
        .checkIn(req.getCheckIn())
        .checkOut(req.getCheckOut())
        .status(BookingStatus.CONFIRMED)
        .totalAmount(total)
        .build();
    booking = bookingRepo.save(booking);

    // 5. Charge & publish event (Observer fires notification)
    paymentService.charge(booking, req.getPaymentMode());
    publisher.publish(new BookingCreatedEvent(booking));
    return booking;
  }

  @Transactional
  public RefundResult cancel(Long bookingId) {
    Booking b = bookingRepo.findById(bookingId).orElseThrow();
    if (b.getStatus() != BookingStatus.CONFIRMED)
      throw new IllegalStateException("Cannot cancel");

    long hoursToCheckIn = ChronoUnit.HOURS.between(
        LocalDateTime.now(), b.getCheckIn().atStartOfDay());

    RefundResult refund = (hoursToCheckIn > 24)
        ? paymentService.fullRefund(b)
        : RefundResult.noRefund();

    b.setStatus(BookingStatus.CANCELLED);
    bookingRepo.save(b);
    publisher.publish(new BookingCancelledEvent(b, refund));
    return refund;
  }
}

Critical SQL Queries

-- Rooms in Bangalore, Deluxe, Mar 1–5 — not overlapping any active booking
SELECT r.* FROM room r
  JOIN hotel h ON h.id = r.hotel_id
WHERE h.city = 'Bangalore'
  AND r.type = 'DELUXE'
  AND r.status = 'AVAILABLE'
  AND NOT EXISTS (
    SELECT 1 FROM booking b
    WHERE b.room_id = r.id
      AND b.status IN ('CONFIRMED','CHECKED_IN')
      AND b.check_in < '2026-03-05'
      AND b.check_out > '2026-03-01'
  );

SELECT u.name, u.email, b.check_in, b.check_out, b.status
FROM booking b
  JOIN user u ON u.id = b.guest_id
WHERE b.room_id = 42
ORDER BY b.check_in DESC;

SELECT r.room_number, h.name AS hotel, b.check_in, b.check_out
FROM booking b
  JOIN room r  ON r.id = b.room_id
  JOIN hotel h ON h.id = r.hotel_id
WHERE b.guest_id = 101
ORDER BY b.check_in DESC;

SELECT
  b.total_amount AS room_charge,
  COALESCE(SUM(sc.amount), 0) AS services,
  b.total_amount + COALESCE(SUM(sc.amount), 0) AS grand_total
FROM booking b
  LEFT JOIN service_charge sc ON sc.booking_id = b.id
WHERE b.id = 555
GROUP BY b.id, b.total_amount;

Interview Q&A

Summary & Extensions