Extra tables or non-specific foreign keys?

Question

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:

1. One comments table, with a FK column for each object type (ObjectAID, ObjectBID, etc)
2. Several comments tables, one for each object type (ObjectAComments, ObjectBComments, etc)
3. One generic FK (ParentObjectID) with another column to indicate the type ("ObjectA")

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

Answer 1

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.

Answer 2

@Hank Gay

So something like:

1. ObjectA
   • ObjectAID
   • ParentID
2. ObjectB
   • ObjectBID
   • ParentID
3. Comments
   • CommentID
   • ParentID
4. Parents
   • ParentID

Answer 3

@palmsey

Pretty much, but the variation on that pattern that I've seen most often gets rid of ObjectAID et al. ParentID becomes both the PK and the FK to Parents. That gets you something like:

• Parents

  • ParentID

• ObjectA

  • ParentID (FK and PK)
  • ColumnFromA NOT NULL

• ObjectB

  • ParentID (FK and PK)
  • ColumnFromB NOT NULL

Comments would remain the same. Then you just need to constrain ID generation so that you don't accidentally wind up with an ObjectA row and an ObjectB row that both point to the same Parents row; the easiest way to do that is to use the same sequence (or whatever) that you're using for Parents for ObjectA and ObjectB.

You also see a lot of schemas with something like:

• Parents
  • ID
  • SubclassDiscriminator
  • ColumnFromA (nullable)
  • ColumnFromB (nullable)

and Comments 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.

Answer 4

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).

Answer 5

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.

So, for objects Foo and Bar and then comments on Foo & Bar, you'd have something like this:

• Foo
  • Foo ID (PK)
• Bar
  • Bar ID (PK)
• Comment
  • Comment ID (PK)
  • Comment Text
• FooComment
  • Foo ID (PK FK)
  • Comment ID (PK FK)
• BarComment
  • Bar ID (PK FK)
  • Comment ID (PK FK)

This structure:

1. Lets you have a common Comments table
2. Doesn't require a DB with table inheritance
3. Doesn't pollute the Foo and Bar tables with Comment-related information
4. Lets you attach a Comment to multiple objects (which can be desireable)
5. Lets you attach other properties to the junction of Foo/Bar and Comment if so desired.
6. Still preserves the relations with standard (ie: fast, simple, reliable) foreign keys