Database Design for Online Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Database Design for Online Forums

Author

Topic

descentflower
Starting Member

3 Posts

Posted - 2009-09-29 : 05:40:57

Hi,
I need to built an online forums in asp.net and Sql Server 2005.
Can anyone provide me tips where to start from? Any tutorial or links for the database design ? I am a newbie so would need your help.

Thanks.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-09-29 : 14:15:51

Be Consistent in your naming is one suggestion.

Inconsistent:
     Topics, Post

Better:
     Topics, Posts

Inconsistent:
     Users.User_ID, Posts.UserID

Better:
     Users.User_ID, Posts.User_ID

Try not to implement redundant data (ie. normalize it)

Redundant:
     Posts.Forum_Name, Posts.Forum_Description

Better:
     Posts.Forum_ID, Forums.Forum_ID, Forums.Forum_Name, Forums.Description

Provide a Logging Table so when problems happen, you can investigate

Log_Errors.User_ID, Log_Errors.Error_Time, Log_Errors.Error_Message

descentflower
Starting Member

3 Posts

Posted - 2009-09-30 : 00:20:22

Thanks denis,
So you mean I should use different tables for Topics and Posts?
Can you please elaborate more on the table structure for the same ?

Thanks.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-09-30 : 12:29:22

quote:
Originally posted by descentflower

Thanks denis,
So you mean I should use different tables for Topics and Posts?

Definitely. And the Topic record will contain the ID of the corresponding Post record.

A few ideas:

Forums: Forum_ID, Forum_Name, Description
Topics: Topic_ID, Forum_ID, Title, User_ID
Posts: Post_ID, Topic_ID, User_ID, Post_Date, Post_Text
Users: User_ID, First_Name, Last_Name, User_Name, User_Country

descentflower
Starting Member

3 Posts

Posted - 2009-10-01 : 00:02:46

Hi denis,
i understand the Topic table will have the "Topic heading" and Posts table will have the "Opening Post" as well as the "Subsequent Posts". But what will the Forum tbale have?

Thanks.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-10-01 : 09:48:21

quote:
Originally posted by descentflower

Hi denis,
i understand the Topic table will have the "Topic heading" and Posts table will have the "Opening Post" as well as the "Subsequent Posts". But what will the Forum tbale have?

Thanks.

Forum table was for multiple Forums - ie.: Transact-SQL 2005, Transact-SQL 2008, High Availability, Other Sql Server Topics...

I basically copied the lingo from this website.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-10-01 : 10:37:49

There are many software packages available for free that have forums and use databases. You could probably borrow a database design from them to use as a starting point, and modify it for your purposes.

For example:
http://info.tikiwiki.org/tiki-index.php

No point in re-inventing things that have already been done. Small minds copy ideas; great minds steal them outright.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources