Table design for a forum

Question

To be clear, I'm not looking for someone to design an entire forum here, rather, I need some help determining how to relate a couple of tables, and whether or not these tables need to exist in the first place.

Basics of a forum

Topics

topic_id // Unique topic ID, AI, PK
topic_name // name of the topic
... etc

• Topics
• Posts in these topics (which is what this is here, a post
• Replies to posts, which is what the responses below are (your responses that is)

Users

user_id // Unique ID of the user, PK, AI
user_name
user_pass
user_email
user_date

Posts

post_id // unique ID of post - PK, AI
post_content
... etc

I also want to include the ability to like / dislike a post, and for that reason I created a table post_ranking which looks as follows

id // Auto increment ID    
post_id  // Foreign key, refers to post.post_id
user_id  // Foreign key, refers to user.user_id
vote_up  // Whether or not the post was voted up (0 for down, 1 for up)
rank_date // date the ranking occured

The problem I've just run into is that I cannot figure out how to incorporate the replies into this table. The only option I've thought of so far would be to create a second table called reply_ranking - but wouldn't this be somewhat unorganized?

So, my question:

Should I create two separate tables for rankings (post_ranking , reply_ranking) or is there another way that I should be arranging the tables above which would avoid this problem in the first place?

Answer 1

Your post is not very clear, I can't understand if in the table Topics you have inside also posts in that topic and replies to that topic.

In this case I think that this is not the good way, you should have posts separated from topic data. Let me explain.

A forum is formed by:

//                MAIN PAGE, showing avaible forums
//                    |
//              SPECIFIC FORUM, that show the threads (or topics) inside
//                    |
//                  POSTS that show part of the post related to that thread.
//
//       USER_CP -------------- ADMIN_CP

We need at least 4 tables (I'm not considering admin_cp). these are:

//  TOPICS:
//  +---------------+---------+-------------+----------+----------+----------------+
//  | ID, p.k. a.i. |  TITLE  |  SUB_TITLE  |  AUTHOR  |  CLOSED  |  PARENT FORUM  |
//  +---------------+---------+-------------+----------+----------+----------------+
//
//  These are very few basic field for the table. No reference about the posts here.
//
//
//  FORUMS:
//  +---------------+---------+-------------+--------------+
//  | ID, p.k. a.i. |  TITLE  |  SUB_TITLE  |  VISIBILITY  |
//  +---------------+---------+-------------+--------------+
//
//  Almost clear, visibility is a value to determine if the forum is private (such are
//  forums like: "Moderator rooms" or "Admin stuff"), protected (i.e. for non registered
//  users, or if the forum is public.
//
//
//  POSTS:
//  +---------------+----------+-------+-----------+--------+---------+-------+- - -
//  | ID, p.k. a.i. | TOPIC_ID | TITLE | SUB_TITLE | AUTHOR | MESSAGE | VOTES | ...
//  +---------------+----------+-------+-----------+--------+---------+-------+- - - 
//  
//  In Votes you have a integer number (positive o negative, no matter) with the total
//  of the votes for this post (not useful if you fully use the table like).
//
//  REPLIES
//  +---------------+----------+---------+--------+---------+
//  | ID, p.k. a.i. | TOPIC_ID | POST_ID | AUTHOR | MESSAGE |
//  +---------------+----------+---------+--------+---------+
//
//
//  USERS
//  +---------------+------+------+------------+------+---------+- - - - -
//  | ID, p.k. a.i. | NICK | PASS | PRIVILEGES | NAME | SURNAME | ETC..
//  +---------------+------+------+------------+------+---------+- - - - -
//
//  Privileges determinate if a user is admin, mod, super_mod or simple user
//
//
//  LIKES
//  +---------------+---------+---------+----------+----------+---------+------+
//  | ID, p.k. a.i. | USER_ID | POST_ID | TOPIC_ID | REPLY_ID | UP_DOWN | DATE |
//  +---------------+---------+---------+----------+----------+---------+------+

This is, in my opinion the structure you should use.

home page, show all the forums:

• read the privileges of the user
• read the forums data from the table WHERE visibility >= user_privileges
• impaginate each forum with proper links.

Forum page, show all the topics (and moderators):

• read the topics from the table WHERE parent_forum = forum_id
• read moderators of this forum
• impaginate topics with proper links
• shows moderator at the bottom of the page.

Topic page, show all the messages.

• read the topi data from the table.
• the topic is also the first message!
• read the posts from the table WHERE topic_id = current_topic_id
• for each post read from the table "LIKES" WHERE post_id = selected_post
• sum all the votes
• impaginate the post in proper pages and proper votes.

User page:

• read all the data, WHERE id = user_id_that_want
• read from "LIKE" WHERE user_id = actual_user
• you have all the likes/dislike the user sent. do what you want.
• impaginate

With this table structure you can also use a StackOverflow similar way to consider votes, i.e. UPvotes are 0 for the voter, but downvote are -1 for the voter. For each post, you can read from the like table just using post_id, so you know how many votes have the post (up and down) and with this you can count also up vote +10 to the post creator, downvote -5 to post creator.

Only with an extra table that is likes.

You can have:

• votes on a topic
• votes on a post
• votes on a reply
• votes total (reply+post+topic) of the topic.

When you insert a topic you will add:

user_id
topic_id = topic id
post_id = -1 (this is not a post, is the topic!)
repl

y_id = -1 (as above)

when you insert a post:

user_id
topic_id = current topic
post_id = post_id
reply_id = -1 (this is not a a reply)

when you insert a reply

user_id
topic_id = current topic
post_id = current post
reply_id = the reply id.

This will not have id problem issues. because the three are separated.

If you call for all the answer of a specific topic and a specific post you will add:

WHERE topic_id = current, post_id = current

if you need the reply of a topic, just put post_id = -1

And so on. No issues with the ids!

I think I have explained almost all. for any question, ask! EDITED: something here and there

Answer 2

Another thing you can do is make a single rankings table, with a field indicating whether the upvote/downvote is for a post or a ranking. You will have to use a non unique generic "item_id" instead of post id or reply id, but you do get one table if this is important to you.

EDIT: Just realised the foreign key field will be non unique anyway, since you may have multiple votes for the same post; the point is post ids and reply ids can't be told apart.

This WILL make it harder to deal with querying the table. My recommendation is you go with your current plan and make two tables; it is the most semantic way you can organise your data.

Answer 3

I usually think like this:

One user has many posts (this means you'll need a users table and a posts table, and every post will have a user_id to signal who owns it.

For some relations, (for example, when you realize that A has many B and B has many A, that's what we can an n to n relationship), you'll need a third table; this might be what you are hinting at in your comment as to what the reply entity should be translated to in your database....

Feel free to commit errors... almost never you will end up with the exact same model you started with... many things we don't know we need until we do.