Could supply some sample user queries and some sample data the queries
should match? In other words, how do your users expect to "view" the data?
If you are simply trying to replicate full SQL queries in Solr, you're
probably going to be disappointed, but if you look at what queries your
users are likely to want to enter, maybe it won't be so bad.
And maybe Solr's limited "join" capabilities might be sufficient to bridge
the gap between a single flat schema and many relational tables.
http://wiki.apache.org/solr/Join
Join support is there, but don't leap before you think carefully.
-- Jack Krupansky
-----Original Message-----
From: Thomas J. Brennan
Sent: Monday, September 24, 2012 10:23 AM
To: solr-user@lucene.apache.org
Subject: need best solution for indexing and searching multiple, related
database tables
I have a requirement to search multiple, related database tables. Sometimes
I need to join two tables, sometimes three or four and possibly more. The
tables will generally store structured data related to individuals or
organizations. This will include things like company, contact and address
tables and may include other child tables like products, assets, etc. It is
something of a moving target. Record counts are commonly in the tens of
millions but can be upwards of a few hundred million or even much more.
My understanding is that denormalization is most commonly the preferred
solution. For two tables that is pretty straightforward. For three or four
or more tables, or many to many relationships, and depending on the record
counts, this can generate a lot of redundant data, indexing time, etc.
Any information on the best way to design a single approach to this problem
or any options I might employ like faceted search, NoSQL (based on my
limited research I am guessing this is not a solution), etc. would be
greatly appreciated.
Answers that are terribly obvious, even to a newb, are a tiny bit annoying.
Things like "you should test several scenarios" or "there is no one good
solution". I really do appreciate any suggestions that would help me solve
this problem.
Biff
P.S. - I did search the existing posts and found some related topics but
nothing as specific as I was looking for.
