The joys of tags
Well, inspired by WB and with little else to do, I decided to look into tags and how best to implement them on my blog. Boy, was that a mistake.
There’s three basic schemas that one can use; non–normalized (the MySQLicious solution) which uses fulltext, the sort–of–normalized and the fully (3NF) normalized. There are problems with each, but in the end I opted for the latter choice. The only major obstacle to overcome was efficiently displaying a list of tags for each entry. Clearly I didn’t want to have to run a query for each individual entry since that would be hidously inefficient.
So after much googling and beating my head against a wall, I came up with an idea. MySQL’s GROUP_CONCAT function is extremely useful, so I used a subquery to get all of the tags for that news entry and GROUP_CONCAT them all together. It’s not pretty, but it works and it seems to be okay–ish. I know subqueries aren’t particularly efficient, but this seems to be the best way that I can think of without doing something completely radical (for instance, dumping the RDBMS system altogether).
Anyway, that’s kept me up until now (3:17am). The rest of the stuff is coming together nicely. Getting load times of between 0.1 and 0.01 seconds on my lowly laptop, so I’m hopeful that this will be a lot more efficient than my last incarnation. Once I’ve got the basic foundations laid, I’ll be setting up the user system and hoping things work nicely. Themes are also getting there, but there’s a lot more to do.
If only I could put this much effort into doing something useful – like revision. Oh well.