March 6, 2011 at 11:18 am
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!
March 6, 2011 at 11:27 am
Please reply here
http://www.sqlservercentral.com/Forums/Topic1073727-1292-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply