Malcolm Chisholm has written an article for DM Review entitled "Is Data Modeling Sufficient for Database Design?" However, I think a better title would have been "Working with Hidden Subtypes", as his comments on Codes Tables, Indicators, Nulls, and their dependence on other attributes really do focus on the complexities involved in making good data modeling style decision.
By now you get the picture. Certain attributes are only populated under certain circumstances. Traditionally, we have gotten around this need by creating subtypes in data models, thus spawning additional entities. Yet, in the above example we have four sets of supertypes and subtypes (Product Development Status, Product Category, Finish Type, and another for Finish Types of Gold and Oil Rubbed Bronze). It would be an absolute nightmare to try to model such a situation using subtyping, and anyone who did would certainly face a rebellion from the people charged with implementing it. Furthermore, traditional data modeling only permits one supertype and one set of subtypes per entity. It cannot deal with the various independent, overlapping and hierarchical natures of the subtypes we find even in the relatively simple situation shown in Figure 1. The only solution is to use code tables and indicators to deal with the problem in the context of a single Product entity.
Such a solution misses out on design. On the face of it, the Product entity on Figure 1 appears to say that every record in the physically implemented table will be uniform, in accordance with third normal form. In reality there are a number of subtypes hidden within the Product entity. These hidden subtypes are never formally identified, named or defined, let alone managed.
I'm not sure I agree that traditional data model allows only one set of subtypes per entity. However, I have seen the rebellion he mentions if subtyping is used in the physical model. Rebellion is a nice term for "revolt" in this case.
I use subtypes, but I have sometimes been forced to work with models that are overly subtyped to the point the models no longer provide the benefits that they are supposed to deliver. My classic presentation story about this is a US Department of Defense data model I worked on that had some subtyping that went more than 25 levels deep. It even said that a PERSON is a subtype of an AGREEMENT. I don't know which world that inheritance existed in, but it did not reflect how I understood my world, nor did it reflect how most of our military users saw their world, either. Sure, this was during the Reagan years and it was a project for the Strategic Defense Initiative, but still.....
Anyway, Chisholm has done a great job of showing how subtypes exist in our models whether or not we've chosen to use the subtype structure to represent them. In fact, by not subtyping, we've chosen to hide them and move the subtyping rules to someplace outside our models.