How can I create a foreign key on a column, every record of which may refer to a column in one of several tables?

Question

I'm in the process of creating a social network. It has several entities like news, photo, which can have comments. Since all comments have the same columns and behave the same way, and the only difference is their type — news, or photo, or something else to be added in the future — I decided to create one table for all comments with a column named type. It worked perfectly until I decided to add foreign keys to my database schema.

The comment table have a column parent, which refers to id of news or photo table, depending on the column type.

The problem is, I can't add a foreign key which refers to the unknown in advance table, and even more, which refers to several tables at once.

The whole database now uses foreign keys, except this one parent column in the comment table. It bothers me because it's the only place where I can't add a foreign key.

I'm sure I can't create such a foreign key; something in my database design needs to be changed. I decided to create one table for comments to be ready to add new comment types for new entities in the future — video, music, article, etc — and don't run into maintenance hell when I want to add one new column for all comments.

If I absolutely have to create a separate table for each comment type to be able to use foreign keys fully, I'll do that. But maybe another common solution to this problem already exists, and I'm just not aware of it?

Maybe I should create some sort of link table, which links the comment table with other entities' tables? But maybe this solution is even more complex than creating a separate table for each comment type?

Maybe I should have several columns in the comment table, like newsId, photoId, to which I can add foreign key?

These solutions just don't seem elegant to me, or I just misunderstand something. My whole perception of this issue might be plain wrong. That's why I'm here. Please share your ideas.

Answer 1

I think your problem is that you have several entities - news, photos. But these are all just types of (say) items. Like comments,items will probably have some attributes in common as well as some distinct attributes. One of those attributes will be the ability to be commented upon.

In this approach you have a table CommentableItems (1), with the common attributes. Then you have some sub-tables NewsItems, PhotoItems, etc. It is quite easy to set-up the keys for these tables to enforce the required one-to-one relationship. Obviously, Comments has a foreign key which references CommentableItems.

(1) Actually I would probably shoot myself rather than allow a table called something as ghastly as CommentableItems into my schema, but this is just for the sake of example.