What is the correct way to map relationships between structurally similar comments tables and commentable entities? I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments.

The problem is that I have multiple commentable entities. My initial thought was to create a table for each entity, like posts_comments, and profile_comments, but that just feels wrong as all the comments are structurally similar. If I decide to allow commenting on say, user-posted pictures, then I would need to add another picture_comments table with identical columns. And then if I decide to add or remove any column, I would have to modify all the tables for consistency.

So I'm considering whether I should instead have a single comments table and then maintain referential integrity through intermediary join tables with a unique index on the comment_id foreign key to ensure the one-to-many relationship. Are there any performance or size-related downsides from doing this?

What is the best way to handle this in postgres or SQL in general? I looked around for solutions or clues on stackoverflow and dba exchange but did not find a satisfying answer. If someone can provide an answer or opinion or a link to an already answered question, I would really appreciate it.
I think you should really look into using inheritance to manage this particular issue.

Inheritance

Table inheritance does many things in PostgreSQL, and I'd highly recommend you refer to the online documentation about inheritance to get a feel for what it does and how it might work for you.

Since you stated that your comments are all structurally similar, then you can make a parent comments table as

CREATE TABLE comments (
/* columns here */
);


Then you can create the child comment tables as (for example)

CREATE TABLE posts_comments (
/* any columns unique to this table here */
) INHERITS(comments);


Now, posts_comments will have all columns from comments, plus any additional column fields which you believe are necessary for that particular table.  Rinse and repeat to make other tables which are children of the comments table.

Further, you can then query for all comments which match a particular condition

SELECT * FROM comments
WHERE condition = true;


and all comments meeting you condition will be returned.  That is, every comment from the comments table (if there are any entries, since this might just be an empty parent table), plus all comments from the child tables posts_comments, profile_coments, etc. will be returned by the SELECT.

You can also have ALTER TABLE propagate changes to all child tables through this structure.

Does this meet your needs?