How to handle an "OR" relationship in an ERD (table) design?

Question

I'm designing a small database for a personal project, and one of the tables, call it table C, needs to have a foreign key to one of two tables, call them A and B, differing by entry. What's the best way to implement this?

Ideas so far:

• Create the table with two nullable foreign key fields connecting to the two tables.
  • Possibly with a trigger to reject inserts and updates that would result 0 or 2 of them being null.
• Two separate tables with identical data
  • This breaks the rule about duplicating data.

What's a more elegant way of solving this problem?

Answer 1

If you're sure that C will only ever be referring to one of two tables (and not one of N), then your first choice is a sensible approach (and is one I've used before). But if you think the number of foreign key columns is going to keep increasing, this suggests there's some similarity or overlap that could be incorporated, and you might want to reconsider.

Answer 2

You're describing a design called Polymorphic Associations. This often gets people into trouble.

What I usually recommend:

A  -->  D  <--  B
        ^
        |
        C

In this design, you create a common parent table D that both A and B reference. This is analogous to a common supertype in OO design. Now your child table C can reference the super-table and from there you can get to the respective sub-table.

Through constraints and compound keys you can make sure a given row in D can be referenced only by A or B but not both.