Query Efficiency - relational data lookups for comments, likes, etc

Question

Relational Database with these tables:

• Users
  • user_id
  • image_url
• Products
  • product_id
• Comments
  • product_id
  • user_id

To pull comments for a product is just a join, but to also include the user's image_url I must do a lookup for each comment to find it's associated user data.

I can think of a few ways to handle this:

• Separate comment DB to offset load with added lookups and general caching solutions
• Create redundant data on the comment table that may be out of date

I'm just assuming that there is a better way of dealing with this problem to make it super clean. Can anyone suggest a better alternative?

Thanks in advance!

Answer 1

I am just curious: why can't it be a three way join? You are joining Products with Comments on product_id. Why not join it with Users as well on user_id (which is in Comments)? Since it is too simple and obvious, I must be missing something but still I wanted to throw it out there :-)