Lets say i have 3 tables: users, posts and comments.

users - contains information about registered users

posts - contains information about posts that were created by users

comments - all comments on one or multiple posts created by one or multiple users

enter image description here .

This image is how my comments table looks like. user_id and post_id are foreign keys in this table but primary keys in users and posts tables respectively. However this structure doesn't seem right to me because as you can see the post_id and user_id may get repeated multiple times.

My question is - is this structure good and should i use this, or should i create a new "middleman" table to hold user_id and post_id in order to somehow reduce redundancy? If so, how? Can you give me an example?


closed as off-topic by Daniel, Stephen Rauch, Stop ongoing harm to Monica, yuri, Graipher Aug 5 '18 at 5:38

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – Daniel, Stephen Rauch, Stop ongoing harm to Monica, yuri, Graipher
If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ Shouldn't this be placed in stackoverflow? \$\endgroup\$ – Gareth Ma Aug 5 '18 at 2:16

If your example means that, say, comment #1 is by user 8 about post 2, and comment #2 is by user 9 about post 7 then you're doing it right. This is a classic method to organize a table that relates the contents of other tables; you can use it to get all the comments on a particular post, or all the comments by a particular user, and so on. Since the answers to those questions are "some, one, or more" the repeated values are not a problem. If you saw the same exact combination in all three columns that might indicate a poor design -- but in this case it looks pretty appropriate.

The table as shown does not have a column for the actual comment data; you could add that here or foreign key to another table that contained the actual comment text.


Not the answer you're looking for? Browse other questions tagged or ask your own question.