Ternary Relationships

Ternary relationships are usually in the form X does Y to Z, and like many-to-many relationships they can't be directly modeled in a relational database. Unlike many-to-many relationships, however, there isn't a single recipe for modeling them.

In Figure 3-15, we can see that the Mozzarella di Giovanni purchased by Vins et alcools Chevalier is supplied by both Formaggi Fortini s.r.l. and Forêts d'érables, but there is no way to determine which of them supplied the specific cheese that was shipped to Vins et alcools Chevalier. A ternary relationship has been lost in the data model. Suppliers don't merely supply products, they supply products that are purchased by specific customers.

Figure 3-15. These relations don't indicate which supplier's cheese was purchased by Vins et alcools Chevalier.

To understand the problem, it's useful to first examine the relationships in a more typical problem space, as shown in Figure 3-16.

Figure 3-16. This is the typical relationship chain for the entities participating in an order.

In this diagram, each product is provided by only a single supplier and the ternary relationship is maintained—if you know the product, you know who supplied it. In Figure 3-17, however, each product is provided by multiple suppliers and the ternary relationship has been lost.

Figure 3-17. The ternary relationship has been lost in this model.

The key to resolving the problem is to examine the direction of the one-to-many relationships. Given any entity on the many side, you can determine the corresponding entity on the one side. Thus, given a specific OrderDetails item in Figure 3-16 you can determine the Orders item to which it belongs, and knowing the Orders item you can determine the Customers. The process works in the other direction as well, of course: knowing the OrderDetails item, you can determine the product and then the supplier.

But the reverse is not true. Having identified an entity on the one side of the relationship, you can't select a single entity on the many side. This is the problem with Figure 3-17. Knowing an OrderDetails item, you can determine the product, but knowing the product, you can't determine to which SupplierProducts entity it's linked.

An easy way to think of this is that you can't change directions from one-to-many to many-to-one more than once in a relationship chain. The chain in Figure 3-16 changes directions only once, at OrderDetails. The chain in Figure 3-17 changes directions twice, at OrderDetails and again at SupplierProducts.

The solution is to eliminate the Products entity from the chain, as shown in Figure 3-18.

Figure 3-18. This model preserves the ternary relationship.

The chain now changes direction only once, at OrderDetails, and the relationship has been maintained. Notice, however, that the Products entity hasn't been eliminated. Chances are good that orders will still be placed for Products rather than for SupplierProducts, and maintaining the Products entity allows the user interface to accommodate this.

Of course, you might not care about the ternary relationships in your problem space, or there may be some other way of tracing the relationship when necessary, such as a lot number on the packaging. In this case, there is absolutely no need to model it. It's important to understand that the model in Figure 3-18 isn't intrinsically better or more correct than that in Figure 3-17. You must choose the model that best reflects the semantics of your problem space.