SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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

  3. #3
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  5. #5
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having some difficulties understanding how this would work for multiple hotels.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    make sense now?

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

  8. #8
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    I would need to have separate tables if I divide my amenities into different categories?
    can you give examples of these amenity categories please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    Is the way I have structured the tables correct?
    depends on what the definition of "correct" is

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by RedBishop View Post
    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.

  14. #14
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  15. #15
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Seems to me like this could go in one table.

    amenity_id, amenity_name, amenity_details, parent_id

    and do a category/subcategory table.

    Code:
    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.
    <cfset myblog = "http://cydewaze.org/">

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Seems simpler, no?
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ah, I figured you'd get around that by just eliminating the categories with null parent IDs in the programming part.
    <cfset myblog = "http://cydewaze.org/">

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!





    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".

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by RedBishop View Post
    Could you please advise if this is an appropriate way of designing the amenities tables,
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Addict
    Join Date
    Jul 2013
    Location
    South of the equator, then turn left
    Posts
    364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay thanks Rudy,

    I'll read up on the evils of EAV.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •