How to model data for time-based joins?

I'm just getting started with ElasticSearch and I'm trying to figure out the best way to model my data. Any and all input is appreciated. My scenario is this:

I have a set of Stores which sell Products. Each day I get a list of Products that each Store has in stock. This is a boolean value -- either the Product is in stock or not for each Store.

I want to be able to search on a Store (for example by store name, id, location, etc) for a given date. I want to return in the search results the Store as well as a list of Products which the Store has had in stock at any point in the previous 1 month, based off the given date.

Somewhat naively, my initial approach is to have one index for the Stores and one index for the Products. The Stores index is straight forward -- I would index the Store names, locations, etc. The Products index would contain the Product information, a list of Stores where it is in stock, and the date. I'd add a new record to this index each day, based on the list of in stock Products.

At query time I would first query the Stores index to get the matching Stores, then query the Products index based on the matching Stores and the given date. I'd combine the results in my application, effectively doing a join between the two indices.

Is there a better way to do this?


I'd probably copy information regarding the store within the product object. Denormalisation that is.

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.