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. Chris -----Original Message----- From: David Nesbitt [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 07, 2002 10:31 AM To: [EMAIL PROTECTED] Subject: Re: [CFTALKTor] search problem > 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)
