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==================================================================
