Generic Database table design

Question

Just trying to figure out the best way to design my table for the following scenario:

I have several areas in my system (documents, projects, groups and clients) and each of these can have comments logged against them.

My question is should I have one table like this:

CommentID
DocumentID
ProjectID
GroupID
ClientID
etc

Where only one of the ids will have data and the rest will be NULL or should I have a separate CommentType table and have my comments table like this:

CommentID
CommentTypeID
ResourceID (this being the id of the project/doc/client)
etc

My thoughts are that option 2 would be more efficient from an indexing point of view. Is this correct?

Answer 1

Option 2 is not a good solution for a relational database. It's called polymorphic associations (as mentioned by @Daniel Vassallo) and it breaks the fundamental definition of a relation.

For example, suppose you have a ResourceId of 1234 on two different rows. Do these represent the same resource? It depends on whether the CommentTypeId is the same on these two rows. This violates the concept of a type in a relation. See SQL and Relational Theory by C. J. Date for more details.

Another clue that it's a broken design is that you can't declare a foreign key constraint for ResourceId, because it could point to any of several tables. If you try to enforce referential integrity using triggers or something, you find yourself rewriting the trigger every time you add a new type of commentable resource.

I would solve this with the solution that @mdma briefly mentions (but then ignores):

CREATE TABLE Commentable (
  ResourceId INT NOT NULL IDENTITY,
  ResourceType INT NOT NULL,
  PRIMARY KEY (ResourceId, ResourceType)
);

CREATE TABLE Documents (
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL CHECK (ResourceType = 1),
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

CREATE TABLE Projects (
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL CHECK (ResourceType = 2),
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

Now each resource type has its own table, but the serial primary key is allocated uniquely by Commentable. A given primary key value can be used only by one resource type.

CREATE TABLE Comments (
  CommentId INT IDENTITY PRIMARY KEY,
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL,
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

Now Comments reference Commentable resources, with referential integrity enforced. A given comment can reference only one resource type. There's no possibility of anomalies or conflicting resource ids.

I cover more about polymorphic associations in my presentation Practical Object-Oriented Models in SQL and my book SQL Antipatterns.

Answer 2

Read up on database normalization.

Nulls in the way you describe would be a big indication that the database isn't designed properly.

You need to split up all your tables so that the data held in them is fully normalized, this will save you a lot of time further down the line guaranteed, and it's a lot better practice to get into the habit of.