SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Matching various attributes problem ...


Matching various attributes problem ...

Author
Message
bdcoder
bdcoder
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 194
I would like opinions from those that are more experienced than I in dealing with the following problem:

The system has a number of categories and each category has a varying number of attributes, for example:


Category -> Attributes:
C1 -> A11, A12, A13, A14, A15, A16, A17, A18, A19
C2 -> A21, A22, A23, A24, A25
C3 -> A31, A32, A33, A34, A35, A36, A37



So far in the design, the fewest number of attributes associated with a category is 6, the largest is 14 and there are only 9 categories defined (so far).

The system also requires the following tables:

A table (call it T1) where each widget may have none, any or all of the attributes above (from any category) associated with it, for example:


Widget -> Attributes:
W1 -> A11, A12, A21, A22, A31, A32
W2 -> A11, A12, A13, A14, A15, A16, A17, A18, A19, A21, A22, A23, A24, A25, A33
W3 -> NULL



A table (call it T2) in which users may save search criteria like the following examples:
1. Find all widgets with the attributes A11 or A12 present.

2. Find all widgets in category C1 with the attributes A11 and A19 present AND in category C2 with attributes A22 or A23 present AND in category C3 with attributes A33 or A34.

Users should be able to “store” their search criteria so the system will find any widgets that have any combination of attributes in any category, ie: A SQL query that can use the information in T2 to extract the appropriate result set from T1. This is done because the system sends out notifications at specific intervals when widgets are found that match a users saved search criteria.

The table T2 would need to store the following elements for each users search criteria:
- The category id
- The match type for attributes in the category (match any or match all)
- The attributes to be matched

Taking example 2 above, a user might store the following search criteria:
- Category: C1, MatchType: All, Attributes: A11, A19
- Category: C2, MatchType: Any, Attributes A22, A23
- Category: C3, MatchType: Any, Attributes A33, A34

The above search criteria would return widget W2 as it contains ( all of the attributes A11, A19 ) AND (any of the attributes A22, A23) AND ( any of the attributes A33, a34 ).

I am interested in knowing how the SQL experts would tackle such a problem, specifically:

1. The structure of the various tables, ie: categories, category_attributes, T1 and T2.

2. The SQL queries used on T1 and T2 that could produce the result sets shown in the examples above.

I’ve worked out a solution using bitmasks to encode the various category attributes, as bitmasks work extremely well in handling problems such as finding all or any attributes that might be present for various categories, however – I know several experts (Joe Celko – which by the way, your book SQL For Smarties I use all the time), would cringe at the very notion of using bitmasks in a SQL design – hence, my plea for better ideas for this problem.

Any and all ideas are welcome - thanks!
bitbucket-25253
bitbucket-25253
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9441 Visits: 25280
Please reply here

http://www.sqlservercentral.com/Forums/Topic1073727-1292-1.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search