<p>There are several types of objects in a system, and each has it's own table in the database. A user should be able to comment on any of them. How would you design the comments table(s)? I can think of a few options:</p>

<ol>
<li>One comments table, with a FK column for each object type (ObjectAID, ObjectBID, etc)</li>
<li>Several comments tables, one for each object type (ObjectAComments, ObjectBComments, etc)</li>
<li>One generic FK (ParentObjectID) with another column to indicate the type ("ObjectA")</li>
</ol>

<p>Which would you choose? Is there a better method I'm not thinking of?</p>

[{'answer_id': 39938, 'author': 'Hank Gay', 'author_id': 4203, 'author_profile': 'https://Stackoverflow.com/users/4203', 'pm_score': 1, 'selected': False, 'text': "<p>Is it feasible to design the schema so that the commentable (for lack of a better word) tables follow one of the standard inheritance-modeling patterns? If so, you can have the comment table's FK point to the common parent table.</p>\n"}, {'answer_id': 39974, 'author': 'palmsey', 'author_id': 521, 'author_profile': 'https://Stackoverflow.com/users/521', 'pm_score': 0, 'selected': False, 'text': '<p>@Hank Gay</p>\n\n<p>So something like:</p>\n\n<ol>\n<li>ObjectA\n\n<ul>\n<li>ObjectAID</li>\n<li>ParentID</li>\n</ul></li>\n<li>ObjectB\n\n<ul>\n<li>ObjectBID</li>\n<li>ParentID</li>\n</ul></li>\n<li>Comments\n\n<ul>\n<li>CommentID</li>\n<li>ParentID</li>\n</ul></li>\n<li>Parents\n\n<ul>\n<li>ParentID</li>\n</ul></li>\n</ol>\n'}, {'answer_id': 39997, 'author': 'Hank Gay', 'author_id': 4203, 'author_profile': 'https://Stackoverflow.com/users/4203', 'pm_score': 2, 'selected': True, 'text': "<p>@palmsey</p>\n\n<p>Pretty much, but the variation on that pattern that I've seen most often gets rid of <code>ObjectAID</code> et al. <code>ParentID</code> becomes both the PK and the FK to <code>Parents</code>. That gets you something like:</p>\n\n<ul>\n<li><p><code>Parents</code></p>\n\n<ul>\n<li><code>ParentID</code></li>\n</ul></li>\n<li><p><code>ObjectA</code></p>\n\n<ul>\n<li><code>ParentID</code> (FK and PK)</li>\n<li><code>ColumnFromA NOT NULL</code></li>\n</ul></li>\n<li><p><code>ObjectB</code></p>\n\n<ul>\n<li><code>ParentID</code> (FK and PK)</li>\n<li><code>ColumnFromB NOT NULL</code></li>\n</ul></li>\n</ul>\n\n<p><code>Comments</code> would remain the same. Then you just need to constrain ID generation so that you don't accidentally wind up with an <code>ObjectA</code> row and an <code>ObjectB</code> row that both point to the same <code>Parents</code> row; the easiest way to do that is to use the same sequence (or whatever) that you're using for <code>Parents</code> for <code>ObjectA</code> and <code>ObjectB</code>.</p>\n\n<p>You also see a lot of schemas with something like:</p>\n\n<ul>\n<li><code>Parents</code>\n\n<ul>\n<li><code>ID</code></li>\n<li><code>SubclassDiscriminator</code></li>\n<li><code>ColumnFromA (nullable)</code></li>\n<li><code>ColumnFromB (nullable)</code></li>\n</ul></li>\n</ul>\n\n<p>and <code>Comments</code> would remain unchanged. But now you can't enforce all of your business constraints (the subclasses' properties are all nullable) without writing triggers or doing it at a different layer.</p>\n"}, {'answer_id': 40016, 'author': 'Eric Z Beard', 'author_id': 1219, 'author_profile': 'https://Stackoverflow.com/users/1219', 'pm_score': 0, 'selected': False, 'text': "<p>Be careful with generic foreign keys that don't point to exactly one table.  Query performance suffers dramatically if you have to split the where condition on a type and point to several different tables.  If you only have a few types, and the number of types will not grow, it's Ok to have separate nullable foreign keys to the different tables, but if you will have more types, it's better to come up with a different data model (like @palmsey's suggestion).</p>\n"}, {'answer_id': 106044, 'author': 'Craig Walker', 'author_id': 3488, 'author_profile': 'https://Stackoverflow.com/users/3488', 'pm_score': 0, 'selected': False, 'text': "<p>One of the things I like to do is have a separate tables that link the generic/common table to all of the individualized tables. </p>\n\n<p>So, for objects Foo and Bar and then comments on Foo &amp; Bar, you'd have something like this:</p>\n\n<ul>\n<li>Foo\n\n<ul>\n<li>Foo ID (PK)</li>\n</ul></li>\n<li>Bar\n\n<ul>\n<li>Bar ID (PK)</li>\n</ul></li>\n<li>Comment\n\n<ul>\n<li>Comment ID (PK)</li>\n<li>Comment Text</li>\n</ul></li>\n<li>FooComment\n\n<ul>\n<li>Foo ID (PK FK)</li>\n<li>Comment ID (PK FK)</li>\n</ul></li>\n<li>BarComment\n\n<ul>\n<li>Bar ID (PK FK)</li>\n<li>Comment ID (PK FK)</li>\n</ul></li>\n</ul>\n\n<p>This structure:</p>\n\n<ol>\n<li>Lets you have a common Comments table</li>\n<li>Doesn't require a DB with table inheritance</li>\n<li>Doesn't pollute the Foo and Bar tables with Comment-related information</li>\n<li>Lets you attach a Comment to <strong>multiple</strong> objects (which can be desireable)</li>\n<li>Lets you attach other properties to the junction of Foo/Bar and Comment if so desired.</li>\n<li>Still preserves the relations with standard (ie: fast, simple, reliable) foreign keys</li>\n</ol>\n"}]
['https://Stackoverflow.com/questions/39915', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/521/']