we want to use elastic search for e-commerce statistics. In our web view we have the possibility to filter products by sales, attributes like colors, categories, full text search etc.
The result is a table of products with statistics on sales, inventory changes and product attributes.
With the current implementation we calculate sales kpis on indexing for a period of 90 days, but in the future we want to calculate them on-the-fly. Our mysql schema looks like this:
Product models 1:n Product variants 1:n Product units Orders 1:n Order lines n:n Product units
For your information: The table of order lines has 6 million lines and a total size of 2 GB. The product tables look similar.
Is there an smart way to index all data with relations in the Elastik search? For example, should we store all data in one document: A product document has all attributes and a list of all orders and stock changes?
Or should we split it into different documents? Is there a possibility to join them then?