Using A Hierarchy Bridge WIthout A Many-to-many Relationship

In this post, I describe what you can do to eliminate the many-to-many relationship when using a hierarchy bridge.

The Hierarchy Bridge

A recent post on Recursive Hierarchies and Bridge Tables described how to support a recursive hierarchy in a dimensional model.

The business benefits of this kind of solution can be extremely valuable.

In the example, a special bridge table was used to navigate the relationships between companies and their subsidiaries. When connected to a fact table, this allowed us to roll up transactions via the recursive hierarchy.

Here is what the solution looked like.  Click the picture for a larger version.  For full details, refer to the original post.

Many-to-many is legal, but you may need to resolve it anyway.

You be wondering about the many-to-many relationship -- the one between the fact table and the bridge table.

This kind of relationship is perfectly valid in a relational database.  Unfortunately, some of our software products may not accept this kind of relationship, making it necessary to eliminate it.

I've written about this before in the context of a multi-valued dimension.  The solution in that case was to resolve the many-to-many relationship by creating a single-column table that consisted solely of a surrogate key.

With a hierarchy bridge, we can resolve the many-to-many without adding one of those silly single-column tables.

Additional table not required

In the recursive hierarchy above, we already have what we need: the company table itself.

Notice that in the picture above, the company table represents the "superior" to which we are aggregating transactions. The bridge links it to facts that describe the activities of subordinates.

To resolve the many-to-many, we will insert a second alias for the company table between the fact table and the bridge.  This alias represents the subordinate.

Now the picture looks like this:

An added benefit of this approach is that we can include information about both the superior and the subordinates in our query results.

Learn more

There are several posts describing bridge tables in this blog.  This post has mentioned two of them:

• Recursive Hierarchies and Bridge Tables (May 2, 2012) introduced the recursive hierarchy bridge, including the many-to-many that is eliminated in this post.

• Bridge Tables And Many-to-many Relationships (April 25, 2011) described how to eliminate many-to-many relationships involving a different kind of bridge table -- one that handles multi-valued dimensions. 

• Use the category keyword bridges to find more posts.

And of course, you can help support this blog by picking up a copy of Star Schema: The Complete Reference.  It devotes two chapters and 30 illustrations to the topic of bridge tables.

Bridge photo by Branwebs via Creative Commons