To expand a bit on the other replies, yes, your order data should definitely be denormalized into one single order scheme. We store orders this way in Solr, since near real-time search among live orders is a requirement for several of our systems.
Something non-Solr though - consider denormalizing your order data in your relational database as well. Sooner or later, you will get into trouble with keeping orders and associated products separated via normalization - unless you keep a history of all previous versions of a product, or you never change products. Say that a product changes its name one month after an order is placed - if you keep the data normalized, all previous orders will get the new name of the product - not the name it had when the order was placed. This behaviour is usually not sought after in my experience. This would, of course, also make a direct map to Solr more straightforward. -- Henrik Ossipoff Hansen On 1. dec. 2013 at 02.06.54, subacini Arunkumar (subac...@gmail.com<mailto://subac...@gmail.com>) wrote: Thanks Walter for the reply. Here is my complete requirement. Please let me know the possible solutions to address my requirement. * Two tables might have millions of records with 50 columns in each table * Expected output is same as what we get in SQL inner join say For Eg, I have two tables Product , Order table. *Product Table * id Name P1 ipad P2 iphone 4 P3 iphone 5 *Order Table* id order date product_id O1 1-Dec-2012 P1 O2 1-Dec-2012 P2 O3 2-Dec-2012 P2 *Expected Output: *I want to show the details in UI as below [SQL inner join ] O1 01-Dec-2012 ipad O2 1-Dec-2012 iPhone 4 O3 2-Dec-2012 iPhone 5 I tried setting up two solr cores , Product core & Order Core *Option 1: Using Solr Join* I got expected result but i was able to get columns only from one core (ie) total 3 records but only product table columns http://<….>/product/select?q=*&fq={!join from=product_id to=id fromIndex=order}* *Option 2: Using shards* Created third core, but number of records is sum of(Product core + Order Core) as documents are of different types and they are all unique(ie) 6 records So how could i generate a single document with all fields from two different document types in different cores. On Sat, Nov 30, 2013 at 8:04 AM, Walter Underwood <wun...@wunderwood.org>wrote: > 1. Flatten the data into a single table. > > 2. Solr does not seem like a good solution for order data, especially live > orders that need to be transactional. That is a great match to a standard > relational DB. > > wunder > > On Nov 30, 2013, at 12:15 AM, subacini Arunkumar <subac...@gmail.com> > wrote: > > > Hi > > > > We are using solr 4.4 . Please let me know the possible solutions to > > address my requirement. > > > > We have to fetch data from two tables Product , Order table. > > > > Product Table > > > > id Name > > P1 ipad > > P2 iphone 4 > > P3 iphone 5 > > > > > > Order Table > > > > id order date product_id > > O1 1-Dec-2012 P1 > > O2 1-Dec-2012 P2 > > O3 2-Dec-2012 P2 > > > > I want to show the details in UI as below > > > > O1 01-Dec-2012 > > > > > > On Sat, Nov 30, 2013 at 12:13 AM, subacini Arunkumar <subac...@gmail.com > >wrote: > > > >> Hi > >> > >> We are using solr 4.4 . Please let me know the possible solutions to > >> address my requirement. > >> > >> We have to fetch data from two tables Product , Order table. > >> > >> Product Table > >> > >> id Name > >> P1 ipad > >> P2 iphone 4 > >> P3 iphone 5 > >> > >> > >> Order Table > >> > >> id order date product_id > >> O1 > >> > >> > > -- > Walter Underwood > wun...@wunderwood.org > > > >
