Yes, but if each user had a csv of key words, you woiuld still get the word fish duplicated in the csv for each user who had that as a keyword.
No difference, just better/faster indexing per David's description BTW This is *exactly* what the R for Relational in RDBMS's means! Use it to your advantage. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Chris Ham > Sent: Thursday, March 07, 2002 11:46 AM > 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. > > 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)
<<attachment: winmail.dat>>
