Hotel Booking Database design problem

Question

I am currently designing (as an assignment for the course at the university) a database for online booking of hotels worldwide and I have stumbled into a problem.

Here's is the conceptual design:

And below are the tables of the relational layout so far :

GUEST
guest_ID varchar PK
email varchar
guest_password varchar
first_name varchar
last_name varchar
mobile_num varchar
member_status varchar
pref_language varchar
pref_currency_code varchar

HOTEL
hotel_ID varchar PK
hotel_name varchar
ratings_avg int
phone_num varchar
email varchar
currency_code varchar
street_name varchar
street_num varchar
zip_code varchar
city varchar
country varchar

ROOM
room_ID varchar PK
hotel_ID varchar FK to Hotel
room_name varchar
low_season_rate numeric
high_season_rate numeric
max_persons int

BOOKING
guest_ID varchar FK to Guest
room_ID varchar FK to Room
check_in date
check_out date
(a combination of the first 4 as PK)
persons_num int

PAYMENT
guest_ID varchar FK to Guest
room_ID varchar FK to Room
date_paid timestamp
amount numeric

EVALUATION
guest_ID varchar FK to Guest
hotel_ID varchar FK to Hotel
eval_date date
(a combination of the first 3 as PK)
rating int
guest_comment text

I came up with this design thinking as follows: The guests will book rooms, which belong to hotels, pay for the rooms and then, if they want, after their stay, evaluate the hotel.

So I considered Booking and Payment as relationships between the Guest and the Room and the Evaluation as a relationship between the Guest and the Hotel.

The problem this design seems to have is that the Payment and Evaluation are completely cut off from the Booking, so their tables can fill even without a preexisting booking having taken place.

And the way I see it now, a Guest pays for a Stay(=Booking) in a Room and evaluates a Stay(=Booking) in a Hotel, so I am thinking that these tables should refer to the Booking table.

But Booking is a relationship, could I form a relationship between an entity and another relationship? Or could it be considered an entity?

I would welcome any thought about the subject.

Answer 1

I see nothing wrong with PAYMENT referring to BOOKING. Starting with BOOKING, why is check_out part of the P.K.? I don't see how you can check out of the room several times, for a check-in. Given:

CREATE TABLE BOOKING
( guest_ID varchar NOT NULL --  FK to Guest
, room_ID varchar NOT NULL -- FK to Room
, check_in date NOT NULL
,   PRIMARY KEY (guest_ID, room_ID, check_in)
, ... );

For PAYMENT you need to identify what BOOKING it relates to, so it makes sense to include check_in:

CREATE TABLE PAYMENT
( guest_ID varchar NOT NULL
, room_ID varchar NOT NULL
, check_in date NOT NULL
,     FOREIGN KEY (guest_ID, room_ID, check_in) REFERENCES BOOKING (...)
, date_paid timestamp NOT NULL
--    , amount numeric NOT NULL 
);

I deliberately left out the P.K. for PAYMENT, if full payment is required, first 3 columns are sufficient, if partial payment is possible, date_paid can be included.

Other considerations, what is the agreed price of the booking? the price for a room may vary over the season, campaigns etc. If we assume that the price belongs to the booking somehow, the amount paid is only needed in case of partial payments, otherwise, a bill is either paid or not

For evaluation, is there a reason to include hotel instead of room? You can deduce the hotel from the room, and that would also make it possible to relate an evaluation to a booking as with payments.