login
tags (2) posted: Tue 2011-08-09 01:52:17 tags: phosphoros
Something I didn't think about until I started adding in a "list all entries tagged with X" view... in the absence of a join table mediating between entries and tags, the SQL to retrieve a subset of entries with a specific tag is:
SELECT * FROM entries WHERE tags LIKE '%my tag%'

Having to resort to a 'LIKE' comparison usually means really sloppy, and often unsafe, design. In this instance, it means that clicking through a link connected to the tag "cat" will also catch all entries tagged "catheter", "backscatter", "copycat" etc. There's no sane way to make the MySQL engine pre-process the tags column to tighten up that comparison operation. (There are insane ways; but if there must be insanity, I'd prefer to keep it in the PHP, not offload it to the database engine.)

And I think I was onto something there, because IF you're not implementing a tag-stats or tag-cloud sort of widget, AND you're going to loop through the whole table parsing the tags column every time you select by tag, THEN there's not much point in having a distinct tags table anyway. If we're going to not-have a table, it makes more sense to have an entry-tags table (because it sidesteps the looping-and-parsing boondoggle) and not-have the tags table (because tags is the most expendable table).