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

问题:

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?

回答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.

回答2:

"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.

回答3:

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

回答4:

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

回答5:

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.