Sql-server – SQL Server database forum design sample

database-designsql serversql-server-2008-r2

I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find any free samples to compare my work to it.

As some people requested in the comments, this is what I did so far: For a Forum, I considered a User (Table), each user have a post (Table) and each post can start a topic (Table).

Table User : UserID (PK), Nickname, Email, Password, AvatarImage.

Table post : PostID (PK), UserID (FK), DateTime, Description.

The question that I don't have an answer for:-

  1. How can I provide the possibility that a post can start a topic? Should I make a new table? what columns? or something can be done with the post table with the help maybe of stored procedures?
  2. How can I make a topic after a topic (like answering a question)?

Best Answer

You might have something like this. If the post is a new topic, ParentID and ReplyToID would be NULL. For forums it is often desirable to show when a post is a reply to another post, but still indicate which topic it belongs to, so a second column (ReplyToID) can indicate this. When it is a reply to the original parent this can be NULL or have the same value as ParentID.

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
);
Related Question