Hi - I’m running into the same relational issue and have read this and similar threads for how to “do a lookup” across what are 2+ source tables. I’d like to summarize my understanding. Can you please confirm that this is true of what you’d recommend myself and others with this same use case should do?
Following the above scenario:
@jontan has two source tables:
- Users (info about users including user’s name)
- Posts (info about posts users have authored)
The use case: Do a search for a subset of Posts by User name.
Method #1 (Recommended method if <1000 Posts per User)
Have 2 indexes: Users and Posts. Add array-type field to Users index containing a list of the objectIDs for each Post corresponding to that user.
Each time the user writes a new post, a record is 1st added to the Post Index which creates a new Post_ObjectID. Then a subsequent operation is needed to update the Post_ObjectIDs in the users index to include the new Post_ObjectID.
If the user updates her name, an operation is needed to do a partial update on the Users index for name.
Method #2 (Could handle scenarios where there are >1000 Posts per User)
Have only one index that includes both the Post info and User info.
In this scenario, all new POST records need to include first and last name of the User. If the user changes her name, a combination of browse/filter on users name is needed to update ALL posts for the user.
Is this correct? And is your recommendation to…?:
Go with method #1 if:
- There are <1000 Posts or whatever is being arrayed
- The field to be updated, in this case name, is something that will change frequently. I realize in this specific example name changed infrequently, but were there another frequently changing users field that was used for the search, then it would be preferable to store the Post_ObjectIDs in the users table
Go with method #2 if:
- There are >1000 Post per user and thus it doesn’t work to store the Post_ObjectIDs as an array in the Users table.
- Or, the search field, in this case name, is unlikely to change. In the event the user updates her name, the mass browse update for all historic posts is costly, so this method is not recommended if method #1 is possible.
- If name, for some reason, changed frequently, the suggestion would be to limit the frequency of updates (similar to what is suggested here)
Is this summary correct? Am I missing any new developments as the thread is from 2017?
PS - I suspect this is a very common use case. It would be nice to see an example a tutorial for it in docs.