Database design for generic behaviour

Question

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 CommentSet and FavoriteSet, that would be used by Comments and 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?

Answer 1

There are several ways to solve this problem, for example:

Elaborating on your solution 1

For every kind of commentable thing (i.e. table) in your database, you would have a commentSet column.

The first time a user post a comment for a specific item (i.e. row), you would then create a new commentSetid, and put all subsequent comments on the same item in the comment table with the same set. Of course, you'd store for each a timestamp and a user id.

This approach is flexible. The only constraint is to foresee the commentSet column where needed. When displaying items, its very fast to find related comments. It's also very easy to find all the contributions of a specific user.

The main drawback will be to find back the commented items (when starting navigation from a user or a comment set. Here you'd have to look for every potential table that has a commentSet column. This might slow down considerably the search.

There's a way out: in the commentSet put a class identifier (i.e. something that would uniquely identify the table containing the item to be commented). Although this would be easy to use in a programmatic way, it's not so nice, because this is very difficult to use in SQL queries.

Elaborating on your solution 2

This is a nice way to address the bidirectional navigation between commentable items and comments. The relational engine should handle it very fast.

The problem is that your comment table depends on all the othe commentable tables. Everytime you create a new commentable class/table, you'll have to update your comment handling as well... This is not in the sense of separation of concerns.

Additionnally, if you have many different objects that are commentable, you'd have many empty columns in the comment table.

But it's ok. In fact this is an implementation of the single table inheritance object-relational mapping pattern.

other solutions

You may as well consider other orm patterns such as concrete table inheritance or inheritance mappers.

Answer 2

You are missing another possible solution, which is something like the following:

Comment
 - id
 - body
 - subject_id
 - subject_type

The subject_id field would contain the ID of either a user, a post, or anything else that can be commented on. And the subject_type column would be an enum that indicates whether it is a user, or a post, or whatever. This is, as far as I know, the most normalised of the possible solutions.

Answer 3

This sounds like a couple of classic one-to-many relationships. You haven't indicated what the thing is that they are commenting and/or favoriting so I'll just call it "Post" here. It's the root entity of your Entity-Relationship-Model (ERM)... also known as a database "schema".

Consider this schema...

Post
- id  (PK)
- content

Comment
- id  (PK)
- post-id  (FK to Post.id)
- content

Favorite
- id  (PK)
- post-id  (FK to Post.id)
- type