Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks for your excellent feedback. It was the supertype/subtype that we used but I have been guilty of over-engineering on occasion and wanted a second opinion. –  tcarvin May 17 '12 at 12:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.