Database Design: Hotel Reservation System
Introduction
The database design I will use for the proposal will assist in hotel room reservation. The system created will enable the hotel managers and customer service agents to keep records of rooms, reservations booked, and guests. The hotel room reservation system will use SQL. This type of database should facilitate secure storage, management, and retrieval of data by the use of tables (Li & Manoharan, 2015).
The database design is contingent on the use of four basic tables for storing the data. These tables will include room, reservation, guest, and reserved_room. The rooms table should enable the system users to manage details of the rooms concerning the room number, name, and status, i.e., occupied or available. The status can be represented by values 0 and 1. It should allow the users to understand whether or not, the room can be reserved. The guest table should inform the system user about the names of the visitor and their contact details. The table should store and retrieve the first and second names of the guest, their email, and phone number. As a result, this table should have four fields to store the first name, second name, email, and phone number. Don't use plagiarised sources.Get your custom essay just from $11/page
The reservation table, on the other hand, should offer information about when a room should be occupied and when the visitor should move out. It should also store information about the name of the customer service agent, as well as the name of the guest. As a result, the table storing the data should contain four roles with time_in, time_out, served_by, and guest_id. Finally, the reservation table should be used to offer more information about the type of room reserved by the client. The table will store fields like the number of units, reservation id, and status. It should enable the end-user to display rooms that are occupied or reserved and add more information about the number of units available to the room that a guest has reserved. The status field in the database table will allow the user to see all the rooms that have been occupied by indicating that the room is occupied.
Each table should have an additional field for a primary key that assigns an incremental value to each row in the table. The foreign keys (FKs) should be used to make the tables relational by linking details about reservations, rooms, and guests among different tables.
| Reservation |
| reservation_id PK time_in time_out served_by guest_id FK |
Database Model
| Guest |
| guest_id PK first_name second_name phone_number |
| Rooms PK |
| room_id room_number name status |
| Reserved Room |
| reserved_room_id PK number_of_units reservation_id FK status |
Input Data Types
The input data types that should be used in the room reservation system are tabulated below. The data type lengths are also provided. The information should oversimplify the work done in creating the database to ensure that the correct information is stored in an efficient manner (Taylor, n.d.).
| Table Name | Field Name | Type | Length |
|
Guest | Guest ID | Integer | 10 |
| First Name | Variable character | 70 | |
| Second Name | Variable character | 70 | |
| Variable character | 90 | ||
| Phone Number | Integer | 13 | |
|
Room | Room ID | Integer | 10 |
| Room Number | Integer | 3 | |
| Name | Variable character | 70 | |
| Status | Integer | 1 | |
|
Reservation | Reservation ID | Integer | 10 |
| Time In | Date | ||
| Time Out | Date | ||
| Served By | Variable character | 70 | |
| Guest ID | Integer | 10 | |
| Reserved Room | Reserved Room ID | Integer | 10 |
| Number of Units | Integer | 2 | |
| Reservation ID | Integer | 10 | |
| Status | Integer | 1 |
References
Li, Y., & Manoharan, S. (2015). A performance comparison of SQL and NoSQL databases.
doi:10.1109/PACRIM.2013.6625441.
Taylor, A.G. (n.d.). How to Design a SQL Database. Retrieved from
https://www.dummies.com/programming/sql/how-to-design-a-sql-database/