As I'm pretty new to ES, can you please help me out regarding a design question for the indices inside ES?
I want to accomplish the following:
There is a central repository of issues inside my system. Once in a while compliance reports are created which link to different issues of my repository. Whenever a Report is queried, the link to the issue should be resolved to get the latest information of this issue (e.g. Severity, Importance, Description, Solution, ...).
The central repository can be updated by our employees to refine the information (e.g. better description, ...)
To always guarantee that the latest data will be retrieved, I would create a separate index called "central_issue_repo", where all my issues are stored (and can be updated).
Whenever a report comes in, I'm sending it to a separate index (e.g. report_1) and link every issue by its unique id.
Now the following problem arises:
Whenever I want to query a report (containing up to 20000 issues) I also need to query the issues and match this information. The matching is needed because the issue information MUST be up-to-date.
This matching needs a lot of time because I need nested for-loops on the PHP side of my application to match the results.
In SQL I would have used a JOIN to get the information I need in a single query. Unfortunately, this is not possible in ES across indices.
Do you have any recommendation on how this could be improved?
Is my design ok, or just crap?