Tagging acts more like categories, you can find all rows with as many of those categories as possible. That is one option, the SQL is not hard, but making those that match all 3 tags rank higher than those with 2 and so on gets a bit trickier.
Another option is to use full text searching, perhaps Verity (included with CF), or something like SQL Server's full text search engine, both of which allow for proximity searching and can assign weights to results based on how likely a match each row is. --- Ben -----Original Message----- From: Jacob Munson [mailto:[EMAIL PROTECTED] Sent: Thursday, January 04, 2007 12:55 PM To: CF-Talk Subject: Re: Using a tag system like del.icio.us(sort of) Maybe I'm oversimplifying, but what about this: select top 10 article from SearchTag where tagname = 'Bush' or tagname = 'iraq' or tagname = 'stupidity' This will grab ten articles that match any one of the 3 tags. I know I'm not doing all the joins you'd need, but you get the idea. On 1/4/07, Jack Morgan <[EMAIL PROTECTED]> wrote: > > Hi there everyone, this is my first post on this mailing list. > > I've got a project that is in planning and one of the features that > I'd like to offer the client is stumping me a little bit. On this site > they've got a whole bunch of text articles; books, thesis papers, news > stories, etc. > They > are in general categories, but I need a better way to relate similar > articles. So what I was thinking was that I could use a tag system, > similar to del.icio.us or youtube. Users can add tags to an article, > the tags will be stored in a database like so. > > -------------------- > Article - table > -------------------- > ArticleID - PK int > > ------------------- > SearchTag - table > ------------------- > SearchTagID - PK > TagName - varchar > > ------------------ > ArticleSearchTag - table > ------------------ > ArticleSearchTagID - PK > SearchTagID - FK many to many > ArticleID - FK many to many > > I was thinking that users can select a tag from the tags list or add a > new one if they like. Then the relationships are stored and I can just > count the number of times that tag has been related to an article to > get the tags relevance to the article. This would allow me to rank > searches better. I'm not sure if this is the best system, so please > feel free to call me on that > :) > Where I'm stuck right now is another, related, feature. When a user is > viewing an article, I'd like to show them a small list of articles > with similar tags. Each article can have multiple tags associated > with it, so I'm not sure how I could get a list of about 10 articles > with similar tags. > For example, an article has the tags 'Bush' 'Iraq' 'stupidity'. I'd > like to get articles that contain the highest amount of all 3 of those > tags instead of just articles related to Bush. > > I'm really not sure how to do that at all. I would think it's a rather > complex stored proc, but I may just be going down the wrong road. What > do you all think is the best approach? > > -Morgan > > _________________________________________________________________ > Get FREE Web site and company branded e-mail from Microsoft Office > Live http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265653 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
