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?

  • The biggest drawback (in both approaches) that I see is that it is not guaranteed that a comment applies to exactly one post or one user. It is possible for a comment to apply to both a post and a user or to none of them. That is something that should be handled gracefully. – Bart van Ingen Schenau Jul 5 at 6:47
  • I thought about that too, but if I'm using postgreSQL, is there not a constraint that could handle that? And even if so, realistically it wouldn't really be an issue since a comment would be created programmatically anyway through my API. – Sebastian Olsen Jul 5 at 6:50
  • Are you limited to only using an RDBMS with a strict relational structure, or could you use a denormalized schema or even a NoSQL db for this project or this part of the project? – Paul Jul 5 at 9:46
  • It's my personal project, but it's a project where I feel a RDBMS is appropriate, plus I don't really have any experience with other solutions so I'd rather not change it at this point. – Sebastian Olsen Jul 5 at 10:00
up vote 1 down vote accepted

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.

  • So, you're saying solution 2 is viable and would work in production? – Sebastian Olsen Jul 5 at 10:00
  • @SebastianOlsen yes, certainly, and especially if there are not too many commented tables. – Christophe Jul 5 at 11:00

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.

  • I've considered this before but there is one reason to why I decided to not bother with this, and that is because this approach makes it more or less impossible to query the target item of the comment. – Sebastian Olsen Jul 6 at 9:39
  • It might make it a bit more complicated if you're writing the SQL yourself, but I don't think it makes it impossible, many ORMs use this method for storing polymorphic objects and will handle this for you. – Sean Burton Jul 6 at 9:41
  • I'm not using an ORM, and I'm fairly certain that you cannot do it with SQL alone, I've tried. – Sebastian Olsen Jul 6 at 10:24
  • @Sebastian Olsen - You are right. The Subject table would require Foreign Keys to User, Post, etc. and the SQL queries would get pretty gnarly. Chances are, that would result in one or more of the dreaded, performance-killing, full table scans of large tables. With something like the solution we've discussed, since all the PKs would have indexes, the queries using the FKs pointing to them would usually be almost instantaneous. – DocSalvager Jul 8 at 7:53

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
  • 1
    How is this different from my second solution? – Sebastian Olsen Jul 8 at 2:55
  • It's not. This just shows all the relevent tables, columns and keys using more typical names. – DocSalvager Jul 8 at 5:49

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.