Use multiple columns for location or supertype/subtype?

Given an Equipment entity, it has a location, generically called Whereabouts. This could be a reference to:

-
a User entity (the employee who will use and be responsible for the eqipment for the next week).

-
a Vendor entity (if the equpment is getting serviced).

-
a StorageLocation entity (if the equpment is put away).

The piece of equpment can only be in one place at a time, even if that place is not a physical location.

Is it better:

-
to have Equipment with three columns (UserId, VendorId, StorageLocationId) such that one of these columns must be not null while the other two must be null (enforced by a constraint)?

-
or to have Equipment with a WhereaboutsId that references some type supertype table that then links to User, Vendor, and StorageLocation as subtype tables, even though they share no other common attributes?

-
or something else?

My approach to design is to think about the what-if's:

• How would the design need to change to add a 4th type?

• How would it need to change to store a common attribute?

• How would it need to change to store an attribute for only one of the types?

I think after going through the process of answering the questions above for the proposed designs, it will be obvious that using a supertype/subtype structure is definitely the way to go (I dare say this is a best-practice for the situation).

There are things to be concerned about, of course -- for example, ensuring that for a given supertype, a matching record must exist in exactly 1 subtype table. With a well-written stored procedure to do "transfers" between types (which is something I assume you'll need), this should be pretty straightforward to implement.

StackExchange Database Administrators Q#18016, answer score: 5