Oracle Tips by Burleson

Chapter 2 Physical Entity Design for Oracle

So, how could we de-normalize such a relationship?  The answer is that we could join all three tables together and create a single, highly-redundant table.  The advantage to this denormalization would be that all student, course and grade information would be available in a single disk I/O, but the downside would be the increase volume of update overhead for DML statements.  In practice, this type of many-to-many relationship would be ideal for an Oracle Materialized View.

Next, let’s examine recursive many-to-many relationships.

Recursive Data Relationships

Recursive many-to-many relationships contain an object that also has a many-to-many relationship with other occurrences of the same object. These relationships are often termed Bill-of-Materials (BOM) relationships, and the graphical representation of the recursive relationship is sometimes termed a Bill-of-Materials explosion. These relationships are termed recursive because a single query makes many sub-passes through the tables to arrive at the solution (see Figure 2.6).

Figure 2.6 – Examples of a recursive many-to-many relationship

Bill-of-materials relationships denote an object with a many-to-many relationship with another object in the same class. In other words, a part may consist of other parts, but at the same time, it is a component in a larger assembly. For example, a class at a university may have many prerequisites, but at the same time, it is a prerequisite for another class. Figure 2.7 depicts a course-prerequisite hierarchy for a university. Note that the IS-A prerequisite relationships are relatively straightforward, indicating which courses are required before taking another course. For example, the prerequisites for Linear Equations 445 are Business 400, Accounting 305, and Multivariate Statistics 450. These courses all have prerequisites of their own, which may also have prerequisites, and so on.

Figure 2.7 – Expanding a recursive many-to-many relationship

Each occurrence of a COURSE object has different topics, and a complete implementation must iterate through all courses until reaching terminus, the point where the course has no further prerequisites.

Unfortunately, the recursive many-to-many relationship is very confusing and almost impossible to understand without the aid of a graphical representation. Visualize the recursive many-to-many relationship as an ordinary many-to-many relationship with the owner entity “pulled apart” into owner1 and owner2 (Figure 2.8).

Figure 2.8 – A junction table establishes the relationship

There are many real-world examples of recursive many-to-many data relationships (Figure 2.9).  For example, parts and components are recursive.  Each part has many parts, and at the same time, a part many be a sub-part of a larger part.

Figure 2.9 – Examples of recursive relationships

With an understanding of the nature of recursive relationships, the question becomes one of implementation: What is the best way to represent a recursive relationship in Oracle and navigate the structure?

The following Oracle table definitions describe the tables for the part-component example:

CREATE TABLE PART(
part_nbr     number,
part_name    varchar2(10),
part_desc    varchar2(10),