6

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?

  • 2
    Not going to answer because Killian Foth did a good job of that already. Wanted to point out some other unrelated issues I noticed. Pricing sheets for Hotel Rooms are fluid and change from day to day. There may be other potential attributes that could influence the price of a room (Eg. Did you book 6 months in advance? Is there a festival in town? Did you order online? etc...) You may want to consider having some kind of pricing rate table that can change overtime, then the actual purchase price of the room would be a property of the Booking instead of the Room itself. – maple_shaft Jan 14 '16 at 13:22
  • @maple_shaft Those are very good points and hints. Really appreciated – mark951131b Jan 14 '16 at 16:42
  • Can you elaborate on why you consider joining "junction" tables to one another as a bad idea? Is it performance. – JeffO Jan 14 '16 at 18:33
4

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.

| improve this answer | |
  • That'd be an option. But then, when calculating a booking's total price, I still need to get each Service from B_S, then the price from Hotels_HotelSettings. I'd need to get the hotel_id via the room in the booking, plus HotelServices has a VARCHAR as PK, so I'm worried about the performance. Of course, I could change that PK into an INT. – mark951131b Jan 14 '16 at 12:45
  • 1
    If you're really worried about performance, create your database and add some sample data, then look at the query plans for some representative queries. Since you're joining on primary keys to largely static data (how often are you going to get new hotels and/or services?), I doubt you're going to have any performance issues. – TMN Jan 14 '16 at 13:05
  • 1
    @mark951131b Premature optimization is evil. If you have strict performance requirements and clearly expected record volumes in the millions then I would suggest implementing a prototype and doing performance testing to see if you meet performance expectations with the current database design. If not only then should you consider denormalization of the database schema and other tuning. Otherwise, join the tables in query. That is why relational databases and SQL are there for you. People seem to have an irrational fear of table joins when it is clearly not a problem 95% of the time. – maple_shaft Jan 14 '16 at 13:27
  • 1
    @maple_shaft Yes, I totally agree, premature optimization is evil, and I always apply that when coding. But when it comes to DB, given that schemas are much harder to change that application code, and that I'm a newbie, I have some concerns. – mark951131b Jan 14 '16 at 14:52
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.

enter image description here

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.

| improve this answer | |
  • 1
    This is a good data model, however can you edit your question to provide more explanation to how your data model addresses the specifics of the OP's question? Otherwise this seems like a great answer to a question that the OP didn't ask, which is confusing. – maple_shaft Jan 14 '16 at 15:04
  • 1
    @maple_shaft I added en edit on what I think is the OP's question. But my alternative design is, in a way, a solution to his model, adresseing the problems I saw with its original design. – Tulains Córdova Jan 14 '16 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.