Comments table with multiple types of parents

Question

My site (ASP.NET/C#/MS-SQL 2005) has multiple sections that allow for comments (User profiles, image pages, videos, etc). I want to store everything in a single comments table.

My issue is linking the comment back to its parent. The user profiles' primary keys are uniqueidentifiers, while the images and videos use ints. I'd like to have a foreign key relationship from the comment to its parent.

What would be considered best-practice for this?

Answer 1

There is debate over the best practice here. My opinion is to use surrogate id's for everything. Even use it for the user profile, whether you need it or not. Then, you only have to deal with one "pattern" for connecting related entities. This lets you automate a lot of boilerplate code if you want. Even if you don't automate the boilerplate, it will be simpler to write and less error-prone.

Answer 2

I would use join tables, one for each section that allows comments. Then you would use whatever key you want for the comments table and your join table would just need to have the identifier from your section table and from your comments table.

  user_profiles
  id uniqueidentifier
  ...

  comments
  id int
  ...

  user_profile_comments
  profile_id uniqueidentifier
  comment_id int