Best storage model for forum application?

Question

Trying to build application which will be a forum, with topics and every topic may have many discussion posts to it, and every discussion post may have reply to it. So it'll be like:

[Topic: Let's talk about cell phones]
\
 \
  \ [User A: I have nice Motorola]
  | [User B: I have good Blackberry]
     \ [Reply to User B from User C: I prefer iPhone to Blackberry!]
     \ [Reply to user B from User X: I like Blackberry, too!]
  | [User D: Something else]

Something like that.

The problem is that I need to be prepare for large volumes of comments. Also, need performance is important, and I need to be able to check all replies to discussion post, as well as all comments and comments "around it" by date/time within the topic.

Should I use SQL? Or some key-value storage? Who had experience with them? Any ideas on structure to build?

Thanks.

Answer 1

The tried and true approach is a relational database. Many of these systems have been built already on relational databases and many of them perform wonderfully even with huge amounts of data. If you want to ask about anything more specific then please do so, because this is a very general question that is hard to help with.

Answer 2

You really should look into using an existing open-source CMS package (e.g. Joomla) instead. Also, you should look into the (not-released-yet) Google Wave.

Now to answer your question, SQL (which uses the relational database model) would probably be best for this project. Your schema might look like this (I know mostly PostgreSQL, so this might not be 100% standard SQL):

CREATE TABLE topics (
   id INT PRIMARY KEY,
   title TEXT
);

CREATE TABLE posts (
   id INT PRIMARY KEY,
   parent INT REFERENCES(posts),
   topic INT REFERENCES(topics),
   subject TEXT,
   body TEXT
);

By the way, when you mentioned key-value storage, there is a name for that too, and that is EAV. However, depending on who you ask, EAV is sometimes considered "poor design". From the looks of it, I don't think it would be a good fit for the application you are proposing.

Answer 3

I second Joey. You should look into using a pre-built solution. Vanilla also looks like a good option.

If you really want to build it yourself, I would go with mysql. It isn't simple to explain how to design a database. At a high level you'll need tables to represent each part of the app:

-User

• id(primary key)
• name(unique)
• password
• privilege <-- So that you can differentiate between admins/users

-Post

• id(primary key)
• subject
• body
• topic_id <-- So that you can search for all the posts about a particular topic
• timestamp <-- So that you can sort all the posts for a particular topic in order
• user_id <-- The id of the user making the post
• reply_id <-- The id of the post that this post is replying to, if any

-Topic

• id(primary key)
• name