A database schema for Tags (eg. each Post has some optional tags)

Question

I have a site like SO, Wordpress, etc, where you make a post and u can have (optional) tags against it.

What is a common database schema to handle this? I'm assuming it's a many<->many structure, with three tables.

Anyone have any ideas?

Answer 1

A three table many to many structure should be fine.

Eg. Posts, PostsToTags(post_id,tag_id), Tags

The key is indexing. Make sure you PostsToTags table is indexed both ways (post_id,tag_id and tag_id,post_id) also if read performance is ultra critical you could introduce an indexed view (which could give you post_name, tag_name)

You will of course need indexes on Posts and Tags as well.