This table contains information about each room in the hotel. Columns:
room_id (int, primary key) - unique identifier for the room room_number (varchar) - room number or name room_type (varchar) - type of the room (e.g. single, double, suite) rate (decimal) - rate per night for the room status (varchar) - status of the room (e.g. vacant, occupied, reserved)
This table contains information about each booking. Columns:
booking_id (int, primary key) - unique identifier for the booking room_id (int, foreign key) - the room that is being booked checkin_date (date) - the date when the guest will check in checkout_date (date) - the date when the guest will check out guest_name (varchar) - name of the guest who made the booking guest_email (varchar) - email address of the guest guest_phone (varchar) - phone number of the guest status (varchar) - status of the booking (e.g. confirmed, canceled, checked-in, checked-out)
This table contains information about each user who has access to the system. Columns:
user_id (int, primary key) - unique identifier for the user username (varchar) - username for the user password (varchar) - hashed password for the user role_id (int, foreign key) - the role of the user (e.g. admin, receptionist)
This table contains information about each role that can be assigned to a user. Columns:
role_id (int, primary key) - unique identifier for the role role_name (varchar) - name of the role (e.g. admin, receptionist)
This table contains information about each guest who has checked in to the hotel. Columns:
checkin_id (int, primary key) - unique identifier for the checkin booking_id (int, foreign key) - the booking associated with the checkin checkin_date (date) - the date when the guest checked in guest_name (varchar) - name of the guest who checked in guest_email (varchar) - email address of the guest guest_phone (varchar) - phone number of the guest
This table contains information about each guest who has checked out of the hotel. Columns:
checkout_id (int, primary key) - unique identifier for the checkout booking_id (int, foreign key) - the booking associated with the checkout checkout_date (date) - the date when the guest checked out guest_name (varchar) - name of the guest who checked out guest_email (varchar) - email address of the guest guest_phone (varchar) - phone number of the guest
This table contains information about each user login/logout activity. Columns:
log_id (int, primary key) - unique identifier for the log user_id (int, foreign key) - the user associated with the activity activity_type (varchar) - type of the activity (e.g. login timestamp (timestamp) - timestamp for when the activity occurred
This table contains information about each room assignment. Columns:
room_assignment_id (int, primary key) - unique identifier for the room assignment booking_id (int, foreign key) - the booking associated with the room assignment room_id (int, foreign key) - the room assigned to the booking checkin_date (date) - the date when the guest will check in checkout_date (date) - the date when the guest will check out
This table contains information about the room inventory. Columns:
room_inventory_id (int, primary key) - unique identifier for the room inventory room_id (int, foreign key) - the room associated with the inventory date (date) - date when the inventory count is taken available_rooms (int) - number of available rooms occupied_rooms (int) - number of occupied rooms reserved_rooms (int) - number of reserved rooms
This table contains information about the room rates. Columns:
room_rate_id (int, primary key) - unique identifier for the room rate room_id (int, foreign key) - the room associated with the rate start_date (date) - date when the rate starts end_date (date) - date when the rate ends rate (decimal) - rate per night for the room during the specified period
Note: This is just an example database structure and may not fit every hotel's requirements. The actual implementation may vary depending on specific business rules and requirements.