Styjun

We have an online shop that stores product details in three tables:

1. Products (Name, Description, Brand) - total 10.000 entries


2. Product-Variants (Size, Price, Availability) - total 80.000 entries


3. Product-Attributes (Colors) - total 60.000 entries

That gives a total combination of about 400.000 items.

Example:

• Product is "Nike Air Max"


• Variants are "Size 36, 70 USD", "Size 37, 72 USD", "Size 38, 74 USD"


• Attributes are "Color red" and "Color black"


• Total 6 combinations ("Nike Air Max + 36 + red", "Nike Air Max + 36 + black" ...)

I'm looking for a way to store the elements in elastic to get best filter performance, for queries like "Give me all products in size 34 - 37 in colors red or orange, that cost below 90 USD"

How should I reflect this setup in Elasticsearch, i.e. which indices should I create to get optimal search and filter performance (index-performance is not so important)

Note: I've seen keywords like "denormalization", "nested documents" and "child documents" but not sure on which approach I should read more and implement.

You have 2 ways to do this

1. create one document by kind of shoes (=Product) each of them will store all information about this product. This will minimize the number of documents in your cluster.

Something like

shoes
 "name": "type": "keyword" , 
 "brand": "type": "keyword" , 
 "Description": 
 "type": "string",
 "index": "not_analyzed"
 

 nested:
 variant:
 size: "type": "integer" ,
 price: "type": "float" ,
 availability: "type": "integer" ,
 colors: "type": "keyword" 

2. Create one document by shoes, you will have a lots of documents.

.

shoes
 "name": "type": "keyword" , 
 "brand": "type": "keyword" , 
 "Description": 
 "type": "string",
 "index": "not_analyzed"
 

 size: "type": "integer" ,
 price: "type": "float" ,
 availability: "type": "integer" ,
 colors: "type": "keyword" 

If you have to work with query like you said, the second solution would be better (you wont have to filter nested fields, stats or aggregation will be easy and 400k items will be easy for elasticsearch), if you are looking to make a search engine for a website I think this is better. If you need to make stats, or a backoffice tools by "Product", the first model could be better.

(drumrolls...) It depends!
To be a bit more constructive, it depends on how you want to fetch those products. If you want to search for a red, size 40 specifically, you'll probably need nested documents.
Here's a short example:

"name":"Air max", variants: [color:"red", size:"36", color:"black", size:"40"]

Now if we consider that variants are nested documents, and you're still looking for a size 40 red, then ES will correclty return 0 results.

Without nested documents, the internal index will look something like this:

"name":"Air max", variants.color:"red" "black", variants.size:"36" "40"

And with that kind of data structure, ES will return you the document for a size 40 red.