[RESOLVED] Table Connection Problem

laduch

Here is the problem:

I'm building a website which has hotel functions. In my database, there are 2 tables -- one is "hotels", and another is "roomTypes".

"hotels" has a column "room_types"
"roomTypes" has a column "typeID"

The idea is, one hotel may have several room types, but how do I store these typeIDs into "room_types" column?
I don't want to have columns like room_type1, room_type2, etc...

Whether foreign key can help me on this?

laserlight

According to your model, there is a one to many relationship between hotels and room types. As such, the tables really should be:

hotels {id, ...}
roomTypes {id, hotel_id, ...}

In other words, it is the roomTypes table, not the hotels table, that should have a foreign key to the other related table. This way you can repeat room types for the same hotel.

laduch

But each room type might have multiple hotels, too!
The purpose for separating "hotels" and "roomTypes" is to minimize the redundancy of the data.

laserlight

But each room type might have multiple hotels, too!

Ah, that implies that the relationship is not one to many, but many to many. As such, you should use an intermediate table.

hotels {id, ...}
roomType {id, ...}
hotelRoomTypes {id, hotel_id, roomType_id}

laduch

Thanks a lot!!
This is exactly what I wanted! 😉

laserlight

You're welcome 🙂
Remember to mark this thread as resolved (if it is) using the thread tools.