Hello, I've got a DB design issue here, and I would like to hear your views on this problem. I've got a website where people can post comments on various ressources : news, articles, polls, and so on. So every comments are in a table with a field for the ressource type (news=1,polls=2, and so on) and a field for the ressource ID of this particular ressource. Comments are also threaded so : Create table comments ( id integer not null auto_increment, comment_parent integer not null, comment_thread not null, --used in solution 1 for efficiency ressource_type integer not null, ressource_id integer not null, body blob ); + index and key :)
The problem is the forum, where usually the traffic is higher. I've got two solutions : 1. a new topic on one forum goes in the same table 'comments'. Since the forum is displayed by thread (topic + replies), managing which comments has been seen by someone is done on a thread basis, whereas on all the website it's done on the couple (ressource type, ressource id) basis [the same for displaying comments]. So there is two logics for keeping track of comments viewed by people, for displaying comments and for moderation (removal comments, or topics). _but_ topics and replies are in the same table, which makes sense to me because they are similar. (same fields). 2. solution two : separate each topic from replies. a new topic is sent in a new table create table forum_post ( id integer not null auto_increment, body blob ) and then all the logic for comments seen, moderation, displaying is absolutely the same than the rest of the website... but I don't see why similar things would be in different tables. Also, searching the forum would be a little more work to merge comments and posts. It ay seems stupid, but having different way of displaying comments is beginning to be a hard work, and I believe with solution 2, queries will be faster (index would be more balanced : at the moment the forum number one has 20000 messages, but no other couples (ressource type, ressource id) has more than 100 comments. So what would you think about those solutions ? Has anyone other solutions for forum ? Thanks for your help. Pooly :-) ps: if you want to have a look : http://www.w-fenec.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
