Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design Introducing Redundancy Into An Entity/Relation Model
As we know, five types of data relationships must be considered when designing any Oracle database:

* One-to-one relationship

* One-to-many relationship

* Many-to-many relationship

* Recursive many-to-many relationship

* The IS-A relationship (class hierarchies)

The effective data warehouse designer's role is to represent these types of relationships in a sensible way and ensure acceptable warehouse performance. In a hierarchical or CODASYL (Network) database, it is possible to define and implement a database design that contains absolutely no redundant information (such as pure third normal form). Hierarchical and network databases can be truly free of redundant information because all data relationships are represented through pointers and not through duplicated foreign keys. Because object-oriented systems use pointers to establish data relationships, many object-oriented systems can also be designed totally free of redundant data.

The complete elimination of redundancy requires embedded pointers to establish the data relationships. Therefore, no relational database can ever be totally free of redundant data because they use redundant foreign keys to establish SQL JOIN columns for one-to-many data relationships. In a sense, the requirement for non-redundant models absolves the theoretical purist perspective which states that normalization is the only way to design relational systems. 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 duplication of foreign keys in the subordinate tables creates the data relationships, making it possible to join tables together and relate the contents of the data items in the tables.

---

This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm