Search code examples
phpmysqlforumorganization

Table design for a forum

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?

Solution

  • 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.