Menu
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.
888
MySQL allows us to add a FOREIGN KEY constraint on multiple columns in a table. The condition is that each Foreign Key in the child table must refer to the different parent table.
To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Follow that with the name of the referenced table and the name of the referenced column in parentheses.
A table can have multiple foreign keys based on the requirement.
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.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
