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


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?


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.


"I'm assuming it's a many<->many structure, with three tables. Anyone have any ideas?"

More to the point, there aren't any serious alternatives, are there? Two relational tables in a many-to-many relationship require at least an association table to carry all the combination of foreign keys.

Does SO do this? Who knows. Their data model includes reference counts, and -- for all any knows -- date time stamps and original creator and a lot of other junk about the tag.

Minimally, there have to be three tables.

What they do on SO is hard to know.


I'm not entirely sure if this is what SO uses. But there is a good discussion here.


It would be a good idea to loook at how wordpress handles tags for posts and it will give you some idea.


The other possibility of course is that there are only two tables.

Given there are at most 5 tags, a Question table with five nullable foreign-key references to a Tag table is a possiblity.

Not very normalized, but it could be more performant.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »