I have Student model and Class model, and they have many-to-many relationship.
(A student can register for many classes, and a class can include many students)

I have Enrollment table as a join table.
(you can get the picture in the following website)
https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help/many-to-many-relationships.html

■ Student table
attributes:
・name
・age

■ Class table
attributes:
・className
・desrciption

■ Enrollment table
attributes:
・studentId
・classId

I think this is typical many-to-many relationship and I'm working this with MySQL and rails.

I would like to know if I could implement this relationships on Elasticsearch. I read some articles which say that Elasticsearch does not support it, but is there any hacks or best practice for this?

CodePudding user response：

Your use case is better suited for relational database.

Store and query data separately in elastic search and join data in API (business side).

Elasticsearch does not have concept of joins. It is based on concept of denormalization. Denormalization is used to improve the response time of a query at the expense of adding redundant data. Data from different tables can be combined and stored in single place , avoiding the need of joins, which results in faster retrieval at cost of storage(duplicity of data).

Your document which is equivalent to row in a table can be modeled as below

{
   studentName:"",
   "age":"",
   ....
   classes:[
              {
                 className:"class1",
                 ...
              },
              {
                 className:"class2",
                 ...
              }
   ]
}

For each student store all the classes associated with him/her. This will cause duplication of class data across students. It will lead to faster search but slower update as any change in class data will need to be updated across students.

You can also model your data other way around with class as parent and array of students under it. Choose based on your use case.

For your sub model you can use different data types.

1. Object -- data is flattened.
2. Nested relations is maintained between properties of model.
3. Child/Parent sub model becomes different document. It is used when sub model data changes frequently. It does not case re-indexing of parent document.