I have a table representing an item like so:
item(id, description, motor_id, product_id, drive_id);
motor(id, description, hp, voltage, motor_orientation);
drive(id, feature, model, weight, dimensions, hp);
product(id, description, model, class, type, option1, option1_cost);
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
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.