I've got an entity that I need to associate with 1 of 2 other entities. The relationship is exclusive - if it's associated with one it can't be with the other.
I'd considered creating a lookup table that held the OwnerID's of the 2 parent entities, as well as the ID for the child -- then adding a check constraint or something to make sure that only one is set. These could then be joined in a view or something so I have Parent1Children and Parent2Children, checking that their ParentID is in the relevent column.
I'm looking for suggestions as to how this can be done, i'd like to have the child entity in one table for all, and associate it with the relevant parent.
I was aiming to try and separate it out a bit -- leave the child entity to focus on it's job, and then have the lookup table performing the guts of the work. I see the point that it's probably better joined into the child, especially to reduce the overhead of the joins.