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.
booking
in both yourpayment
andevaluation
tables. Also, not directly relevant to the problem at hand. but you might want to look at this.