DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=U8EwPHTXv4IxNfEybeUj0fBik88qRZci/T2VNSEFrsd9XNhKXZQMix2uAAFrMxuz9aPRWQr8cUNtT6WPp4DJhNwa02fdAnYumD6FUaWnW4/KDVKkTidvUk8EjWiAUBcJeCkSx3vLwZJ3NBmKsPSCRlHD2Lrvx5oVwd0hFQjix6A=
Message-ID: <bf05e51c0607151526w1253f993hfe9fd79dbe75c956@mail.gmail.com>
Date: Sat, 15 Jul 2006 17:26:28 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "Paul S" <plabrh1@gmail.com>
Subject: Re: Querying for name/value pairs in reverse
Cc: pgsql-sql@postgresql.org, stefan.arentz@gmail.com
In-Reply-To: <866624ef0607150500h537f496byc489affa019f996d@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_42818_20095577.1153002388259"
References: <adf34c300607150224k7d4243f6ye52a21bf0a1bd0a6@mail.gmail.com>
	<866624ef0607150500h537f496byc489affa019f996d@mail.gmail.com>

------=_Part_42818_20095577.1153002388259
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 7/15/06, Paul S <plabrh1@gmail.com> wrote:
>
> This is definitely doable.
>
> one "Set" way that I could think of doing this would be to first compile a
> temp table with all of the Value/Pairs that your looking to search for and
> then just JOIN the ID's (in this case it would be the Value and Pair) to the
> ErrorValues table.  This should give you all of the ErrorID's in the
> ErrorValues table which would then allow you to JOIN up against the Errors
> table to get information like CreateDate.
>

This works - the subselect I sent earlier kind of does this (the subselect
can act as a temp table in memory so you don't have to create a physical
one).

Another way, would be to use XML instead of Value/Pair to turn it into a
> Node and Value type of thing.  You could then use XQuery to search inside of
> the XML attribute for what you were looking for.  SET theory would be a
> better alternative but this is just a different idea.
>

I wonder if the use of XML in a database is a very good idea.  (am I
treading on religious territory here?)  I can think of some examples where
XML can be useful but the problem I see with it is that your data structure
is embedded in a single field and your database schema does not describe
your data structure very well anymore.  I always like to use the database
schema as a way to document the data structure so if you have the DB
diagrams, you can understand everything there.

One Naming convention tip, I like to name my PrimaryKey's something more
> descriptive than just ID.  You'll notice that your ErrorValues table had to
> include the foreign key called ErrorID that actually relates to attribute ID
> in the Errors table.  When your looking at miles and miles of code or
> reviewing JOIN syntax " a.ID = b.ErrorID" sometimes it's easier to
> validate if it looks like this. "a.ErroID = b.ErrorID".  Just my 2
> cents...
>

I wholeheartedly agree.  If you don't adopt a good naming convention like
this, you will come to regret it as your application grows in size and
complexity.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

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

On 7/15/06, <b class="gmail_sendername">Paul S</b> &lt;<a href="mailto:plabrh1@gmail.com">plabrh1@gmail.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><div>This is definitely doable.&nbsp; </div>
<div>&nbsp;</div>
<div>one &quot;Set&quot; way that I could think of doing this would be to first compile a temp table with all of the Value/Pairs that your looking to search for and then just JOIN the ID's (in this case it would be the Value and Pair) to the ErrorValues table.&nbsp; This should give you all of the ErrorID's in the ErrorValues table which would then allow you to JOIN up against the Errors table to get information like CreateDate.&nbsp;
</div></div></blockquote><div><br>This works - the subselect I sent earlier kind of does this (the subselect can act as a temp table in memory so you don't have to create a physical one). <br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><div>Another way, would be to use XML instead of Value/Pair to turn it into a Node and Value type of thing.&nbsp; You could then use XQuery to search inside of the XML attribute for what you were looking for.&nbsp; SET theory would be a better alternative but this is just a different idea.&nbsp;
</div></div></blockquote><div><br>I wonder if the use of XML in a database is a very good idea.&nbsp; (am I treading on religious territory here?)&nbsp; I can think of some examples where XML can be useful but the problem I see with it is that your data structure is embedded in a single field and your database schema does not describe your data structure very well anymore.&nbsp; I always like to use the database schema as a way to document the data structure so if you have the DB diagrams, you can understand everything there.
<br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div><div>One Naming convention tip, I like to name my PrimaryKey's something more descriptive than just ID.&nbsp; You'll notice that your ErrorValues table had to include the foreign key called ErrorID that actually relates to attribute ID in the Errors table.&nbsp; When your looking at miles and miles of code or reviewing JOIN syntax &quot;
<a href="http://a.ID" title="http://a.ID" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">a.ID</a> = b.ErrorID&quot; sometimes it's easier to validate if it looks like this. &quot;a.ErroID = b.ErrorID
&quot;.&nbsp; Just my 2 cents...</div></div></blockquote><div><br>I wholeheartedly agree.&nbsp; If you don't adopt a good naming convention like this, you will come to regret it as your application grows in size and complexity.<br>
</div></div><br>==================================================================<br>&nbsp;&nbsp; Aaron Bono<br>&nbsp;&nbsp; Aranya Software Technologies, Inc.<br>&nbsp;&nbsp; <a href="http://www.aranya.com">http://www.aranya.com</a><br>==================================================================

------=_Part_42818_20095577.1153002388259--