Hi, i have a mySQL Database with the following tables. Company, maincategory, subcategory and listing. Subcategory has a field with a one to one relation to maincategory. And listing has 3 fields with relations to company,maincategory subcategory. In words. Every Company could have many listings. I need to search for a subcategory name like medicine and must retrieve all companies who are listet for this subcategory. My question is now: How can i model this with elasticsearch? Which way would be the right? Thanks a lot for some hints and some help.
The best bet would be to flatten it as much as possible.
You have to denormalize the data. To make the search you want you would declare a Subcategory type with 2 fields:
- name (for the search)
- companies (could be just an array of company ids, or it could be an array of objects with whatever company fields you need to display in the search results)
For some more background the following blog post might be interesting for you: