sql server 2008 - How to implement polymorphic associations in an existing database

Question

Polymorphic assiociations (PA's) is quite a mouthful for a relatively simple database requirement: let various tables have child records in one shared table. The classic example is a single table with comment records that apply to different not necessarily kindred entities.

In this question Mark did an excellent job showing three common approaches to implement PA's. I want to use the base table approach, which is described in more detail in an equally excellent answer by Bill Karwin.

A concrete example would look like this:

The primary keys of the entities refer to identical key values in the base table and the Comment table refers to to the base table, so referential integrity is observed. The crucial part here is that the primary keys of the entity tables have distinct domains. They are generated by creating a new record in the base table and copying its generated key to the entity's primary key.

Now my question: what if I want to introduce PA's with referential integrity in an existing database having entities that generate their own, mutually overlapping primary keys?

So far, I see two options:

Option 1:

Each entity keeps its own primary key but also gets an alternate key.

Like:

• Close to the recommended approach.
• Base table is stable.

Dislike:

• Existing entities must be modified.
• Hard to find the owning entity of a comment.

Option 2:

Each entity has its own foreign key column in the base table. This looks like Mark's multiple column approach.

Like:

• Existing entities not affected.
• Easy to find the owning entity of a comment.

Dislike:

• Sparse columns
• Base table not stable: needs modification when a new entity with PA is introduced

I lean to option 1, possibly with a field "EntityName" in the Base table for bidirectional lookup. Which option would be better. Or is another, even better, approach?

Answer 1

You could use Option 1 but without an additional surrogate Alternate Key.

Instead, extend the existing Primary Key (of each entity), with an EntityType column (say CHAR(1), that would be E for Events, P for Persons, D for Products).

The compound (EntityId, EntityType) will become then the Primary Key of table Entity and the corresponding compounds in the other 3 subtype tables.

(The EntityType is just an auxilary, reference table, with 3 rows):