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

Another common Name for this model is the Supertype Model, where one has a base set of attributes that can be expanded via joining to another entity. In Oracle books, it is taught both as a logical model and physical implementation. The model without the relations would allow data to grow into invalid state and orphan records I would strongly validate the needs before selecting that model. The top model with the relation stored in the base object would cause nulls, and in a case where fields were mutually exclusive you would always have a null. The bottom diagram where the key is enforced in the child object would eliminate the nulls but also make the dependency a soft depenendency and allow orphans if cascading was not enforced. I think assessing those traits will help you select the model that fits best. I have used all three in the past.