I'd probably pick option C. Put the id into the comment table, then use the commentId as an FK in the tables that need a comment.
The exception to that choice would be if there were many tables that had dependancies on each other that needed to reference a single set of comments (in other words, you truck chassises that had engines in them and wheels on them, and you needed to logically tie one comment to all of the pieces in a given puzzle). Then I would give more thought to a set of linking tables.
better yet, if the database supports it, use a sequence.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Originally Posted by r937
there goes the idea of actually defining it as a foreign key
the correct solution, of course, is D -- have a separate comment table for each object type
I actually agree with the blindman on this!!
It's all well and good saying use multiple tables just to preserve your foreign keys but it comes at a cost. All your code now has to support multiple tables rather than just one. This means more time to produce the code, more effort in maintaining it. You're also quite likely to get more bugs where one query is pulling comments from most of the comment tables but perhaps missing the last comment table that recently got added.