Why do we have so much trouble with hierarchies? I was in a training session for an e-Commerce package I’ll need to support starting in a few months. There was a question raised about a product being sold in the US and in Canada with different prices in each. The solution proposed was to make a copy of the product and put it in the Canada hierarchy with a separate price. Now when the product get’s renamed in a few months we’ve got multiple instances where we’d need to make that change and maintenance overhead in just tracking where each of the instances is. All because we use hierarchical models where a relational model is needed.

I’m sure everyone has examples like this. I started thinking about why this is so hard for people. I only started getting my head around relational models about five or six years ago, yet I’ve known about outlines and hierarchical classification systems since I was about 8. My conclusion is that we do a bad job of teaching relational thinking, while we do a good job of teaching how to organize information into hierarchies.

Teaching relational modeling can best be done with pictures and sentences. Consider the following:

Taken whole, at first glance, this looks like a mess of spaghetti. However, each relationship can be separated from the group, redrawn, and have a sentence attached:

The relational model shown here is of a higher logical order than a hierarchy in that the relational model can include hierarchies, but the converse is not true. The first part of this statement is seen in the Pet- [Fish, Cat, Hamster] one-to-many, type/subtype relationships. A hierarchy, however, can never handle a many-to-many relationship without cheating or duplicating in a non-normalized way.

So what would be at the next logical order level up from the relational model? First is a kind of meta-relational model where entities can participate in multiple relationships with eachother, constrained by context . In UML we can also define Association Classes that can represent many-to-many-to-many…(and so on) joins.