Oracle Tips by Burleson

Chapter 2 Physical Entity Design for Oracle

Because the relative costs are different for each hardware configuration and for each application, this boundary may be quite different depending on the type of application. The rapid decrease in the disk storage costs designates that the size boundary is only important for very large-scale redundancy. A large, frequently changing item is not a good candidate for redundancy. But large static items or small, frequently changing items are acceptable for redundancy. Small static items (e.g., gender) represent ideal candidates for redundant duplication.

As we have noted, Oracle provides a wealth of options for modeling data relationships and we must understand the ramifications of each option.  Let’s begin with a review of one-to-many data relationships.

The Dangers of over-normalization

When creating an E/R model, it is often tempting to look at the data model from a purely logical perspective without any regard for the physical implementation, specifically the overhead from SQL table joins.

The savvy database designer strives to identify all of the logical relationships in the data model, but does not fall into the trap of over-normalization.  For example, a common mistake is “pseudo” relationships.  A pseudo-relationship is when the one-to-many relationship actually exists, but the application has no need to reference this relationship. To illustrate, consider the E/R model below (Figure 2.2).

Figure 2.2 – Inappropriate data relationships 

From a purely theoretical perspective, this is a correct 3NF data representation.

• A many-to-many data relationship exists between the HAIR_COLOR and STUDENT entities. Many students have blonde hair, and blonde hair is common to many students. Hence, we create a many-to-many relationship between these entities.
   

• A one-to-many relationship (or possibly a many-to-many relationship) exists between CITY and ZIP_CODE.  Each city has many zip codes, and a zip code is for one, and only one, city.

So, does this logical model imply the physical creation of a zip_code and hair_color table? The solution depends upon whether any other non-key data items exist within the hair_color entity.

Remember, the overhead of a relational database is the requirement that actual column values be repeated to establish the data relationship. Hence, if many other data items relating to hair color are required, 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.

Another example is the zip_code attribute in the student entity. At first glance, it appears that a violation of Third Normal Form (that is, a transitive dependency) has occurred between city and zip_code. In other words, it appears that a zip_code is paired with the city of residence for the student. If each city has many zip_codes, while each zip_code refers only to one city, it makes sense to model this as a one-to-many data relationship, and theory demands creating a separate entity called ZIP. However, this is another case where the ZIP entity lacks key attributes, making it impractical to create the ZIP entity. In other words, zip_code has no associated data items. Creating a database table with only one data column would be nonsense.