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:
- Create an index named shop_items which contains all items for all shops.
- 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?