How to handle OR relationships in an ERD (table) project?

Question

I am developing a small database for a personal project, and one of the tables that invokes its table C must have a foreign key for one of the two tables, call them A and B , which differ in entry. What is the best way to implement this?

Ideas so far:

• Create a table with two field fields with a zero foreign key that join the two tables.
  • Possibly with a trigger to reject inserts and updates that will result in 0 or 2 of them being empty.
• Two separate tables with identical data
  • This violates the data duplication rule.

What is a more elegant way to solve this problem?

Answer 1

You are describing a design called Polymorphic Associations. This often leads people to trouble.

What I usually recommend:

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

In this design, you create a common parent table D containing a link A and B This is similar to the general supertype in OO design. Now your child table C can refer to the super-table and from there you can get to the corresponding subtable.

Using constraints and compound keys, you can make sure that for a given line in D you can only refer to A or B , but not both.

Answer 2

If you are sure that C will only refer to one of the two tables (and not to one of N), then your first choice is a reasonable approach (and the one I used before). But if you think that the number of columns of the foreign key will continue to increase, this indicates a similarity or overlap that may be included, and you might want to reconsider.