I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want, and the amenity keys will be sent to the backend. On the backend, I've got these three tables:

| id | name         | vacancies
| ---| ------------ | -------- |
|   1| Marriott     |        0 |
|   2| Best Western |       10 |
|   3| Sheraton     |        3 |

| id | name                  | key                   |
| ---| --------------------- | --------------------- |
|   1| Cafe                  | cafe                  |
|   2| Wheelchair Accessible | wheelchair_accessible |
|   3| Wifi                  | wifi                  |

| id | amenity_id | hotel_id |
| ---| ---------- | -------- |
|   1|          1 |        3 |
|   2|          2 |        1 |
|   3|          2 |        2 |
|   4|          2 |        3 |
|   5|          3 |        2 |
|   6|          3 |        1 |

To search for one amenity, such as wheelchair_accessible, I would do something like this:

WITH hotels_with_amenity as (
    SELECT ha.hotel_id
      FROM hotels_amenities_lookup ha
      JOIN (
            SELECT id from amenities a 
            WHERE  a.key = 'wheelchair_accessible'
        ) amenity ON ha.amenity_id = amenity.id

SELECT  h.name, 
   FROM hotels h, hotels_with_amenity hwa
  WHERE h.id = hwa.hotel_id;

Returns all three hotels.

The question is: if the user selects multiple amenities, wheelchair_accessible and wifi for example, how would I query for hotels that have both? With this current set up, I couldn't do WHERE a.key = 'wheelchair_accessible AND a.key = 'wifi'

Is there a better way of setting up these tables to make this query easier?

I'm new to relational databases and it's likely I'm missing something obvious here.

  • I think that this is a bad database design. Thos amenities could be boolean columns on the hotels table. – Laurenz Albe Mar 26 at 16:38

A classical case of !

One of the most efficient queries techniques for what you ask:

SELECT h.name, 
FROM   hotels_amenities_lookup ha1
JOIN   hotels_amenities_lookup ha2 USING (hotel_id)
-- more?
JOIN   hotels h ON h.id = ha1.hotel_id
WHERE  ha1.amenity_id = (SELECT id FROM amenities WHERE key = 'wheelchair_accessible')
AND    ha2.amenity_id = (SELECT id FROM amenities WHERE key = 'wifi')
-- more?

db<>fiddle here (reusing McNets fiddle, thanks!)

These indices help:

  • amenity(key, id) -- ①
  • hotel_amenity(amenity_id, hotel_id)
  • hotel_amenity(hotel_id, amenity_id) -- ②
  • hotel(id) -- PK

① Maybe in the form of UNIQUE (key) INCLUDE (id). Related:

② Yes, another multi-column index with reversed columns. See:

But there is a plethora of other ways:

Aside 1: The above is using AND, but it's not really about avoiding the keyword, is it?
Aside 2: Just hotels_amenities instead of hotels_amenities_lookup?

Database design

But, as Laurenz commented, the first question is about the right database design. And that heavily depends on exact (undisclosed) specifications and requirements.

Your fully normalized many-to-many implementation is a "catch-all" solution. You can combine as many hotels and as many amenities as you like, rename, add, drop hotels and amenities easily, add any amount of additional information in each table, and indexing with plain btree indexes is simple. Robust against heavy concurrent write load with many updates in any or all tables. But the disk footprint is high. And optimizing your query with many amenities needs some work.

For combining just a hand full of amenities the presented query is fast and optimized by the query planner automatically (using MCV lists in the column statistics). For more than a few amenities (more than join_collapse_limit) you'll have to switch to a smarter solution like outlined in the related answer you found:

The linchpin of the solution is to filter on rare amenities first.
About join_collapse_limit:

OTOH, for a small, stable set of amenities a simple design with boolean flags like Laurenz suggested has a much smaller disk footprint. Maybe add one partial index per flag.
Or an array of amenities for more than a few options (optimized with "char"[]?) with a GIN index. May even be combined with your current design in the form of a MATERIALIZED VIEW.
Each with a matching query style.

If storage space is important (it always is in some way):

As always: it depends.

  • I've learned a new term: relational division. That's very helpful, thank you! When you say this example is "one of the most efficient query techniques", is that still the case if there are 10 or 20 (or however many) keys to search by, instead of just two? I found your comment here and I am trying to wrap my head around your "Best of both worlds" recursive CTE example? Would that approach be better for a usecase with many search keys? – cberg Mar 26 at 10:46
  • @cberg: Yes, the rCTE solution can optimize queries with many amenities. The linchpin of the solution is to filter on rare amenities first. I added some more above. – Erwin Brandstetter Mar 26 at 23:05
  • @cberg: So is this question answered? – Erwin Brandstetter Apr 14 at 23:15

One solution could be using GROUP BY + HAVING COUNT.

  count(*) as num_amenities
  hotels_amenities_lookup hal
  amenities am
  on am.id = hal.amenity_id
  am.key = 'wifi'
  am.key = 'wheelchair_accessible'
group by
  count(*) = 2;

hotel_id | num_amenities
-------: | ------------:
       1 |             2
       2 |             2

The drawback of this model is that conditions are hardcoded in you query, for a generic form you could use a new (temporary) table with the selected amenities.

create table selected_amenities(id int);
insert into selected_amenities values (2),(3);

Now you can add this table to the previous query in this way:

  count(*) as num_amenities
  hotels_amenities_lookup hal
  hotels h
  on h.id = hal.hotel_id
  amenities am
  on am.id = hal.amenity_id
  selected_amenities sa
  on sa.id = am.id
group by
  count(*) = (select count(*) from selected_amenities);
id | name         | vacancies | num_amenities
-: | :----------- | --------: | ------------:
 2 | Best Western |        10 |             2
 1 | Marriott     |         0 |             2

db<>fiddle here


Another option might be to aggregate the amenities into an array, then use Postgres' array operators to search for the hotel:

select h.name, 
from hotels h
  join (
    select l.hotel_id, array_agg(a.key) amenities
    from hotels_amenities_lookup l 
      join amenities a on a.id = l.amenity_id
    group by l.hotel_id
  ) a on a.hotel_id = h.id 
WHERE a.amenities  @> array['wifi', 'wheelchair_accessible'];

Online Example

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.