Need help with approach for query - extensible meta data table

31 Aug 2006 3:57 PM

Cy Huckaba

I have a database structure that was built to be flexible and store extended
properties on items without having to change the database when a new extended
property added by a data import process. These extended props were not
originally meant for direct querying and now we do want to start query for
specific attributes in some cases.

example.

This is a SQL 2000 database.

Item {id, name, etc.} - these are the main entities
Attributes {id, name, DataType, etc}

ItemExtendedAttributes {RowID, ItemID, AttID, Value}

Each item may have 20 or 30 extended attributes that can be loaded very
easily into a named-value collection. I basically need to Pull back Items
where several ExtendedAttributes match some criteria.

I can actually do the queries using self joins but that is very cumbersome
and error prone if you have more than a couple criteria. Is there an easier
way to do this by possibly transposing the attributes into a temp table with
each value as field? Is there another approach. I'm just looking for
approaches and maybe a reference to functions or something I may be able to
look up.

Thank you,

31 Aug 2006 5:27 PM

Jim Underwood

I know this is not what you want to hear, but the easiest way to do this is
to eliminate the custome attributes and replace them with actual columns in
your tables. This approach usually makes a mess and the only ways I know to
query with it are to self join the table with one occurance for ever column
or use a lot of exists subqueries.

You may also try relational division (basically uses exists, but fairly easy
to code), which is described here:
http://www.dbazine.com/ofinterest/oi-articles/celko1

In your case your attributes table would be like PilotSkills and you would
create a table (maybe temp table) to store your criteria in, like the
Hangers table.

The best thing to do, however, is to redesign your database.

Show quote

"Cy Huckaba" <Cy Huck***@discussions.microsoft.com> wrote in message
news:A7E95553-61CB-4A88-B765-2F0FA9B85CA6@microsoft.com...
> I have a database structure that was built to be flexible and store
extended
> properties on items without having to change the database when a new
extended
> property added by a data import process. These extended props were not
> originally meant for direct querying and now we do want to start query for
> specific attributes in some cases.
>
> example.
>
> This is a SQL 2000 database.
>
> Item {id, name, etc.} - these are the main entities
> Attributes {id, name, DataType, etc}
>
> ItemExtendedAttributes {RowID, ItemID, AttID, Value}
>
> Each item may have 20 or 30 extended attributes that can be loaded very
> easily into a named-value collection. I basically need to Pull back Items
> where several ExtendedAttributes match some criteria.
>
> I can actually do the queries using self joins but that is very cumbersome
> and error prone if you have more than a couple criteria. Is there an
easier
> way to do this by possibly transposing the attributes into a temp table
with
> each value as field? Is there another approach. I'm just looking for
> approaches and maybe a reference to functions or something I may be able t
o
> look up.
>
> Thank you,
>

4 Sep 2006 7:12 AM

ML

Google for "relational division".

In a similar model I've used table-valued functions to return a list of
entity IDs that either had the same attribute of the same value. This
somewhat simplifies relational division queries, but has a seriously
nedgative impact on the performance - but AFAIK this can't be avoided in a
loos model such as this one.

Consider using XML to store attributes if you're on SQL 2005.

ML

---
http://milambda.blogspot.com/