Shops/shop items design?

Suppose I have thousands of shops and each shop has thousands of items they sell. I want to store all items for all shops and query all items for all shops whose shop satisfies a set of shop filters.

For example, I might want the first X items amongst all shops within a fixed geo distance that have an atm, and accept credit cards.

My initial thought on how to design this:

  1. Create an index named shop_items which contains all items for all shops.
  2. Each record (shop item) in the index contains its shop’s user id and set of shop filters, like atm: true, credit_card:true, lat,lng, etc.

I believe this would work fine; however, suppose a shop changes one of its filters, like they no longer accept credit credit cards. When this happens, I’d have to update all the items for the shop in the index. Since each shop has hundreds of items, this is costly.

The ideal situation would be to only have to update shop filters in one location.

So, what should my design be to solve this problem?

You’re absolutely correct with your first assumption.
Unfortunately, I don’t believe there is another way to achieve what you want. The design you described is exactly the one I would have recommended you.

Ok, suppose I have a set of shop id’s that have been filtered client side. I know I could use the OR operator in queries, but how many ORs can i use in one query?

I.e. something like: query all items where (credit_card == true) && (shop_id == id_1 || shop_id == id_2 || … || shop_id === id_n).

Ahh ignore this post. asdfjkajshdfa

Should I ignore the one before or also your question about OR filters?

I’ll answer anyway. :slight_smile:
There’s no actual limit, but the query will timeout if you have too many of them.
A rule of thumb is that ~1000 filters would be fine, but ~10000 wouldn’t.