CODEWITHSUNDEEP
asp.netasp.net-mvc-3solrsolrnet
HW90
HW90

Reputation: 1983

How to index ralational Database with solr?

As I explained in my post a few days before I'm programming an ASP MVC3 multi-language website which should contain facetted search, full text search and a distance search. To realize that I've installed solr 3.3 on a Tomcat 7. I'm also successfully integrated a dataimporthandler.

Now I want to index the data from my relational ms sql database. I read the index structure looks like one table containing all the data of one object. That means if I've got a object like a car my schema catains fields like Branding, Color and so on.

  1. But what about n-m realtions? Does the index "table" have one column for each relation?
  2. And what about multi language items? Should I create one object/row int the index for each language?
  3. And should I save just the id of objects in the index or the whole names?
  4. And last how to index (query) a Object like on the database image? (I read something about dynamic fields and multiplevalue fields but I'm not sure if it is the solution for my problem)

I've a example of a database design I'm talking about attached.

Thanks for all the answers!!!

enter image description here

Update: The people should be able to have different way to search. They should have the possibility to search the tbl_text_local.text by full text searching and the miscellaneous are are facettes. The Result should be a list of objects that match to the search and a list of facetts.

But how should I index the Miscellaneous? Is there a posibility to index them in a form like that:

<cattegory name = "cat1">

<Miscellaneous>
name...
</Miscellaneous>

<Miscellaneous>
...
</Miscellaneous>

<Miscellaneous>
...
</Miscellaneous>

</cattegory>


<cattegory name = "cat2">

<Miscellaneous>
</Miscellaneous>

<Miscellaneous>
</Miscellaneous>

<Miscellaneous>
</Miscellaneous>

</cattegory>

People should have a searchfield like:

Text input (to search the text)

Facettes:

Miscellaneous-Cattegory1

Miscellaneous-Cattegory2

Miscellaneous-Cattegory3

Miscellaneous-Cattegory4

Upvotes: 0

Views: 484

Answers (1)

Mauricio Scheffer
Mauricio Scheffer

Reputation: 99730

There is no single, "best" way to model relationships in Solr. Unlike relational databases, where you design tables by following normalization, in Solr the schema design is very much ad-hoc, a function of the searches you will perform on the index. Ask yourself these questions as guidance:

  • What are users searching for? What is the "result type"? The schema should be designed around this.
  • What information do I need to facet?
  • What information do I need to include in full-text search?
  • What information do I need to use to sort results?
  • What information will I search by? I.e. what information will I use to filter search results, and how will I use that information?
  • What will I process at index-time and what will I process at query-time?

Finally, don't be afraid of duplicating data in the index for specific search purposes.

Upvotes: 2

Related Questions