All we need is an easy explanation of the problem, so here it is.
I was going through the weak entity concepts and was trying to model the relation
Hotel has many rooms. I have come up with the partial solution presented below but i’m having a problem to uniquely identify the tuples in the room entity related to the specific hotel.
- Hotel(hotel_number, name) is a strong entity.
- Rooms(room_number, bed) is a weak entity whose existence depends on the existence of the hotel.
1. Hotel entity’s table
2. Room entity’s table
There are two hotels with hotel_id 125 and 132. Hotel with hotel_id 125 has room numbered 101 with 3 beds and another hotel has room_number 101 with 2 beds. So how can i model the given relation in such condition as there’s no pk for the weak entity i.e. room ??
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Room should have PK of (hotel_id,room_number).
Weak entities have compound keys with the strong entity key/foreign key as the leading column(s).
There are people who disagree with this approach, but they are wrong.
How can i find the room belonging to specific hotel which has the different number of beds limiting the attributes in the domain of my problem
select * from rooms where hotel_id = 1001 and beds = 3
The real question is why is there no primary key for the
Rooms table? In most cases (except a few edge cases like staging tables) you should have a primary key on your tables.
Despite this, even without a primary key on the
Rooms table, it looks like the relationship is naturally many-to-one with
Hotels (that is any room can only belong to one hotel), and therefore you could at least add a foreign key field to the
Rooms table on
hotel_id. So your foreign key constraint would be on
But my recommendation would be to add both a primary key and then foreign key field to your
Rooms table. Without doing at least one of those things, it’s not possible to establish a relationship between
Hotels. The primary key on the
Rooms table would be defined on
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂