How to create a foreign key in a column, each record of which can refer to a column in one of several tables?

Question

I am creating a social network. It has several objects, such as news, photos, which may contain comments. Since all comments have the same columns and behave the same, and the only difference is in their type - news, photos or something else that needs to be added in the future - I decided to create one table for all comments with a column named type . It worked fine until I decided to add foreign keys to my database schema.

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

The problem is that I cannot add a foreign key that refers to a previously unknown table, and even more, which refers to several tables at once.

The entire database now uses foreign keys, except for this parent column in the comment table. This bothers me because this is the only place where I cannot add the foreign key.

I am sure I cannot create such a foreign key; something in my database project needs to be changed. I decided to create one table for comments in order to be ready to add new types of comments for new entities in the future - video, music, article, etc. - and not run hellish care when I want to add one new column for all comments.

If I absolutely need to create a separate table for each type of comment in order to make full use of foreign keys, I will do it. But perhaps another common solution to this problem already exists, and I just don't know about it?

Perhaps I need to create some kind of link table that links the comment table with tables of other entities? But maybe this solution is even more complicated than creating a separate table for each type of comment?

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

These solutions simply do not seem elegant to me, or I just misunderstand something. My general idea of ​​this problem may just be wrong. That is why I am here. Share your ideas.

Answer 1

I think your problem is that you have several entities - news, photos. But all these are just types (say) of objects. Like comments, elements are likely to have some common attributes, as well as some different attributes. One of these attributes will be the ability to comment.

In this approach, there is a CommentableItems (1) table, with common attributes. Then you have some sub- NewsItems , PhotoItems , etc. It is very simple to configure the keys for these tables to provide the required one-to-one relationship. Obviously, comments have a foreign key that refers to CommentableItems .

(1) In fact, I will most likely shoot myself, and not let the table, named as badly as CommentableItems , in my schema, but this is just an example.