1

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:

enter image description here

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.

  • You should post your schema on a fiddle site so that we can "mess" with it. At first glance, you appear to have mastered the concept of FOREIGN KEYs, so I'm not sure why you don't include an FK to booking in both your payment and evaluation tables. Also, not directly relevant to the problem at hand. but you might want to look at this. – Vérace Jul 30 at 8:37
  • This might be of interest! – Vérace Jul 30 at 20:55
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.

  • The solution you provide solves my problem, but I don't know how to justify the introduction of a foreign key in the PAYMENT table refering the attribute check_in of the BOOKING table. I have added the Conceptual Design so that you see what I am talking about. In class we were taught that a relationship can not relate to another relationship, but only connect 2 entities. Booking(coming from Books) is a relationship between the Guest and the Room, so how can I justify the connection between Payment (and Evaluation, as you propose) with Booking in the Conceptual Design phase? – Georgy90 Jul 30 at 16:03
  • My last comment disapeared somehow. It is a bit tricky to answer when you dont know what is allowed or not. If I recall correctly there is something called associative entity which is both an entity with its own attributes, and a relationship. Are you allowed to use something like that? – Lennart Jul 30 at 19:11
  • I think Booking as I have made it is an associative entity, with check_in, check_out and persons_num being its own attributes, as they don't exist in neither Guest nor Room table, the two entities that Booking connects. But in class we never came accross an example in which a diamond (=relationship, either having it's own attributes or not) was connected with another relationship and I'm trying to build the particular database with the tools given in the lectures. – Georgy90 Jul 30 at 23:10
  • One solution that I thought of : taking as a fact that all payments must be made at the time of the booking, delete the table Payment and bringing the attributes date_paid as date_made and amount to the booking table. As for Evaluation, because evaluating the Hotel(or Room) will be optional, if I bring the rating and guest_comment to the Booking table, enough of them will have Null values, so keeping it as a separate table seems like a good idea. – Georgy90 Jul 30 at 23:16
  • What I thought of is making a trigger activated before insert in Evaluation to check if the count (in the table Booking) of the combination of guest and whichever room of the Hotel he is about to evaluate is bigger than the count(in the table Evaluation) of the combination of the guest and, again, whichever room of the hotel he is about to evaluate. So there will be no direct relation between Booking and Evaluation, but a check of the one based on the other. Does that seem like an effective solution? – Georgy90 Jul 30 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.