# Design for relationships across indices in ES?
**URL:** https://discuss.elastic.co/t/design-for-relationships-across-indices-in-es/22452
**Category:** Elasticsearch
**Created:** [February 28, 2015, 5:42pm UTC](https://discuss.elastic.co/t/design-for-relationships-across-indices-in-es/22452 "2015-02-28T17:42:34Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Jo\_Emil\_Holen](https://discuss.elastic.co/u/Jo_Emil_Holen)
#### Post date: [February 28, 2015, 5:42pm UTC](https://discuss.elastic.co/t/design-for-relationships-across-indices-in-es/22452/1 "2015-02-28T17:42:34Z")
Hi!
I have a database with quite a lot of relations across tables that I want
to replicate it to ES to make it searchable, and avoiding having to make a
search engine towards the database as it'd be really heavy from time to
time trying to be dynamic on what one can search..
For instance, it contains a table for _users,_ _groups_ and _group\_members_.
The tables would look something like this:
_Users_
| id | name |
_Groups_
| id | name |
_Group\_members_
| group\_id | user\_id |
A typical search would then be "List all users _not_ in _group"._
\*Note: \*This is a really simple example, other searches will be "worse"
than this.
Is it even possible to do this in ES?
Another solution I'm thinking of is just having a _memberof_ param on the
user document in ES which would be updated regurarly. Would this be a
better solution than even thinking of trying to do realtionships?
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/d701b0ec-ac46-488e-a94c-2b860da922ba%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d701b0ec-ac46-488e-a94c-2b860da922ba%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Ognjen\_Antonic](https://discuss.elastic.co/u/Ognjen_Antonic)
#### Post date: [March 1, 2015, 7:57am UTC](https://discuss.elastic.co/t/design-for-relationships-across-indices-in-es/22452/2 "2015-03-01T07:57:04Z")
Hi!
Probably the simplest approach would be to store your info into
Elasticsearch in a completely denormalized form.
For example:
select u.first\_name, u.last\_name, g.name as group\_name
from users u
join group\_members gm
on gm.user\_id = u.id
join groups g
ob g.id = gm.group\_id
Then just replicate any changes into Elasticsearch using regular time
intervals.
Regards,
Ogo
Dne sobota, 28. februar 2015 18.42.36 UTC+1 je oseba Jo Emil Holen napisala:
> Hi!
>
> I have a database with quite a lot of relations across tables that I want
> to replicate it to ES to make it searchable, and avoiding having to make a
> search engine towards the database as it'd be really heavy from time to
> time trying to be dynamic on what one can search..
>
> For instance, it contains a table for _users,_ _groups_ and
> _group\_members_.
>
> The tables would look something like this:
>
> _Users_
> | id | name |
>
> _Groups_
> | id | name |
>
> _Group\_members_
> | group\_id | user\_id |
>
> A typical search would then be "List all users _not_ in _group"._
>
> \*Note: \*This is a really simple example, other searches will be "worse"
> than this.
>
> Is it even possible to do this in ES?
>
> Another solution I'm thinking of is just having a _memberof_ param on the
> user document in ES which would be updated regurarly. Would this be a
> better solution than even thinking of trying to do realtionships?
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/a3f6b9cd-5f82-42d8-adda-66d30e4d8286%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a3f6b9cd-5f82-42d8-adda-66d30e4d8286%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@Jo\_Emil\_Holen](https://discuss.elastic.co/u/Jo_Emil_Holen)
#### Post date: [March 1, 2015, 1:39pm UTC](https://discuss.elastic.co/t/design-for-relationships-across-indices-in-es/22452/3 "2015-03-01T13:39:31Z")
Yeah, that seems like the best approach. Thanks for your input 🙂
søndag 1. mars 2015 08.57.04 UTC+1 skrev Ognjen Antonič følgende:
> Hi!
>
> Probably the simplest approach would be to store your info into
> Elasticsearch in a completely denormalized form.
>
> For example:
>
> select u.first\_name, u.last\_name, g.name as group\_name
> from users u
> join group\_members gm
> on gm.user\_id = u.id
> join groups g
> ob g.id = gm.group\_id
>
> Then just replicate any changes into Elasticsearch using regular time
> intervals.
>
> Regards,
>
> Ogo
>
> Dne sobota, 28. februar 2015 18.42.36 UTC+1 je oseba Jo Emil Holen
> napisala:
>
> > Hi!
> >
> > I have a database with quite a lot of relations across tables that I want
> > to replicate it to ES to make it searchable, and avoiding having to make a
> > search engine towards the database as it'd be really heavy from time to
> > time trying to be dynamic on what one can search..
> >
> > For instance, it contains a table for _users,_ _groups_ and
> > _group\_members_.
> >
> > The tables would look something like this:
> >
> > _Users_
> > | id | name |
> >
> > _Groups_
> > | id | name |
> >
> > _Group\_members_
> > | group\_id | user\_id |
> >
> > A typical search would then be "List all users _not_ in _group"._
> >
> > \*Note: \*This is a really simple example, other searches will be "worse"
> > than this.
> >
> > Is it even possible to do this in ES?
> >
> > Another solution I'm thinking of is just having a _memberof_ param on
> > the user document in ES which would be updated regurarly. Would this be a
> > better solution than even thinking of trying to do realtionships?
--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/628d1a95-5030-4fc0-855c-000460ecbba4%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/628d1a95-5030-4fc0-855c-000460ecbba4%40googlegroups.com).
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 12:29am UTC](https://discuss.elastic.co/t/design-for-relationships-across-indices-in-es/22452/4 "2017-07-06T00:29:13Z")