Exercise 4: Hotel

erDiagram

    room {
        string number
        string floor
        int id_room_type FK "id_room_type"
        int key PK "id_room"
    }

    customer {
        string name
        string phone
        string email
        int key PK "id_customer"
    }

    room_type {
        string name
        decimal price_per_night
        int key PK "id_room_type"
    }

    booking {
        datetime check_in_date
        datetime check_out_date
        decimal total_price
        int key PK "id_booking"
        int id_booking_state FK "id_booking_state"
        int id_room_type FK "id_room_type"
        int id_room FK "id_room"
        int id_customer FK "id_customer"
    }

    booking_state {
        string name
        string label
        string uuid
        int key PK "id_booking_state"
    }

    accommodation {
        datetime check_in_date
        datetime check_out_date
        decimal total_price
        int key PK "id_accommodation"
        int id_accommodation_state FK "id_accommodation_state"
        int id_booking FK "id_booking"
        int id_room FK "id_room"
    }

    accommodation_state {
        string name
        string label
        string uuid
        int key PK "id_accommodation_state"
    }

    customer ||--o{ booking : makes
    booking }o--|| room_type : assigned_to
    booking }o--|| booking_state : has_status
    booking ||--o{ accommodation : results_in
    accommodation }o--|| room : uses
    accommodation }o--|| accommodation_state : has_status
    room }o--|| room_type : classified_as

Description #

• The exercise aims to develop a hotel management application that centralizes real-time tracking of room availability, check-ins/check-outs, and customer booking records to streamline operations.

• The rooms are identified by number and have a floor and a type. A room cannot be rented to more customers at the same time.

• The hotel manager will have access to view all available rooms and the dates they are open for booking.

• When a customer checks in, the hotel manager will register the customer in the room for a specific interval of dates.

• Both “booking_state” and “accommodation_state” are used to track the lifecycle of reservations and actual stays, respectively. Each state helps in managing the workflow efficiently and ensuring clarity in operations.

• For example, “booking_state” can have: “Pending”, “Canceled”, “Confirmed”, and “Completed”. Also, the “accommodation_state” can have: “Checked-in” and “Checked-out”.

• In entities like “booking_state” and “accommodation_state”, name represents the state that will be displayed on the screen, label represents the value that programmers will use to filter the states, and the uuid represents a value to uniquely distinguish the states.

• Accommodations are completed bookings, tracking the actual check-in and check-out dates and calculating the total price based on the number of days stayed.

• Observation: The field “uuid” is created automatically.

Tasks #

• room/customer/room_type/booking/booking_state/accommodation/accommodation_state: Add, Edit, Delete.

  • When the booking is created, it will automatically become “Pending”.
  • The field “Booking state” from entity “booking” will be read-only in edit view.

• Create a catalog for entities: “room”, “customer”, “room_type”, “booking_state” and “accommodation_state”.

• Create event listeners for tables that need validations (e.g., adding a customer without a name, a room without a number, etc., should not be allowed).

• In the room search view, modify the fetch to specify if the room is available or not.

• When creating a booking:

  • Validate that the following fields are completed: “check_in_date”, “check_out_date”, and “room_type”.
  • If these fields are completed, check if there is a room available with the specified room type for the given period:
    • If a room is available, assign the room (id_room) to the booking.
    • If no rooms are available → validation message.

• Calculate the total price based on the room type and the number of nights.

• Add a new entity action at the booking level (Confirm booking):

  • It will require to input the customer (with navigation to create a new customer if needed).
  • It will change the booking state to “Confirmed” and assign the customer to the booking.

• Add a new entity action at the booking level (Cancel booking):

  • It will change the booking state to “Canceled”.
  • A booking cannot be canceled if it is in the “Confirmed” state or if already canceled → validation message.

• Add a new entity action at the booking level (Check-in):

  • Create an accommodation from the selected booking with the “check_in_date” set to the current date (check the datetime package).
  • Set the accommodation state to “Checked-in”.
  • Change the booking state to “Completed”.

• Add a new entity action at the acommodation level (Check-out):

  • It will change the accommodation state to “Checked-out”.

• Room report: room situation at room level (total number of bookings on each room_type with possibility to be filtered by a period of time).

• Customer report: Booking situation at customer level (total number of bookings with possibility to be filtered by a period of time, customer details).