||Oracle Tips by Burleson
Chapter 2 Physical Entity Design for
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
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
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
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
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
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:
||Need an Oracle Health Check?
Does your boss blame you for an Oracle performance problem?
Need to prove that your database is properly optimized?
BC Oracle performance guru's can quickly verify every aspect of your
Oracle database and provide a complete certification that your database
is fully optimized.