tri_inn

tri_inn

  • NA
  • 1.2k
  • 235.4k

Sample forum database design guide line

Nov 29 2015 6:21 AM


i want to design small forum where user post their question as per category wise.

1) first user click on category link

suppose categories will be like C#, MVC, WebForm, LINQ, Jquery etc

after clicking on category link a page will come where specific category wise questions will be displaying. there will a button for post a question to that category.

suppose user X post a questions and other people will post answer for that post and when user click on my thread link then user will see list of his asked questions but those will be at top whose answer has been given recently.

if user X post any answer then that thread will be shown in user X's question list.

i have bit unclear idea to table design for this kind of forum.

1) i will have category master table

2) PostTable where questions will be stored.

do i need to create a separate table for answer or store the answer in post table too and create related between question and answer with ID and ParentID concept?

suppose i am thinking post table design below like

CREATE TABLE dbo.Posts
(
PostID INT IDENTITY(1,1) PRIMARY KEY,
UserID INT NOT NULL FOREIGN KEY REFERENCES dbo.Users(UserID),
ParentID INT NULL REFERENCES dbo.Posts(PostID),
ReplyToID INT NULL REFERENCES dbo.Posts(PostID),
Content NVARCHAR(MAX), -- more accurate than "description"

-- ... other columns ...

-- "DateTime" is not a good choice for a column name
create_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
modify_date DATETIME NULL -- or same default as create_date
);

anyone can help me to design tables for small forum. is there any article exist which discuss the same thing with table diagram.

please see all my point like how to show question and their answer and accordingly help me to design table for the forum.
 

main confusion start here like to how to design this below part

suppose user X post a questions and other people will post answer for that post and when user click on my thread link then user will see list of his asked questions but those will be at top whose answer has been given recently.

if user X post any answer then that thread will be shown in user X's question list.

i have bit unclear idea to table design for this kind of forum.

1) i will have category master table

2) PostTable where questions will be stored.

3) how to design answer table?

please come with table design. thanks