DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=jQOSPcyNAPLIsptAfLQCriLHkebHawos4CExjotHDUMmaqftaW9y1Wk4yjCHgNd6u5N4qxm86Ix4tDa6BeWmL64fbmqF9KaNMtc2F5AMIt5+qNQvRkaQEZ8T2nSUNQVCNH/pFo0NwgSaDIshXLuN1+dRoBZ0JLOZ16BOHhoslyw=
Message-ID: <ea41f7570708190619u6daf7cep96430d20566d6159@mail.gmail.com>
Date: Sun, 19 Aug 2007 15:19:52 +0200
From: "mark overmeer" <markovermeer@gmail.com>
To: pgsql-performance@postgresql.org
Subject: schema design question
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_40988_15639817.1187529592569"

------=_Part_40988_15639817.1187529592569
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

 Hi,

Maybe not completely the wright place to ask but... I have this schema
design question (db is postgres of course). I have a couple of classes with
attributes. The only goal is to search the object that I want to find (which
is stored on the harddrive).

I have hundreds of classes that are similar but not the same. They all have
attributes/properties (type is probably String), e.g. (in pseudo code):

class A_version_1 {
   attribute1, attribute2, attribute3, ..., attributeN
}

class A_version_2 {
   attribute1, attribute3, ..., attributeN, attributeN+1, attributeN+2
}

class B_version_1 {
   attribute3, attribute4, attribute7, attributeN+3, ..., attributeN+M
}


Class A will have attributes from class B, class B will have attributes from
class C and so on. My initial thought was to use the (sometimes dreaded) EAV
model: class_id, object_id, attribute_id and attribute_value. In this way I
can make queries like:

SELECT CLASS_ID,
       OBJECT_ID
FROM   EAV_TABLE EAV
WHERE  EAV.ATTRIBUTE_ID = X
       AND EAV.ATTRIBUTE_VALUE = 'searchstring'
       AND EXISTS (SELECT OBJECT_ID
                   FROM   EAV_TABLE EAV2
                   WHERE  EAV.OBJECT_ID = EAV2.OBJECT_ID
                          AND EAV.CLASS_ID = EAV2.CLASS_ID
                          AND EAV2.ATTRIBUTE_ID = Y
                          AND EAV2.ATTRIBUTE_VALUE = 'searchstring2')

Results from this query could be entities from multiple classes!

The alternative is, as many people say: make a proper table for each class
which would lead to hundreds of unions. Is that good/performant? I thought
it would not...  To put all attributes of all classes (as columns) in one
table is impossible. The number of total attributes  should be in the
thousands.

A third alternative I came up with is the entity/value schema design where
each attribute would have its own table. A query would look like this:

SELECT CLASS_ID,
       OBJECT_ID
FROM   EV_X EAV
WHERE  EAV.ATTRIBUTE_VALUE = 'searchstring'
       AND EXISTS (SELECT OBJECT_ID
                   FROM   EV_Y EAV2
                   WHERE  EAV.OBJECT_ID = EAV2.OBJECT_ID
                          AND EAV.CLASS_ID = EAV2.CLASS_ID
                          AND EAV2.ATTRIBUTE_VALUE = 'searchstring2')

Which would be a nice way to partition the otherwise large table (but there
would be thousands of smaller tables).

The app I'm writing has to scale to about 1 billion attributes/value-pairs
in total. A normal search query would imply about 5 search terms (but there
could be 20). Any suggestions/remarks (I think the EXISTS should be replaced
by an IN, something else)? Did anyone implement such a search method (or did
they decide to make a different design)? Did it work/scale?

Thanks in advance,

Mark O.

------=_Part_40988_15639817.1187529592569
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline


Hi,<br>
<br>
Maybe not completely the wright place to ask but... I have this schema
design question (db is postgres of course). I have a couple of classes with attributes. The
only goal is to search the object that I want to find (which is stored
on the harddrive). <br>
<br>
I have hundreds of classes that are similar but not the same. They all
have attributes/properties (type is probably String), e.g. (in pseudo
code):<br>
<br>
class A_version_1 {<br>
&nbsp;&nbsp; attribute1, attribute2, attribute3, ..., attributeN<br>
}<br>
<br>
class A_version_2 {<br>
&nbsp;&nbsp; attribute1, attribute3, ..., attributeN, attributeN+1, attributeN+2<br>
}<br>
<br>
class B_version_1 {<br>
&nbsp;&nbsp; attribute3, attribute4, attribute7, attributeN+3, ..., attributeN+M<br>
}<br>
<br>
<br>
Class A will have attributes from class B, class B will have attributes
from class C and so on. My initial thought was to use the (sometimes
dreaded) EAV model: class_id, object_id, attribute_id and
attribute_value. In this way I can make queries like:<br>
<br>
<pre><pre>SELECT CLASS_ID,<br>       OBJECT_ID<br>FROM   EAV_TABLE EAV<br>WHERE  EAV.ATTRIBUTE_ID = X<br>       AND EAV.ATTRIBUTE_VALUE = &#39;searchstring&#39;<br>       AND EXISTS (SELECT OBJECT_ID<br>                   FROM   EAV_TABLE EAV2
<br>                   WHERE  EAV.OBJECT_ID = EAV2.OBJECT_ID<br>                          AND EAV.CLASS_ID = EAV2.CLASS_ID<br>                          AND EAV2.ATTRIBUTE_ID = Y<br>                          AND EAV2.ATTRIBUTE_VALUE
 = &#39;searchstring2&#39;)</pre></pre>
Results from this query could be entities from multiple classes! <br>
<br>
The alternative is, as many people say: make a proper table for each
class which would lead to hundreds of unions. Is that good/performant? I thought it would not...&nbsp;
To put all attributes of all classes (as columns) in one table is
impossible. The number of total attributes&nbsp; should be in the thousands. <br>
<br>
A third alternative I came up with is the entity/value schema design where each
attribute would have its own table. A query would look like this:<br>
<br>
<pre><pre>SELECT CLASS_ID,<br>       OBJECT_ID<br>FROM   EV_X EAV<br>WHERE  EAV.ATTRIBUTE_VALUE = &#39;searchstring&#39;<br>       AND EXISTS (SELECT OBJECT_ID<br>                   FROM   EV_Y EAV2<br>                   WHERE  
EAV.OBJECT_ID = EAV2.OBJECT_ID<br>                          AND EAV.CLASS_ID = EAV2.CLASS_ID<br>                          AND EAV2.ATTRIBUTE_VALUE = &#39;searchstring2&#39;)<br></pre></pre>Which would be a nice way to partition the otherwise large table (but there would be thousands of smaller tables). 
<br><br>The app I&#39;m writing has to scale to about 1 billion attributes/value-pairs in total. A normal search query would imply about 5 search terms (but there could be 20). Any suggestions/remarks (I think the EXISTS should be replaced by an IN, something else)? Did anyone implement such a search method (or did they decide to make a different design)? Did it work/scale?
<br><br>Thanks in advance,<br><br>Mark O.<br>

------=_Part_40988_15639817.1187529592569--