Hi folks,
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:
I'm not entirely sure if this is what SO uses. But there is a good discussion here.
Brian R. Bondy
2009-03-12 23:33:00
A:
"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.
S.Lott
2009-03-12 23:38:40
A:
It would be a good idea to loook at how wordpress handles tags for posts and it will give you some idea.
Shoban
2009-03-13 04:47:21
wordpress do a many to many with three tables i believe.
Pure.Krome
2009-03-13 05:17:56
A:
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.
Damovisa
2009-03-13 06:58:18
unlikely this will be more performant. it introduces horrendously complex queries when you are looking for multiple tags.
Sam Saffron
2009-03-13 11:51:49
what if the tags were stored in one delimited varchar field and you could use a "like '%tag%'" query against that field, probably still not very indexable
benPearce
2009-03-16 07:31:23
@sambo99 - true in that case and good point. Finding questions for a particular tag would suck.
Damovisa
2009-03-17 00:17:40
+3
A:
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.
Sam Saffron
2009-03-13 11:38:36