Oracle Tips by Burleson

Chapter 2 Physical Entity Design for Oracle

Data relationships and physical design

As we know, five types of data relationships must be considered when converting a logical model into a physical design:

• One-to-one relationships
   

• One-to-many relationships
   

• Many-to-many relationships
   

• Recursive many-to-many relationships
   

• The IS-A relationship (class hierarchies)

The effective database designer's job is to represent these types of relationships in a sensible way and ensure acceptable warehouse performance.

Redundancy and physical design

An Oracle database with either one-to-many or many-to-many relationships must have redundant foreign keys embedded in the tables to establish logical relationships. Redundant foreign keys in the subordinate tables create the data relationships, making it possible to join tables together and relate the contents of the data items in the tables.  While third normal form designs were very popular in the 1980s when disk was very expensive, today’s database designers deliberately introduce redundancy into the physical model to improve performance.

As the size of the database increases, redundancy can become a major problem. Today, many users create very large databases, many of which contain trillions of bytes. For databases of this size, a single table can contain more than a billion rows, and the introduction of a single new column to a table can represent thousands of dollars in additional disk expense. Data redundancy is detrimental for two reasons. First and foremost, duplicating the redundant material consumes disk storage space. The second and most ominous reason is that updating redundant data requires extra processing. Redundant duplication of very large and highly volatile data items can cause huge processing bottlenecks.

However, this does not imply that redundancy is always undesirable. Performance is still an overriding factor in most systems. Proper control of redundant information implies that redundant information may be introduced into any structure as long as the performance improvements outweigh the additional disk costs and update problems.

Since the first publication of Dr. E. F. Codd’s 1993 research paper Providing OLAP (Online Analytical Processing) To User-Analysts: An IT Mandate, database designers have attempted to find an optimum way of structuring tables for low data redundancy. Codd’s rules of normalization guide designers to create logically correct table structures with no redundancy, but performance rules often dictate the introduction of duplicated data to improve performance.

This is especially true for distributed Oracle databases. Any node in a distributed database might want to browse a list of customers at other nodes without establishing a connection to that node. The technological problems inherent in the two-phase commit necessitate widespread replication of entire tables or selected columns from tables. However, the distributed database designer does not have free reign to introduce redundancy anywhere in the enterprise. Redundancy always carries a price, whether it is the cost of the disk storage or the cost of maintaining a parallel update scheme. Figure 2.1 shows a strategy for analyzing the consequences of data redundancy.

Figure 2.1 – The data redundancy boundary  

In Figure 2.1, a boundary line lies within a range between the size of a redundant data item and the update frequency of the data item. The size of the data item relates to the disk costs associated with storing the item, and the update frequency is associated with the cost of keeping the redundant data current, whether by replication techniques or by materialized views.