What's the sql scheme for supporting Tags system?

Question

Hi,

i've coded a tiny forum software in php where people posts threads, and i want to support tags.

How many SQL tables should i make for this ? and briefly how the tags system will work?

Thanks

Answer 1

Basically you need tags(id, tag) table and tags-to-posts M:M relation table (tag_id,post_id). To select posts for a given tag

 select posts.* from posts, tags, post_tags
    where post_tags.post_id = posts.id
    and post_tags.tag_id = tags.id
    and tags.tag = "whatever_tag" 

In response to the comment: a single table(tag,post) approach may appear "simpler", but it's not exactly scalable. What if you decide to add some extra info to your tag - like creation date or who created it. Or let users have "favorite tags", like here at SO - without a separate tags table this will be tricky.

In general, it's better to keep the database normalized, even if it seems "complicated" at the beginning.

Answer 2

There are some good reasoning and performance testing available in the article "Tagsystems: performance tests"