Re: Querying for name/value pairs in reverse

On 7/15/06, Paul S <plabrh1(at)gmail(dot)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
==================================================================