Another way of looking at it is: keeping similar items in little boxed fields is what database programs have been optimized to do. So use the specialization built into Databases.. they're built to hold all your keywords.
does that help? carina ----- Original Message ----- From: David Nesbitt <[EMAIL PROTECTED]> Sent: Thursday, March 07, 2002 12:09:29 PM To: <[EMAIL PROTECTED]> Subject: Re: [CFTALKTor] search problem > > David, thanks for your help. > > One question about your suggestion: > > > > In the tblUser_Keywords table, this means that I could have many records > > for the same keyword. For example, if 100 users have 'fish' as a > > keyword, there will be 100 records for 'fish'. The same applies for the > > tblProducts_keywords table. > > > > This seems inefficient to me. Am I wrong? > > Thanks for all the help. > > Nope, not that inefficient. Space is cheap in a database. If you have 100 > users with 100 keywords each in a comma delimited list stuffed in its own > field or you have 100 users with 100 keywords with each keyword in its own > record you still have the same number of keywords :-) BUT with the > normalized view you don't have to do any fancy CF hacking to manage them. It > the user wants to remove "fish" from his/her keyword list you do one delete > statement ( > > Delete > from tblUserKeywords > where usernumber =#VAL(usernumber)# > AND UCase(Keyword) = '#ToUpper(attributes.Keyword)#' > > Instead of an entire page of CF (or maybe a function) to hunt down the > keyword and remove it from the list then re-enter it back into database. > That is the big bonus. That and the fact that it is quick to search on > because it is indexed. > > I'm not knocking the big comma list you could do something like > > SELECT PRODUCT_ID > FROM PRODUCTS > WHERE PRODUCT_KEYWORD IN ( > > SELECT COMMA_DELEM_KEYWORDS > FROM TBLUSERS > WHERE USERNUMBER = #VAL(cookie.myusernumber)# > ) > > That would return you the same results as the previous query I suggested > (below). Its just not as efficient for you to manage. It's slower to index > (if you can index it- if you are using MS Access it wont let you index Memo > fields which you would probably have to use if your key list exceeds 255 > characters). > > You can do it either way but their are tons of books written on normalizing > tables the way I suggested. It really makes your job easier later on if > something has to be changed. If you break your data down into the smallest > possible chunks then join it all together with a view or in a query it > gives you a lot of flexibility. Especially if you have hammered out the > database design really well. > > My sugar high is running low so I'm getting some lunch. Does this answer > your question? I'm sure someone more articulate than me could have put it > more succinctly. > > > : David Nesbitt > : Web Developer (ColdFusion) > : Tundra Semiconductor > : Ottawa, ON Canada > : [EMAIL PROTECTED] > > > ----- Original Message ----- > > > I am wondering if I should have one large field in the db that > > contains > > > a comma separated list of their keywords or if I should set one field > > > for each keyword (up to a maximum of say 10 keywords). > > > > Best bet in my opinion is to break it into another table (this is called > > normalizing), or as you put it one field for each keyword. This is the > > most > > flexible way, especially if you enter key words for new products in a > > similar manner > > > > To illustrate: > > > > tblUser_Keywords > > keywords | usernumber > > --------------------- > > fish | 1 > > fusion | 1 > > book | 2 > > etc... > > > > tblProduct_Keywords > > keywords | Product_ID > > --------------------- > > Soap | 101 > > fusion | 101 > > Lock | 305 > > etc... > > > > This way, when you want to search for products with matching keywords > > you > > could do a quick query like this: > > > > SELECT DISTINCT (PRODUCT_ID) > > FROM TBLPRODUCT_KEYWORDS > > WHERE KEYWORDS IN ( > > > > SELECT KEYWORDS > > FROM TBLUSER_KEYWORDS > > WHERE USERNUMBER =#COOKIE.MYUSERNUMBER# > > ) > > > > Which would return a set off all the PRODUCT_ID's that match the users > > keywords.You can get even more complex with something like > > > > SELECT PRODUCT_ID, PRODUCT_DESC, PRODUCT_NAME, etc... > > FROM TBLPRODUCTS > > WHERE PRODUCT_ID IN ( > > > > SELECT DISTINCT (PRODUCT_ID) > > FROM TBLPRODUCT_KEYWORDS > > WHERE PRODUCT_KEY_WORD IN ( > > > > SELECT KEYWORDS > > FROM TBLUSER_KEYWORDS > > WHERE USERNUMBER = #VAL(COOKIE.MYUSERNUMBER)# > > ) > > ) > > > > Which would return all the product information for the products that > > matched > > the keywords the user was interested in :-) Another benefit of this way > > is > > you can arbitrarily set a limit of 10 keywords in your CF code, but if > > you > > want to change it to say, 20 you don't have to worry about modifying the > > anything in the database (like the field length of the keywords field). > > > > > Also, when the admin of the site adds a new product to the db, they > > can > > > enter keywords for that product. Then I would like to send an email to > > > all users where any of the products keywords match any of the users > > > keywords. > > > > Simple, > > > > SELECT USERNAME, USER_EMAIL > > FROM TBLUSERS > > WHERE USERNUMBER IN ( > > > > SELECT DISTINCT( USERNUMBER ) > > FROM TBLUSER_KEYWORDS > > WHERE KEYWORDS IN ( > > > > SELECT PK.KEYWORD > > FROM TBLPRODUCT_KEYWORDS AS PK, > > TBLPRODUCTS AS P > > WHERE P.PRODUCT_ID = PK.PRODUCT_ID > > AND P.PRODUCT_NEW = YES > > /* (or what ever SQL you use to designate a new product, > > maybe a > > date?)*/ > > ) > > ) > > > > > I don't want to bog down the db. > > > > That's what the Database is their for :-) Better to bog down the highly > > search optimized database than do the searching in CF. You are of > > course > > indexing both columns in the relational tblFOO_Keywords tables which > > makes > > for lightning query results. > > > > Is this the kind of thing you were looking for? I'm totally nuts for > > nested > > queries these days, so all my solutions > > involve them some how. I'm also on a bit of a sugar high this morning > > so > > please excuse my rambling. > > Anyway, my two cents :-) Hope this helps. > > > > : David Nesbitt > > : Web Developer (ColdFusion) > > : Tundra Semiconductor > > : Ottawa, ON Canada > > : [EMAIL PROTECTED] > > > > > > - > > You are subscribed to the CFUGToronto CFTALK ListSRV. > > This message has been posted by: "David Nesbitt" <[EMAIL PROTECTED]> > > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > > Manager: Kevin Towes ([EMAIL PROTECTED]) > > http://www.CFUGToronto.org/ > > This System has been donated by Infopreneur, Inc. > > (http://www.infopreneur.net) > > - > > You are subscribed to the CFUGToronto CFTALK ListSRV. > > This message has been posted by: "Chris Ham" <[EMAIL PROTECTED]> > > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > > Manager: Kevin Towes ([EMAIL PROTECTED]) > http://www.CFUGToronto.org/ > > This System has been donated by Infopreneur, Inc. > > (http://www.infopreneur.net) > > > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: "David Nesbitt" <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: [EMAIL PROTECTED] To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
