> 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)
