How would you implement a 'tagging' feature?

[lschmidt]

Let's assume you have a site that lets users keep a blog. The basic idea is that they create posts. Each post is allowed to have certain tags that they specify (optional), and the idea is that they can search for posts they've made, by selecting one of their tags, and then showing all posts with that tag.

I'm thinking the best way to do this would be to create a separate table called 'tags'. Each record would contain three fields: userid, tagname, postid. The user could then search for a tag name, and return the posts, and when viewing a post it could show all the tags for that post.

I can't really think of any other way to do it.

Any other ideas?

[busy]

table 'tags' will have cols 'id' and 'tag'
table 'posts' will have cols 'id', 'post', and 'user_id'
table 'users' will have cols 'id' and 'user'

table 'post_tag' will have cols 'p_id' and 't_id'

to get all the posts from 1 user that contain a certain tag you would query:

SELECT 'post' 
FROM users 
LEFT JOIN posts ON (users.id=posts.user_id)
LEFT JOIN post_tag ON (posts.id=p_id)
LEFT JOIN tags ON (t_id=tags.id)
WHERE users.id='$user'

This way you will only have to have one row per tag, you won't have a bunch of redundant data.

[lschmidt]

I never knew about mysql's JOIN syntax. I've been doing the "redundant" method for a long time now. How exactly does the JOIN work?

PS: If I do it how I described, for the purpose of keeping my project consistent in the methods I use, it should only affect the performance of INSERTS, correct? Basically it is storing a little more data, and when I SELECT I just use multiple argument for WHERE instead of using JOIN. Right?

[busy]

SELECT * FROM tablea LEFT JOIN tableb ON (tablea.col1=tableb.col2)

is the same thing as

SELECT * FROM tablea, tableb WHERE tablea.col1=tableb.col2

except that if you use LEFT JOIN then you still get everything from tablea even if there's no match in tableb. (the result for tableb columns will be null)
So for what you're doing, you could still query posts that didn't have any tags.

Very useful, I can't remember the last time I used the second method in a query since I learned LEFT JOINs.

But yes, if you structured your queries the way you're used to it would make no difference, you'd be doing the same thing.

The redundancy comes into play not from the way you do the query, but from your database structure. It's not normalized
http://en.wikipedia.org/wiki/Databas...Starting_Point

lets say you have 1000 posts that all include the tag 'music'. As you were first thinking, you would have to store the word 'music' 1000 times in the column tag. It's not so much that you're wasting space, (although you are), you're also making more work for yourself and the computer when you want to work with that database. Doing it this way also makes it easier for you to inadvertently start collecting database anomalies and it makes them harder to spot.