You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Bruno Dusausoy <bd...@yp5.be> on 2013/01/10 14:34:36 UTC
Database denormalization
Hi,
I'm quite new with Solr.
I've searched for this but found no answer to my needs, which seems to
be quite common though.
Let's say I have three entities in my database (I have more than that
but it's for the sake of simplicity) :
File *------* Procedure *------* Organ
1 n 1 m
All three entities only contain an id (let's say "file_ref",
"procedure_ref" and "organ_ref") which are indexed and stored strings.
I'd like to be able to retrieve documents containing all three fields -
obviously since they are stored.
But if I query for everything ("*:*") I don't get (file count)*n*m
documents, only (file count) documents.
I'm using this kind of SQL query to import the data :
<dataConfig>
<!-- ... -->
<document name="files">
<entity name="file" pk="file_id" query="select file_ref as file_id
from file">
<field column="file_id" name="file_ref"/>
<entity name="procedure" pk="proc_id" query="select procedure_ref
as proc_id from procedure where file_fk = ${file_id}>
<field column="proc_id" name="procedure_ref"/>
<entity name="organ" pk="organ_id" query="select organ_ref as organ_id
from organ where procedure_fk = ${proc_id}>
<field column="organ_id" name="organ_ref"/>
</entity>
</document>
</dataConfig>
Any thoughts/links about how to properly denormalize - and obtain a
cartesian product - a relational database ?
Thanks.
--
Bruno Dusausoy
Software Engineer
YP5 Software
--
Pensez environnement : limitez l'impression de ce mail.
Please don't print this e-mail unless you really need to.
Re: Database denormalization
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
On Thu, Jan 10, 2013 at 9:37 AM, Bruno Dusausoy <bd...@yp5.be> wrote:
> Have you tried inverting the order of your nested entities (organ as an
>> outer one, etc) and seeing if that's better for your needs?
>>
>> Interesting idea, never thought about that.
> But unfortunately my real case is much much more complex than that.
Well, I can only suggest based on what I know. :-)
The other option might be to use rootEntity=false on your top two layers
and true for the most-inner one. It feels nearly the same as the other way
I suggested, but perhaps it will be a better match for your complex needs.
Regards,
Alex.
Personal blog: http://blog.outerthoughts.com/
LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
- Time is the quality of nature that keeps events from happening all at
once. Lately, it doesn't seem to be working. (Anonymous - via GTD book)
Re: Database denormalization
Posted by Bruno Dusausoy <bd...@yp5.be>.
Alexandre Rafalovitch a écrit :
> Not sure if I fully understood that, but it seems to be that you are
> currently indexing "file" with extra child info, when you want to be
> indexing "organs" with extra parent info.
>
Given that File with file_ref = 1,
it has two procedures :
- proc_ref = 1,
- proc_ref = 2
Proc_ref 1 has two organs :
- organ_ref = 1,
- organ_ref = 2,
Proc_ref 2 has one organ :
- organ_ref = 3
The result would be (just like a join on the three tables in database,
actually) :
Document 1:
- file_ref 1
- proc_ref 1
- organ_ref 1
Document 2:
- file_ref 1
- proc_ref 1
- organ_ref 2
Document 3:
- file_ref 1
- proc_ref 2
- organ_ref 1
...
> Have you tried inverting the order of your nested entities (organ as an
> outer one, etc) and seeing if that's better for your needs?
>
Interesting idea, never thought about that.
But unfortunately my real case is much much more complex than that.
Regards.
--
Bruno Dusausoy
Software Engineer
YP5 Software
--
Pensez environnement : limitez l'impression de ce mail.
Please don't print this e-mail unless you really need to.
Re: Database denormalization
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
Not sure if I fully understood that, but it seems to be that you are
currently indexing "file" with extra child info, when you want to be
indexing "organs" with extra parent info.
Have you tried inverting the order of your nested entities (organ as an
outer one, etc) and seeing if that's better for your needs?
Regards,
Alex.
Personal blog: http://blog.outerthoughts.com/
LinkedIn: http://www.linkedin.com/in/alexandrerafalovitch
- Time is the quality of nature that keeps events from happening all at
once. Lately, it doesn't seem to be working. (Anonymous - via GTD book)
On Thu, Jan 10, 2013 at 8:34 AM, Bruno Dusausoy <bd...@yp5.be> wrote:
> Hi,
>
> I'm quite new with Solr.
> I've searched for this but found no answer to my needs, which seems to be
> quite common though.
>
> Let's say I have three entities in my database (I have more than that but
> it's for the sake of simplicity) :
>
> File *------* Procedure *------* Organ
> 1 n 1 m
>
> All three entities only contain an id (let's say "file_ref",
> "procedure_ref" and "organ_ref") which are indexed and stored strings.
>
> I'd like to be able to retrieve documents containing all three fields -
> obviously since they are stored.
> But if I query for everything ("*:*") I don't get (file count)*n*m
> documents, only (file count) documents.
>
> I'm using this kind of SQL query to import the data :
>
> <dataConfig>
> <!-- ... -->
> <document name="files">
> <entity name="file" pk="file_id" query="select file_ref as file_id
> from file">
> <field column="file_id" name="file_ref"/>
> <entity name="procedure" pk="proc_id" query="select procedure_ref as
> proc_id from procedure where file_fk = ${file_id}>
> <field column="proc_id" name="procedure_ref"/>
> <entity name="organ" pk="organ_id" query="select organ_ref as
> organ_id from organ where procedure_fk = ${proc_id}>
> <field column="organ_id" name="organ_ref"/>
> </entity>
> </document>
> </dataConfig>
>
> Any thoughts/links about how to properly denormalize - and obtain a
> cartesian product - a relational database ?
>
> Thanks.
> --
> Bruno Dusausoy
> Software Engineer
> YP5 Software
> --
> Pensez environnement : limitez l'impression de ce mail.
> Please don't print this e-mail unless you really need to.
>