From: | "Aaron Bono" <postgresql(at)aranya(dot)com> |
---|---|
To: | "Paul S" <plabrh1(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org, stefan(dot)arentz(at)gmail(dot)com |
Subject: | Re: Querying for name/value pairs in reverse |
Date: | 2006-07-15 22:26:28 |
Message-ID: | bf05e51c0607151526w1253f993hfe9fd79dbe75c956@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
==================================================================
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-07-16 02:22:32 | Re: Regular Expression in SQL |
Previous Message | Aaron Bono | 2006-07-15 22:21:02 | Re: Querying for name/value pairs in reverse |