Search code examples
asp.netasp.net-mvc-3solrsolrnet

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

  • Miscellaneous1 (9)
  • Miscellaneous2 (39)
  • Miscellaneous3 (49)

Miscellaneous-Cattegory2

  • Miscellaneous5 (59)
  • Miscellaneous6 (69)

Miscellaneous-Cattegory3

  • Miscellaneous7 (7)
  • Miscellaneous8 (8)

Miscellaneous-Cattegory4

  • Miscellaneous9 (19)
Solution

  • 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.