Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?
share|improve this question
2  
Do you have any examples of basic table layout you've already considered? Can't help but think this sounds like homework. –  Aaron Bertrand Aug 9 '11 at 21:42
    
we don't have a sample of a forum design just sitting around, it's not a case of a design that one does all day. Do you have any design started already? In SQL Server you can use the integrated diagrams, so you can attach here a picture with your current try. –  Marian Aug 9 '11 at 23:00

2 Answers 2

up vote 6 down vote accepted

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
);
share|improve this answer
    
Any reason why you didn't/wouldn't use a DATETIME2(N)? –  Erik Aug 21 at 17:50
    
@Erik Largely because the granularity provided is probably not necessary for a forum application (and the question wasn't about perfecting the design of the rest of the table - that wasn't the problem I was addressing at all). This was also four years ago, when most people were still on 2005 (I try not to make answers dependent on OP's version unless it is precisely about that problem, so that the largest number of readers can benefit from the answer). If it were my system, I would have used SYSUTCDATETIME(), too, instead of CURRENT_TIMESTAMP. shrug –  Aaron Bertrand Aug 21 at 17:54

If you just add the field ParentPostID (nullable) into table Post, you will get the topics: if ParentPostID is NULL, this is the starting post of the topic, else this is the answer for some post in the topic. but I think you can not get here the entire solution.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.