On Sun, Oct 19, 2008 at 10:34 AM, Martin Zvarík <mzvarik@xxxxxxxxx> wrote:> Ryan S napsal(a):>>>> Hey,>>>> this the first time I am actually working with "tags" but it seems quite>> popular and am adding it on a clients requests.>>>> By tags I mean something like wordpress' implementation of it, for example>> when an author writes an article on babies the tags might be>> baby,babies, new borns, cribs, nappies>>>> or a picture of a baby can have the tags>> baby,babies, new born, cute kid, nappies>>>> the tags are comma separated above of course.>>>> The way i am doing it right now is i have sayyyy an article or a pic saved>> in the db as article_or_pic_address text>> the_tags varchar(240)>>>> My question is, when someone clicks on any one of the tags, do i do a>> LIKE %search_term% search or...???>>[snip]>>>> Any help in the form of advise, code or links would be appreciated.>>>> TIA.>>>> Cheers!>> Ryan>> The main point here is WHAT SHOULD BE THE BEST DB STRUCTURE.>[snip]>> To the TOPIC: I think normalization would be a killer.> Imagine joining 3 tables (I really don't see more functionality here) OR> just selecting from 1.> Selecting from 3 properly indexed and joined tables should generallybe better than using LIKE '%tag%' from one table if you've got morethan a little data in your table. The latter cannot use an indexbecause of the opening wildcard, so once your table gets big enoughthat queries using indexes begin to out perform table scans, approachwill lose. Yes, there is overhead involved in joining tables, butRDBMS are built for managing joins between sets of related data. Ifyou give that up, you might as well skip the overhead of the databaseentirely and use a flat file. Setting raw performance aside, joins also offer other benefits. If youallow people to search on multiple tags, a joined query can tell youhow many tags each returned item matched in the original query. Thejoined approach also allows your tags to intelligently differentiatebetween 'men' and 'women'. :-) Andrew
