Looking for a starting point for a tagging system.

Question

Basically I want to setup a tagging system like stack overflow has for entries and trying to plan out how a relevance based search would work. I want to have an option to pull up similar tagged entries for a related entries section. Right now I am using two tables for tags, a table for each unique tag and a join table. I am trying to think if that will work for being able to generate a list of entries that share similar tags.

If anyone has any ideas, or links to articles I could read on it to get my brain heading in the right direction that would be amazing. Thank you!

Answer 1

add one more field to entities table: tags. with string of comma separated tags, to prevent 2 more joins for selecting entities list.

Answer 2

Perhaps you could have a separate table to store related entries.

EntryId RelatedEntryId

Then you could have a CRON job recompute the relationships periodically and update the table. It would be less expensive than trying to compute these relationships on the fly.

Answer 3

You'll need to keep track of how often one tag is linked to another. Like, say "php" and "mysql" share 50 articles (or whatever the main content being tagged is), while "php" and "sql-server" might have 3, and "php" and "apache" have 25. So given "php," you'd want to return "mysql" and "apache" in that order (possibly letting "sql-server" fall to the wayside).

No way is this ideal, just thinking out loud (and kind of expanding on stephenc's answer, now that I see it):

CREATE TABLE tag_relations (
tag_id int unsigned not null,
related_tag_id int unsigned not null,
relation_count smallint unsigned not null,
PRIMARY KEY (tag_id, related_tag_id),
KEY relation_count (relation_count)
);

Then for each unique tag tied to an article, loop through all other tags and INSERT / UPDATE, incrementing the relation_count by 1. That means ("php", "mysql") and ("mysql", "php") are two completely different relations to be maintained, but without digging through search concepts I've probably forgotten, it'll still function. If something has 10+ tags, updates will be very slow (maybe pass that to cron like stephenc suggested), but it'll be easier to search this way. Nice and straightforward like so:

SELECT related_tag_id, COUNT(relation_count) AS total_relations
FROM tag_relations
WHERE tag_id IN ([list,of,tag,IDs,to,compare])
// AND tag_id NOT IN ([list,of,tag,IDs,to,compare]) -- probably
GROUP BY related_tag_id
ORDER BY total_relations DESC

Easier than having to check against both tag_id & related_tag_id and sum them up through a mess of subqueries, at least. JOIN on your tags table to get the actual tagnames & you're set.

So if you're looking up "php" and "mysql," and "apache" often relates to both, it'll be near the top since it's counting & weighting each common relation. It won't strictly limit it to common links though, so add HAVING total_relations >= x (x being an arbitrary cutoff) and/or just a regular LIMIT x to keep things relevant.

(note: research the heck out of this before thinking this is even slightly useful - I'm sure there's some known algorithm out there that's 100x smarter and I'm just not remembering it.)

PHPro.org has a good writeup too, using a similar idea.