From: Aaron Bono <postgresql@aranya.com>
To: Paul S <plabrh1@gmail.com>
Cc: pgsql-sql@postgresql.org
Cc: stefan.arentz@gmail.com
Subject: Re: Querying for name/value pairs in reverse
Date: Sat, 15 Jul 2006 17:26:28 -0500
Message-ID: <bf05e51c0607151526w1253f993hfe9fd79dbe75c956@mail.gmail.com> (raw)
In-Reply-To: <866624ef0607150500h537f496byc489affa019f996d@mail.gmail.com>
References: <adf34c300607150224k7d4243f6ye52a21bf0a1bd0a6@mail.gmail.com>
<866624ef0607150500h537f496byc489affa019f996d@mail.gmail.com>
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
==================================================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql@aranya.com, plabrh1@gmail.com, stefan.arentz@gmail.com
Subject: Re: Querying for name/value pairs in reverse
In-Reply-To: <bf05e51c0607151526w1253f993hfe9fd79dbe75c956@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox