CODEWITHSUNDEEP
phpsqltagging
CodeOverload
CodeOverload

Reputation: 48485

What's the sql scheme for supporting Tags system?

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

Upvotes: 0

Views: 633

Answers (2)

chelmertz
chelmertz

Reputation: 20601

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

Upvotes: 1

user187291
user187291

Reputation: 53940

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.

Upvotes: 4

Related Questions