Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002

    Modeling either-or relationship

    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.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    in the logical model, the child entity has two parent entities

    so, two foreign keys

    obviously, both must be optional

    okay, that pretty well completes the logical design

    moving now to the physical, your lookup table is a great idea, but why not consolidate that into the child table itself?

    in other words, the child table has two foreign keys, both nullable

    i think we're done

    any questions? i'd be happy to handle them | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2002
    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.

    Thanks for the suggestion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts