Duplicate tags, FPP title attribute pony request

May 1, 2006 11:40 AM

Bug: You can attach the same tag multiple times to a post.
Pony: Title attribute on FPP link field?
posted by blue_beetle to Bugs at 11:40 AM (13 comments total)

bug: I guess you can if you add them later? if you add tags like this: "test foo test bar test" it should only register one "test" tag, but I guess on the comment page, you can add them one at a time, which I'll fix.

pony: no thanks, I don't want to introduce more work for users for a hidden, little-used feature in browsers. I like to keep my entry forms as simple as possible.
posted by mathowie (staff) at 12:02 PM on May 1, 2006

What is this, some sort of bug-and-pony show?
posted by Captain_Tenille at 1:12 PM on May 1, 2006

try the veal.
posted by boo_radley at 2:38 PM on May 1, 2006

Er, how are the database tables for tagging done? That you can have duplicates makes me think it's like some other table with [Tags: storyId, tagName] ... if it were changed to [TagToStory: (pk)storyId, (pk)tagId] [Tags: (pk)tagId, (unique)TagName] it'd be faster and duplicates couldn't happen.
posted by holloway at 3:00 PM on May 1, 2006

holloway, you're saying just change the tag sql query to only grab uniques then I don't even have to worry about dupes?
posted by mathowie (staff) at 3:39 PM on May 1, 2006

Well I'm just guess at the database structure, but if it's something like

story [storyId, userId, all details about the thread]
tags [storyId, tagName]

then it could be something like,

story [storyId, userId, all details about the thread] Primary Key: storyId
storyToTag [storyId, tagId] Primary Key: storyId, tagId.
tags [tagId, tagName] Primary Key: tagId.

So you put a many-to-many in the middle there, but as I said i'm just guessing it's the former because of the duplicates.
In this blog there's a Toxi Technique by someone who likes coining terms for basic SQL... he goes into the 3 table way and there's some pros and cons in the comments there.

On databases with decent optimisation paths (SQL Server, Postgres, Oracle... most things that aren't MySQL) the 3 table way scales way better than the others. This is with 10000 stories and 3~7 on tags per story on average, using stored procedures, and I've only tested this on SQL Server and Postgres, not Oracle, but I guess it's good too.
posted by holloway at 4:09 PM on May 1, 2006

I agree with holloway. Getting optimal queries in mysql might take quite a lot of effort for things like 'related tags', especially if you're using older versions of mysql. One thing I've found very helpful is the mysql EXPLAIN function, which lists everything the database is doing in a query, which helps you avoid very time-consuming things like table scans. The mysql manual has documentation about what you should be aiming for, and what to avoid.

A simple, short-term solution to the problem of selecting multiple tags is just doing SELECT UNIQUE rather than SELECT.
posted by matthewr at 4:29 PM on May 1, 2006

matthewr: i think he uses sql server. EXPLAIN is great advice for mysql :)

Oh and obviously once you've got that tags table you can unique the tagName column so the database doesn't let you store duplicates.
posted by holloway at 4:33 PM on May 1, 2006

meh, no need for a third table if the query is only pulling uniques.
posted by mathowie (staff) at 5:52 PM on May 1, 2006

Since I can't post twice in the same day, here's another bug:

BUG: It doesn't look like the quotation marks and hypen are being properly encoded in the MeFi RSS feed for this thread. All I see are ?'s.
posted by blue_beetle at 6:28 PM on May 1, 2006

mathowie: sure, if speed isn't an issue that's another way of doing it.
posted by holloway at 7:02 PM on May 1, 2006

If uniqueness is enforced how will I ever tag something "swedish chef bork bork bork" ??
posted by phearlez at 2:19 PM on May 2, 2006

Well that's another problem entirely of words vs phrases.
posted by holloway at 2:40 PM on May 2, 2006