1

I have been trying to create an online discussion forum as a fun project. In this application, registered users can create a "topic". Other users can reply to that topic (lets call them "posts"). Also, people can "reply" to posts . However, reply to "replies" is not required at present.

I thought of creating a DB schema like this enter image description here

But, the posts and replies table basically contain the same kind of fields. Only exception is that- the topic_id column in posts table will refer to topics table and post_id column in replies table will refer to the posts table

So, if I have to keep only one table for posts and replies, how do I manage the relationship between "topics and posts" and "posts and replies" ? What would be the ideal solution in this case?

Or

Should I just keep my existing schema?

Thanks!

webizone
  • 41
  • 1
  • 8

2 Answers2

1

Replies are not posts, so use a different table. Tables don't cost anything. 1 reply can have many posts, having both in the same table is very messy and will make for very ugly sql when querying the data. Replies can have replies, so add a parent id to the reply table so you can drill up.

Programnik
  • 1,449
  • 1
  • 9
  • 13
  • Obviously this is up to preference, but I agree with this 100%. Relational Databases are meant for active relations (i.e. this exact situation). Also displaying the information based on an actual related FK relationship on the page is going to take a single query with a join (easy). Grouping posts to comments if it were a single table would require multiple queries if I'm not mistaken to show the relation correctly. – ViaTech Aug 10 '23 at 12:42
0

You can keep one table only for posts and replies. For the relationship between post and reply add a new column names post_id. This column can be null for post, and in the case of reply insert id of the post.

suman
  • 68
  • 4
  • Thanks! I was wondering, is there any specific advantage or disadvantage for this approach over my existing schema (in terms of database optimization) – webizone Mar 06 '18 at 07:15
  • It comes down to personal preference. I would probably go for the one table approach where a 'post' is simply a 'reply' with no parent (i.e. parent_id is null) – Strawberry Mar 06 '18 at 08:18
  • @strawberry Erm, that's what he said ;-) – Paul Campbell Mar 06 '18 at 08:39