I am trying to figure out the cleanest way to allow users to add comments on multiple types of tables. Here is an example:


Tables users can comment on:
- Cars
- Trucks
- SUVs


Comment Table Layout:
id
userId
targetType (table name for the comment's target)
targetId (this is the id of one of the above tables)
content
dateAdded


I was wondering if there is a better way to go about this, if this is not clear please let me know and I will do my best to further explain.


Thanks in advance!

there are several ways to approach this, but your idea of storing the table name isn't one of the good ones

one approach is to have a separate comment table for each table that allows comments

this is the simplest and easiest, and the only people who don't like it are usually programmers who have spent their entire lives looking (often needlessly) to make "optimizations"

another approach is to ask why in the world would you have separate tables for cars, trucks, and suvs, when you could have just one table for vehicles?

in fact, this is the direction you should take -- a table for vehicles, and then of course only one comment table, and in addition, you could have secondary tables for cars and trucks and suvs if there is enough difference in the data columns that these different types of vehicles require

(and if there aren't enough differences, then of course a single vehicles table is sufficient)


the concept i just described is called "supertype/subtype" and a web search will reveal lots more information

just remember that the comments go on the supertype

The real tables I am creating don't have anything to do with cars or trucks. I am required to keep the actual names under wraps until release. However, to entertain the idea, each table is going to be drastically different. So there would be many fields that would be worthless to other tables.

I'm not throwing out the idea for making a separate comment table per target table (as it certainly sounds easy to implement), but I am curious in exploring other options; mainly because i haven't had an opportunity to talk with many database gurus.

neither did my answer

Ok, well to clarify. The tables I am creating seem as though they should not have a parent, they really don't have anything in common other than they share the ability to be commented on.

Are you recommending that even if this is the only thing they share there should still be a parent for each of those tables that holds a comment.id?

According to your description, it looks like they do have a key in common: "targetid", which is supposed to reference one of the three tables. So it may make sense to create the super type table.

Without specifics, it's hard to give recommendations other than what r937 has already given.

If there is truly nothing in common among the tables other than that you want to hold comments for them, create a separate comment table for each table.

However, if there is some commonality among the tables (they're all 'products', for example), then use subtypes and create one comment table referencing the supertype.

The only other scenario I can imagine -- though I suspect any realistic one I could come up with would point to subtyping (or a missing relation) -- is if the tables are truly distinct but a comment can apply to several of them at once. In this case, create one comment table, then either a) set up intersection tables between the comment table and the tables to which the comment can apply, or b) hold a (nullable) foreign key in the comment table for each table to which it can apply.

You need to look at your model and determine which case applies. In any case, do NOT consolidate comments into one comment table simply because multiple tables can have comments. Down that road lies the OTLT, and that is a road you don't want be on.

One final note: the assumption here is that you are interesting in holding multiple comments for each row in the table being commented on. If this is not case, and you need to store at most one comment per row, then simply implement the comment as an additional column in the table(s) of interest.

I like the idea of intersection tables, I am assuming that be like adding a table like BlogComments(blogId,commentId)?

edit: actually i think that nullable foreign keys would be the easiest to implement while maintaining one comment table.


thanks for your advice guys