does all of the comments in a post belong to the same user? so only he/she adds comments to a post? if not, you need a user_id column to know who is the author of the comment
Foreign key tips for database design
I have three tables. users, posts and comments. User may have many posts and a post may have many comments. My question is, do I need to define user_id in comments table? I can find a user from comment id by traversing from comments table to posts table to users table. But if I define user_id in comments table also, it seems useful since I dont have to traverse a lot, I can directly query users table from comments table.
But, this approach can disrupt the consistency. Meaning technically, by mistake, it is possible that a user_id gets assigned to a comment that belongs to a post that the user did not posted.
However to deal with this, I think it is good to define COMPOSITE PRIMARY KEY in posts table as $primary(['user_id','post_id']); And pass this composite primary key to comments table. (well, i dont know how do I do it.)
My question is what is the best table structure for this example considering the pros and cons.
Please or to participate in this conversation.