De-normalizing 3NF Relationships

When creating an E/R model, it is often tempting to look at the data model from a purely logical perspective, disregarding the physical implications of the model. The designer strives to recognize and establish all of the logical relationships in the model while sometimes finding that the relationships are misleading. A relationship can be misleading when the relationship actually exists, but the application may have no need to reference this relationship. Consider the E/R model for a university shown in Figure 4.5.

Figure 4.5  An example of unnecessary data relationships.

Consider the association of the hair_color attribute to the student entity. Does a many-to-many data relationship really exist between hair_color and student? Let's think about this for a minute. Many students have blonde hair, and blonde hair is common to many students. Why not create a many-to-many relationship between student and hair_color? The solution depends on whether any other non-key data items exist within the hair_color entity.

If many other data items relating to hair color are present, then it is perfectly appropriate to create another entity called hair_color. But in this case, even though a many-to-many relationship exists between hair_color and student, hair_color is a standalone data attribute, so it is unnecessary to create an additional data structure.

This is an excerpt from "High Performance Data Warehousing", copyright 1997.

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.