I thought I'd run this by the group to see if anyone had any input.
We are looking to improve the search capabilities of our web app, which is
a large set of forms with a large schema containing many relationships. We
currently use hibernate so it's database agnostic, and just used db
queries. But now we'd like use start using some synonyms, and soundex
searches for names, as well as search the full text of some documents and
and a few database columns containing large bodies of text for reports.
I'm pretty sure that these requirements can be done with the database
capabilities, but obviously it's be a different implementation per DB.
Also, hibernate doesn't seem to support these advanced features, so we'd be
going back to native queries, with multiple implementations for those as
I've done a lot of reading on elasticsearch, and am just about to start a
proof of concept to see how hard it'll end up being. The real question is,
with so many interconnected entities, does a search index really make
sense? We'd have to do a lot of housekeeping to keep the indexes up to
date. The biggest example of the problem is addresses. An address can be
linked to about of 40 different entities. So if we wanted to search on a
name for example, and find all of their previous addresses, we'd have to
store the addresses in every name document. Then if an address changes,
we'd have to go find all names that had that address, and update it. As
well as 40 other entities.
So.. as you can see and probably already know, many to many relationships,
where one doesn't own the other seems pretty hard to search across. If you
have any thoughts or experience that you could share, I'd appreciate it
greatly. Alternatives as well.