Below is a imaginary - simplified use case ; but real world use case mimics such many-to-many relationships.
Say there is a work order -> which has a patient. patient entity within work order has patient's SSN, name, address, age, weight, illness etc.
Today work order id=1 with patient Social security number = 111-11-1111 and name =John Doe came in, into the system. John Doe's illness = fever.
After 2 months work order id =2 with patient social security number = 111-11-1111 same patient name = John Doe came in, into the system. this time John Doe's illness = back pain
Please ignore audit trail requirements for now so we don't get derailed from original question.
Now work order 1 can be related to some other work order 3. Basically work order < -> patient is many-to-many relationship. Also in db work order and patient are exact same data structure with different attributes and values. Real world use case has more details but irrelevant for this question.
In relational DB work order < -> patient relationship is maintained via a join table.
In designing elastic search index document structure ; following is what I am thinking about;
Have a single index with one type "patient",
for patient data:
{
"patient_ssn" : "111-11-1111",
"patient_fname" : "John",
"patient_lname" : "Doe",
"related_workorders" [ 2345,8979]
}
second type for "workorder"
{
"wo_id" : 2345,
....
}
Now query is find me all work orders where patient's SSN is 111-11-1111. So there will be two queries : first to find patient data : and then find all work order data for all values from patient.related_workorders; . so in this case ; final output will have work order as a parent (with all of it's data) and patient has it's child.
Two queries are using application side join mentioned here . In this case a single patient record will be updated quite frequently. Is there any downside of using application side join in this particular case ?
I am aware of other options mentioned here . Also have seen this this and this but appears application side join will be most suitable in my use case?
