What is the best way to implement Polymorphic Association in SQL Server?

Question

I have tons of instances where I need to implement some sort of Polymorphic Association in my database. I always waste tons of time thinking through all the options all over again. Here are the 3 I can think of. I'm hoping there is a best practice for SQL Server.

Here is the multiple column approach

Here is the no foreign key approach

And here is the base table approach

Answer 1

The two most common approaches are Table Per Class (i.e. a table for the base class and another table for each subclass that contains the additional columns necessary to describe the subclass) and Table Per Hierarchy (i.e. all columns in one table, with one ore more columns to allow for the discrimination of subclasses. Which is the better approach really depends on the particulars of your application and data access strategy.

You would have Table Per Class in your first example by reversing the direction of the FK and removing the extra ids from the parent. The other two are essentially variants of table per class.