How to handle many-to-many relationships

Elastic Stack Elasticsearch
1

So far I have database with many to many relation. Tables is "lists" and "objects" where list contains many objects and objects could be in many lists. Now what I can not find is how to set such relations in Elasticsearch to get best results. I could use denormalized document, but then I will have problem with indexing. For example, if parent (List) is changed, all related objects in Elasticsearch have to be reindexed. And to do that php need to collect all object information from database and put them in Elasticsearch. So if list contains 100 000 objects it is slow and many times ends with memory or execution time limit exception. Requirement is to be searchable after data is changed in database.

I found that Elasticsearch has parent/child and allow update only parent data. But I can not find any solution for many to many relation and parent/child works only with one to many relation.

So what is best practice how to handle this situations? Any guidance would be appreciated.

2

Hi,

unfortunately the fact that ES is document-centric also makes it hard to apply some of the things you are used to from a relational storage system, and many-to-many relationships is one of the biggest issues there. But have you considered application-side joins? Depending on the kind of question your application needs to ask, this will require having to issue more than one query and then do the join in your application, but many times this is fast enough and a good tradeoff to make if in return you can index both entities separately.