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?
3
Why do you need a triple reference? The savoy is a HOTEL. “Turkish massage” is a SERVICE. If the Savoy offers that, that’s an entry ii your H_S table.
If someone stays at the savoy, that’s an entry in the BOOKING table pointing to the hotel; if they order a massage as well, that’s an entry in the B_S table pointing to the booking and to the service. There’s no need to point to H_S rather than directly to SERVICE.
Edit. If your “service” definitions vary in price from hoptel to hotel, and all you’re interested in is the price, then sure, you should store the price right in the H_S table and not even have a SERVICE table. But if the services have attributes of their own which you need to retrieve (e.g. for describing them on the bill), then they should always go into a separate table to which you join as necessary. Quasi-static data like model or service definitions with a couple hundrend or thousand rows are really not “big data” – database engines basically laugh at that. It’s just the ones with unlimited growth potential (e.g. social media comments) where access efficiency usually becomes a problem.
4
I’m not sure whether hotel services are “booked with the room”. What about conference rooms or all-day pool passes?
Hotels have customers that are not guests, so a “CUSTOMER” entity is a better suit. A customer that is staying in a room is a guest.
Also, entities/tables should have singular names.
EDIT:
There’s absolutelly no problem in what you call “junction tables” having relationships with other “junction tables”. Often times junction tables have business domain name which gives them a legitimate entity name. For example a junction table between workers and ships should be called CREW instead of WORKER_SHIP. That way what bothers you about relatioship between junction tables maybe would bother you less.
2