Monday, September 3, 2012

Conceptual Business Modeling for Database Design: Entity Supertype-Subtypes

See the update on 1/5/16.

Meaning Criteria and Entity Supertype-Subtypes 


  1. Interesting post! I can tell you that after building a system for an auction mgmt system that did NOT use property-sharing entities, I learned my lesson and ended up building a second system for a martial arts concern that did. The auction management system has separate bidder and consignor tables and in real life some bidders ended become consignors. By not using property-sharing entities I ended up having to duplicate records across both tables. It wasn't the end of the world, but clearly could have been thought out better. My second major build used a contact table, just as you mentioned, along with student, instructor and billee tables that each had an fk to contact. This has worked well and has reduced duplication. I think when designing your system the key question to consider is the role of these contacts in your system and how they might evolve over time. If a contact will only ever exist as one "type" forever (i.e. a student will only ever be a student, faculty will only ever be faculty) then having separate tables make sense. But if that contact (and contact is a much better name for the table that "entity") could ever exist as more than one "type" then it certainly makes sense to use property-sharing entities .

    As for the need for logging? Yes, you can go through the trouble, but I've never had a client tell me that they needed to know what address a contact was using 4 years ago. As long as the information for the contact is current that's what seems to matter most.

    1. Database design is driven by the conceptual model. How good the latter is determines how useful the former is.

      Practitioners tend to conflate the conceptual with the logical -- they rush into design with poorly specified models.


View My Stats