Question

Create a Database Schema for a hotel reservation system. indicate the Primary Keys, Foreign Keys, and the one-to-one or one-to-many relationships between the tables. Also describe in a small paragraph the scope of the back-end database, by explaining the different tables and their relations in your schema.

Answer 1

PK denotes the primary key while FK denotes the foreign key.

In model above , the main tables are: room, guest and reservation. Afterwards, we continue refining our tables by discovering their attributes or columns. Reservation table has attributes date_in, date_out, status (canceled, confirmed) and made_by (online, in_person, phone, mail), while the attributes of the table guest are: first_name, last_name and member_since.

Table room_reserved stores all rooms belonging to one reservation.

Moreover, as every reservation belongs to one guest, we need to create a new reference linking the guest and the reservation tables.

Move a column: Since we can have several rooms belonging to one reservation, we must allow cancelation per individual room, after that we move the attribute status from reservation to reserved_room table.

One point pending to be modeled is the fact that rooms can be in use by some guests for a period of time. To represent this business fact, we added 2 tables: hosted_at and occupied_room.

Note that every person who stayed at the hotel will have a record in hosted_at. This record will have a reference to the room he/she occupied and to the guest. This is why hosted_at has a double reference towards guest and occupied_room.

The table occupied_room will have one record per each room being rented, on this record we can find the fields: check_in and check_out of type timestamp indicating when the rent begin and finish. A timestamp data type stores a point in time with arbitrary precision. Every occupied_room record will also have a reference to the room number being rented and indirectly via hosted_at to the guests who stayed at this room.

We also added the table room_type to the data model; the idea is to group the rooms by room category or room type.