Search code examples
mysqldatabaseforum

One or two tables for forum?

Im building a forum and I'm wondering whether I should have one table where I store all main posts and then all the answers in another table.

I've always stored everything in one table, making it east to count and let users comment every post (comments in another table).

What should I do? Pros and cons? Tried to google but didn't find anything.

Thanks for your help!

Solution

  • I usually follow the rule every type of dataset gets a own table. This way you can cleanly define relationships

    You have types like

    • userTypes (e.g. guest,user,mod,admin)
    • users (has one userType_id)
    • posts (has one user_id)
    • answers (has one post_id)
    • comments (has one post_id, has one answer_id)

    Since comments can be added for both, a post and an answer, you could add two bridge tables to define this relationship.

    • comment_to_answer (has one comment_id, has one answer_id)
    • comment_to_question (has one comment_id, has one question_id)

    In case you save both, posts and answers into one table, posts table would need to reference themselves to define the posts - answers one-to-many relationship, which would make querying more complicated.

    If you want to be able to cascade, post can have an answer, answer can have an answer and so on, you probably go better with one posts table and a parent_id pointing at the id of posts

    Hope this helps.