I have a few tables that are generic. One of them allows a user to comment on something, and the other one allows a user to favorite something.
Initially, I had thought that to make it possible to favorite and comment on anything, I could have another table named something like
FavoriteSet, that would be used by
Favorites which would then be used as a foreign key on anything that needed to be "commentable" or "favoritable".
CommentSet - id Comment - id - body - setId Post - commentSetId - ...
This seemed to work fine, but there were a few drawbacks with this approach:
- There was no way to figure out what item a favorite or comment was on
- Added complexity requiring the "set" rows to be created when needed
Instead I thought of using foreign keys directly on the tables:
Comment - id - body - postId (for example) - userId (another example)
With this approach there doesn't seem to be any issues and:
- It is now possible to figure out what item the comment/favorite is on (by using the FK)
- No added complexity, you only have to create a comment/favorite and nothing else.
One thing I was worried about was performance, although I don't know for sure this would be bad for performance. Which approach should I use, or is there a better way?