How to store comments in a database?

Question

Is using one table for comments for a social network efficient...i mean the server needs to search the whole comments table and find the comments that belong to a certain post in order to render comments on the page. Imagine doing that for millions of users at the same time.

Answer 1

I ain't a DB expert but i think it's pretty straightforward

Suppose you have a posts table and a comments table with the following columns:

posts(id, user_id, body, create_date, update_date)

comments(id, post_id, body, create_date, update_date)

Using post_id as a foreign key should be enough => when retrieving a post, you'd also retrieve all comments with the post_id and have them sorted by create_date

For efficiency you could index your tables

https://www.geeksforgeeks.org/sql-indexes/

also, you might want to use an ORM to help you make the queries

Answer 2

So indexing is the only way to optimize our DB efficiency...thanks.