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.
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
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)?
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.
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…
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).
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…
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.
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”.
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)
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?
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