Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2010-07-07 : 16:47:47
|
| We've all see the classic type inheritance example with Vehicle as the supertype and Car, Truck and Motorcycle as the subtypes. Presuming engine displacement is an attribute of vehicle, what do you do when a new type, Bicycle, is added to the model?Do you make engine displacement nullable? (no)Do you move (duplicate) the engine displacement down into Car, Truck and Motorcycle?Do you create two intermediary types {Engine Powered Vehicle, Human Powered Vehicle} and deal with a 3 tiered model?Do you create a new Optional Vehicle Attributres table that hangs off vehicle and move engine displacement to that?Do you not consider Bicycle to be a subtype of Vehicle?For a model where a supertype is generalized by N (where N > 2) subtypes and and some attribute is only applicable to N-1 of those subtypes, where should that attribute exist?Jayto here knows when |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-07 : 18:00:55
|
| A good read that might help:http://sqlblog.com/blogs/louis_davidson/archive/2008/10/15/inheritance-in-database-design.aspxHe also covers subclasses in this book:http://apress.com/book/view/143020866xPersonally I don't feel supertypes and subtypes belong in a database model. If they do have to go in the DB, they should not slavishly adhere to their procedural code object structures. Some of the reasons are:- A code object is both behavior (methods) and data (attributes/properties), whereas the DB is only attributes/properties- Relational and OO design are fundamentally different (if not mutually exclusive) in intent and practice- Trying to make the 2 worlds work too closely together compromises both and exacerbates their shortcomings more than their strengthsThe last point, to me, is the most important problem. The scenario you're describing is more about the behavior of the objects rather than their attributes (human vs. engine especially). I would argue that Human and Engine are both Power attributes, not types, and I would probably model them that way.Ultimately I would end up with the most generic Vehicle object possible, and use either child tables for very specific types, or an EAV structure to cover onesie-twosie attributes like you have described. I couldn't justify the effort and heartache of trying to make the data model match the procedural object model.But that's just me. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2010-07-08 : 13:28:15
|
| Thanks for your input Rob.The common use of terms like "subtype/supertype" and "inheritance" in DB design discussions is unfortunate. Date talks about Delegation as potentially a better term in Chapter 20 of An Introduction ... although that chapter really deals with Types on the logical layer, rather than physical implementation details, so maybe I'm committing the same offense with my mis-used terms.The place I've used this design philosophy the most is when implementing a "Party Model" to deal with people, corporations, etc etc (I know, it is hard to say "Party Model" with a straight face).I'm modeling meta-data for an ETL system in a data warehouse. All along I've had my Entity table which contains all the common attributes of my Fact and Dimension metadata tables. I have another "subtype" called EntityCopy with specific instructions for the ETL system on copying an Entity from one data mart to another. This implementation of subtype/supertype has worked well with regards to logging and auditing within the ETL. Unfortunately, along came the need to add non-ETL'ed Entities ... like a System Dimension that is just a flag or indicator or whatever. So all the ETL related attributes that I did have up in Entity, now need to move down into the various subtype tables.That made me kinda sad and feel kinda dirty ... so I posted.Jayto here knows when |
|
|
|
|
|
|
|
|
Subtype/Supertype - Inheritance - Delegation