Many to Many Mapping with Solr

[Sandeep Mestry <sa...@gmail.com>]

Hi All,

Hope the day is going on well for you.

This question has been asked before, but I couldn't find answer to my
specific request. I have many to many relationship and the mapping table
has additional columns. Whats the best way I can model this into solr
entity?

For example: a user has many recordings and a recording belongs to many
users. But each user-recording has additional feature like type, number etc.
I'd like to fetch recordings for the user. If the user adds/ updates/
deletes a recording then that should be reflected in the search.

I have 2 options:
1) to create user entity, recording entity and user_recording entity
- this is good but it's like treating solr like rdbms which i mostly avoid..

2) user entity containing all the recordings information and each recording
containing user information
- this has impact on index size but the fetch and manipulation will be
faster.

Any guidance will be good..

Thanks,
Sandeep

Re: Many to Many Mapping with Solr

[Sandeep Mestry <sa...@gmail.com>]

Thanks Alexandre, even I am of the opinion not to use solr rdbms way but i
am concerned about the updates to the indexes. We're expecting around 500
writes per second to the database which will generate in >500 updates to
the index per second. If the entities are denormalised this will have an
impact on performance hence I was inclined to design it like db.

Joel,
I will explain it in a bit more detail what my use cases are, all of these
should be driven by search engine:

1) user logs in and the system should display all recordings for that user
2) user adds a recording, the system is updated with the additional
recording
3) user removes a recording, the system is updated with the recording
removed.
4) when the user searches for a recording, the system should only display
matches in his recordings. Every user-recording mapping has additional
properties which are also searchable attributes.

here, we are talking about 2M users and 500M recordings and this is
currently driven by database of size ~60-80GB.

I am going to do a small poc for these use cases and I will go with
denormalised entities with search requirements as my main focus. However,
if you have anything more to add, do let me know. I will be grateful.

Many Thanks,
Sandeep


On 29 April 2016 at 14:54, Joel Bernstein <jo...@gmail.com> wrote:

> We really still need to know more about your use case. In particular what
> types of questions will you be asking of the data? It's useful to do this
> in plain english without mapping to any specific implementation.
>
>
> Joel Bernstein
> http://joelsolr.blogspot.com/
>
> On Fri, Apr 29, 2016 at 9:43 AM, Alexandre Rafalovitch <arafalov@gmail.com
> >
> wrote:
>
> > You do not structure Solr to represent your database. You structure it
> > to represent what you will search.
> >
> > In your case, it sounds like you want to return 'user-records', in
> > which case you will index the related information all together. Yes,
> > you will possibly need to recreate the multiple documents when you
> > update one record (or one user). And yes, you will have the same
> > information multiple times. But you can used index-only values or
> > docvalues to reduce storage and duplication.
> >
> > You may also want to have Solr return only the relevant IDs from the
> > search and you recreate the m-to-m object structure from the database.
> > Then, you don't need to store much at all, just index.
> >
> > Basically, don't think about your database as much when deciding Solr
> > structure. It does not map one-to-one.
> >
> > Regards,
> >    Alex.
> > ----
> > Newsletter and resources for Solr beginners and intermediates:
> > http://www.solr-start.com/
> >
> >
> > On 29 April 2016 at 20:48, Sandeep Mestry <sa...@gmail.com> wrote:
> > > Hi All,
> > >
> > > Hope the day is going on well for you.
> > >
> > > This question has been asked before, but I couldn't find answer to my
> > > specific request. I have many to many relationship and the mapping
> table
> > > has additional columns. Whats the best way I can model this into solr
> > > entity?
> > >
> > > For example: a user has many recordings and a recording belongs to many
> > > users. But each user-recording has additional feature like type, number
> > etc.
> > > I'd like to fetch recordings for the user. If the user adds/ updates/
> > > deletes a recording then that should be reflected in the search.
> > >
> > > I have 2 options:
> > > 1) to create user entity, recording entity and user_recording entity
> > > - this is good but it's like treating solr like rdbms which i mostly
> > avoid..
> > >
> > > 2) user entity containing all the recordings information and each
> > recording
> > > containing user information
> > > - this has impact on index size but the fetch and manipulation will be
> > > faster.
> > >
> > > Any guidance will be good..
> > >
> > > Thanks,
> > > Sandeep
> >
>

Re: Many to Many Mapping with Solr

[Joel Bernstein <jo...@gmail.com>]

We really still need to know more about your use case. In particular what
types of questions will you be asking of the data? It's useful to do this
in plain english without mapping to any specific implementation.


Joel Bernstein
http://joelsolr.blogspot.com/

On Fri, Apr 29, 2016 at 9:43 AM, Alexandre Rafalovitch <ar...@gmail.com>
wrote:

> You do not structure Solr to represent your database. You structure it
> to represent what you will search.
>
> In your case, it sounds like you want to return 'user-records', in
> which case you will index the related information all together. Yes,
> you will possibly need to recreate the multiple documents when you
> update one record (or one user). And yes, you will have the same
> information multiple times. But you can used index-only values or
> docvalues to reduce storage and duplication.
>
> You may also want to have Solr return only the relevant IDs from the
> search and you recreate the m-to-m object structure from the database.
> Then, you don't need to store much at all, just index.
>
> Basically, don't think about your database as much when deciding Solr
> structure. It does not map one-to-one.
>
> Regards,
>    Alex.
> ----
> Newsletter and resources for Solr beginners and intermediates:
> http://www.solr-start.com/
>
>
> On 29 April 2016 at 20:48, Sandeep Mestry <sa...@gmail.com> wrote:
> > Hi All,
> >
> > Hope the day is going on well for you.
> >
> > This question has been asked before, but I couldn't find answer to my
> > specific request. I have many to many relationship and the mapping table
> > has additional columns. Whats the best way I can model this into solr
> > entity?
> >
> > For example: a user has many recordings and a recording belongs to many
> > users. But each user-recording has additional feature like type, number
> etc.
> > I'd like to fetch recordings for the user. If the user adds/ updates/
> > deletes a recording then that should be reflected in the search.
> >
> > I have 2 options:
> > 1) to create user entity, recording entity and user_recording entity
> > - this is good but it's like treating solr like rdbms which i mostly
> avoid..
> >
> > 2) user entity containing all the recordings information and each
> recording
> > containing user information
> > - this has impact on index size but the fetch and manipulation will be
> > faster.
> >
> > Any guidance will be good..
> >
> > Thanks,
> > Sandeep
>

Re: Many to Many Mapping with Solr

[Alexandre Rafalovitch <ar...@gmail.com>]

You do not structure Solr to represent your database. You structure it
to represent what you will search.

In your case, it sounds like you want to return 'user-records', in
which case you will index the related information all together. Yes,
you will possibly need to recreate the multiple documents when you
update one record (or one user). And yes, you will have the same
information multiple times. But you can used index-only values or
docvalues to reduce storage and duplication.

You may also want to have Solr return only the relevant IDs from the
search and you recreate the m-to-m object structure from the database.
Then, you don't need to store much at all, just index.

Basically, don't think about your database as much when deciding Solr
structure. It does not map one-to-one.

Regards,
   Alex.
----
Newsletter and resources for Solr beginners and intermediates:
http://www.solr-start.com/


On 29 April 2016 at 20:48, Sandeep Mestry <sa...@gmail.com> wrote:
> Hi All,
>
> Hope the day is going on well for you.
>
> This question has been asked before, but I couldn't find answer to my
> specific request. I have many to many relationship and the mapping table
> has additional columns. Whats the best way I can model this into solr
> entity?
>
> For example: a user has many recordings and a recording belongs to many
> users. But each user-recording has additional feature like type, number etc.
> I'd like to fetch recordings for the user. If the user adds/ updates/
> deletes a recording then that should be reflected in the search.
>
> I have 2 options:
> 1) to create user entity, recording entity and user_recording entity
> - this is good but it's like treating solr like rdbms which i mostly avoid..
>
> 2) user entity containing all the recordings information and each recording
> containing user information
> - this has impact on index size but the fetch and manipulation will be
> faster.
>
> Any guidance will be good..
>
> Thanks,
> Sandeep