How to structure hotel amenities table

Hello everyone,

could someone please advise me how to design my amenities table?

At the moment, besides an ID, my table includes 2 columns - features and services. I’ve added the various features and services to the fields in the following manner:
internet access-dogs allowed-room service-business facilities-hiking trails etc. Then I retrieve these using the explode function. Would it be better to have all of the features/services in separate columns? So I’d have an internet access column, hiking trails column etc.

I’m using checkboxes to filter my results, so if a user selects “hiking trails” it would return only hotels with that feature.

Thank you for your assistance!

Don’t store the data separated by ‘-’. Instead, insert a row for each ID-amenity couple.
If you want to distinguish features and services, add another column that indicates feature or service

Thanks Guido,

would I thus have an amenities table looking this?

amenities_id amenity
1 hiking trails
2 room service
3 internet access

How would I link my amenities table to my “hotels” table? I can add amenities_id as a foreign key to my hotel table but how to get multiple values(amenities)?

Thanks for your help.

No, in my solution the ID column in the anemities table should contain the hotel ID (maybe the table should be called hotelanemities to avoid confusion). So there would be a row for each hotel-anemity couple.

I’m having some difficulties understanding how this would work for multiple hotels.

here ya go…

HOTELS
9 Ritz
37 Hilton
42 Ramada

AMENITIES
1 hiking trails
2 room service
3 internet access
4 dogs allowed
5 business facilities

HOTEL_AMENITIES
9 1
9 3
37 2
37 3
37 4
42 1
42 5

make sense now?

make sense now?

Yes, I got it. Thank you Rudy and Guido!

Hi guys, just to be clear, I would need to have separate tables if I divide my amenities into different categories? A table for features, a table for services etc…

Thanks.

can you give examples of these amenity categories please

Hi Rudy,

thank you for getting back to me. Perhaps I can divide my amenities into 2 categories – features (private jacuzzi, secluded garden) and services (dry cleaning, spa treatments etc).

This is my layout so far:

Services table:
services_id
services_name

Features table:
features_id
features_name

Hotel_amenities table
hotel_amenities_id
hotel_id
services_id
features_id

Is the way I have structured the tables correct?

Thank you!

depends on what the definition of “correct” is :wink:

i would change the amenities table so that each amenity references one of the two categories

the hotel_amenities table should have only two columns, hotel_id and amenities_id, and in particular, it should ~not~ have its own hotel_amenities_id

Hi Rudy,

like this?

amenities table
amenities_id amenity_name amenity_type
1 spa feature
2 nature walks feature
3 daily cleaning service
4 free SQL lessons service
5 large pool feature

Is there anything wrong with having separate features and services tables, or is it simply easier to have them all in one table?

What if I wanted to add some additional info to the features and services, such as dimensions, textures, fabrics etc? For example, the pool might be a certain size, the mini bar is gold-plated etc…

Thanks again for your assistance.

That would be info that is specific to a feature/service of a specific hotel (not all hotels will have the same size pool, and you wouldn’t want to add all possibile pool sizes in the amenities table). And of course you might want to give more than 1 extra info for an amenity (pool of a certain size, indoor/outdoor, heated)

So you could add a additional_info column to the hotel_amenities table, and just store all extra info you want to give about that amenity for that hotel in there. But then it would be hard to do any queries on that info other then retrieve and display.
Or you could add another table, hotel_amenities_info, which would contain a row for each hotel-amenity-infotype-infovalue.

Hi there Guido,

so I would have 3 tables for the amenities:

amenities table
amenities_id amenity_name amenity_type
1 spa feature
2 nature walks feature
3 daily cleaning service
4 free SQL lessons service
5 large pool feature

hotel_amenities table
hotel_id amenities_id
1 1
1 5
2 3
2 4
3 1

hotel_amenities_info table
hotel_id amenities_id type value
1 5 indoor heated

If the above is how the tables should be structured, I would still need another id in the hotel_amenities_info table to relate to the hotel_amenities table – right? If I wanted to, could I add another type-value pair for the same hotel amenity? Using the large pool as an example, I’d have “indoor heated” followed by a new row with a new type-value pair: “size 30m”.

Thanks for helping me with this.

Seems to me like this could go in one table.

amenity_id, amenity_name, amenity_details, parent_id

and do a category/subcategory table.


1 | features      | null             | null
2 | services      | null             | null
3 | drycleaning   | description      | 2
4 | massage       | description      | 2
5 | jacuzzi       | description      | 1

etc

Seems simpler, no? Then you can always add new parents without creating new tables.

the operative word being “seems”

it is now structurally possible to assign to a hotel an amenity called “features” and nothing in the db would prevent it other than a CHECK constraint (which mysql doesn’t support, by the way)

Ah, I figured you’d get around that by just eliminating the categories with null parent IDs in the programming part.

“getting around” stuff with programming is a slippery slope

me, i put as much as possible into the db structure, so that (a) errors are not even possible, and (b) less programming is required

Hi there Rudy and Guido,

have you had a moment to look at my previous post? Could you please advise if this is an appropriate way of designing the amenities tables, or do I need to change anything?

@cydewaze - thanks for your input.

Thank you very much! :slight_smile:

so I would have 3 tables for the amenities:

amenities table
amenities_id amenity_name amenity_type
1 spa feature
2 nature walks feature
3 daily cleaning service
4 free SQL lessons service
5 large pool feature

hotel_amenities table
hotel_id amenities_id
1 1
1 5
2 3
2 4
3 1

hotel_amenities_info table
hotel_id amenities_id type value
1 5 indoor heated

If the above is how the tables should be structured, I would still need another id in the hotel_amenities_info table to relate to the hotel_amenities table – right? If I wanted to, could I add another type-value pair for the same hotel amenity? Using the large pool as an example, I’d have “indoor heated” followed by a new row with a new type-value pair: “size 30m”.

i personally wouldn’t do it that way, with that extra “type/value” table

look up EAV (entity-attribute-value) and you’ll discover that it’s an “anti-pattern” because of the difficulty in extracting information from such a structure