I'm defining a DB structure, and I have a strong feeling I'm not doing it right. I have hotels that can be configured to offer some optional services (airport pickup, massage), that can in turn be booked with a room. So, each hotel picks which services it offers and the price. Currently I have the following tables (simplified):
Guests (id, (PK), name) Hotels (id (PK), name) HotelServices (name (PK)) Hotels_HotelServices (id (PK), hotel_id (FK), service (FK), price_value, price_currency) Rooms (id (PK), hotel_id (FK), number, capacity, price_value, price_currency) Bookings (id (PK), guest_id (FK), room_id (FK), date_from, date_to) Bookings_Hotels_HotelServices (id (PK), hotels_hotelServices_id (FK))
Is this last table that disturbs me the most. I don't like having a junction table pointing to another junction table, but I can't think of another way of representing which services were booked with the room.
Is there a better, common approach to modelling such situation?