An Interesting Relationship Mapping Scenario

This week on the ADO.Net pre-release forum, someone brought up an interesting relationship mapping scenario in this thread.  Essentially the scenario is this:  They have a table which represents an entity, call it Foo, and along with the other columns, the Foo table has a column which is an foreign key ID and another column which denotes a type.  Foo entities have relationships with three other entities, call them A, B & C, but they can only have one relationship at any one time.  The type indicates which relationship is in use, and the ID is then treated as a foreign key to which ever table the type indicates.

On one level, this makes perfect sense even if it's not the same kind of pattern we usually think about, but on another level it's not hard to see why the EF (and other ORMs) do not support this out of the box.  The problem is that there's no good way to describe in the conceptual model the fact that an instance of Foo can only have one of the three relationships at any particular time.  The object model would actually end up with references to all three types, so it would be all too easy to set more than one of those references to a non-null value and then not be able to save changes because there's only one ID column.

Nevertheless, here's a customer whose database funcitons this way today with hand-written code, and it seems like we ought to be able to do something with the EF.  So after some thought, I came up with a few alternatives:

Option #1: Use inheritance and relationships between subtypes.
It's not hard to figure that this solution won't work in many situations, but if it will work for a particular situaiton, then it's the most natural option.  Basically the idea is to use the type field to create a TPH inheritance hierarchy of FOO types.  You could call them FooA, FooB & FooC, where each of these subtypes has a relationship to one of the other types.  The nice thing about it is that the conceptual model now understands that only one relationship can be in place at a time, and the object model matches as well since each type only has one reference to the other type which it can be related to.  There are two major problems, though:  First off, Foo might already have or be part of another inheritance hierarchy in which case inheritance isn't really an option.  Secondly, this mechanism assumes that any one Foo instance has the same relationship throughout its life.  It would not be possible to take a FooA and decide later to relate it to B instead, because that would require destroying the instance of FooA and creating a new instance of FooB.

Option #2: Use a view to transform the storage table plus sprocs for updates.
A more creative approach would be to create a view in the database which does not expose the ID & type columns directly the way the underlying tables does but instead exposes 3 different ID columns--call them IdA, IdB, & IdC--each of which has the ID value only if the type indicates a realtionship to the corresponding entity and otherwise is null.  Once you do that, Foo can have three null-able relationships where the association sets are mapped only if the value is non-null.  That way, only one of the relationships would be active at a time.  The Foo object model would have 3 references but two of them would be null at any one time.  No inheritance hierarchy would be needed, and instances could change which other entity they are related to just by setting the appropriate reference to a non-null value and the other ones to nulls.  The downside is that you have to write the view and, more importantly, that all updates would have to go through stored procs since such a view would not be directly updateable.  Essentially this mechanism would fool the entity framework into thinking that the database looks differently than it actually does, and it would depend on the sprocs to enforce validation of the relationships when updating rather than doing the enforcement itself.

Option #3 (in a future EF release): Model the relationships as containment.
One last possibility which I'll mention is that we have talked about adding to the EF in a future release the idea of a containment relationship.  The basic idea of this construct is that "child" entities maybe related to one or more "parent" entities but only one parent at a time.  This would then allow the conceptual model to more directly represent the idea in the database, and we would then be able to create a declarative mapping for the scenario.  I will point out, though, that this definitely won't make v1, and there are still a number of details to work out in the design.

While probably none of these is exactly what the original requester had in mind, I was pleased to discover that the EF is flexible enough to provide some decent alternatives.

- Danny