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?

Accepted Answer

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.

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

Solution

Problem