Dennis Dennis - 1 year ago 42
SQL Question

How to improve table design that has multiple foreign keys, but only one of the keys is actually used at any one time?

I have a table representing an item like so:

item(id, description, motor_id, product_id, drive_id);

The issue I have is that an item can be only one of a "motor", a "drive", or a "product". In each case the foreign key points to a different table full of motors, products or drives.

Can this table design for
be improved, or is it pretty much good as is?

My concern is that having 3 foreign keys, only one of which can be used at a time is a sign of bad design.

Structure of other tables

motor(id, description, hp, voltage, motor_orientation);
drive(id, feature, model, weight, dimensions, hp);
product(id, description, model, class, type, option1, option1_cost);

Answer Source

This is a legitimate way to implement a "one-of" relationship using a relational database. In addition to the above, you should mandate that exactly one of the columns is not NULL:

alter table t
    add constraint chk_t_foreignkeys
         check (((case when motor_id is not null then 1 else 0 end) +
                 (case when product_id is not null then 1 else 0 end) +
                 (case when drive_id is not null then 1 else 0 end)
                ) = 1

This method of implementing a "one-of" relationship has the advantage that foreign keys are explicitly declared and enforced. It has the downside that most databases will still reserve space for each key, even though only one is used.