Many-to-Many Relationships

The final type of logical relationship that you might need to display in a form is the many-to-many relationship, which is represented by three or more tables in the database.

In the vast majority of cases, you can treat many-to-many relationships as though they were one-to-many relationships. For example, Figure 14-7 shows a many-to-many relationship between Customers and Products, with the Orders and Order Details tables acting as the junction tables.

Figure 14-7. Customers and Products have a many-to-many relationship.

It's reasonable to display all the products a customer has ordered (in which case, you would treat the Customers table as the one side of the relationship) or all the customers who have ordered a product (in which case, the Products table is the one side). You can use the same presentation techniques as for any other one-to-many form. You need only address where to include data from the junction tables and how to handle duplicates on the many side.

Most junction tables contain only the primary keys from each side of the many-to-many relationship. As we saw in Chapter 3 and as shown in Figure 14-7, however, the relationship itself sometimes has attributes that are modeled as part of the junction table. If you need to include these attributes in your form, they should be displayed with the many side.

In our example, if the form is to display the products for each customer (the Customers table being the one side), the order date—which is a field in the Orders table—is clearly part of the product information, not the customer information. "Customer X bought product Y on the 15th and product Z on the 18th..." The order date would be part of the customer information if the display were the other way around, with Products being the one side: "Product X was purchased by customer Y on the 15th and customer Z on the 18th...," as in this case the Customers table is treated as the many side.

It's quite likely that there will be duplicates, or at least partial duplicates, on the many side of the display. You must decide whether to display each item individually or to show summary information. For example, if you are listing the customers who purchased each product, you might choose to list the customer once for each product purchased; or you might just list each customer a single time, and show the total number of times the customer ordered the product and the total (and perhaps the average) quantity purchased.

You need to be careful here to avoid listing complete duplicates. It's of no use whatever to simply list a customer name 27 times if you're not providing any additional information, such as the order date. The only reason users might want to see this information is to determine the number of orders a customer placed, and the application should do this arithmetic, not the users.

Although treating a many-to-many relationship as a one-to-many relationship meets most requirements, there are times when you need to display the relationship fully. A product manager reviewing the customers who purchased a specific product might want to know what other products those customers purchased in order to develop "package deals," for example.

One relatively simple solution here is to treat the relationships as hierarchies and use subdatasheets or Flexgrid controls for display. The danger in this technique is that it isn't necessarily clear what the additional information represents. If you insert in a product listing a subdatasheet that lists only customer names, for example, it might not be immediately clear whether these are the names of customers who purchased a product or suppliers who are the source of the product.

If you're concerned about the potential confusion of a hierarchical display, or if the client doesn't commonly require hierarchical information, it might be better to provide the information in a secondary window, where you can make its meaning clear. Using a secondary window also allows you to provide summary information in addition to or in place of the straight details.

A product manager looking at other products bought by a customer might prefer a list of products purchased by all customers who bought the primary product to be organized according to the percentage of overlap—for example, all the customers who bought a widget bought a gizmo, but only 10 percent of these customers bought a doohickey. You can, of course, provide some of this summary information on the main form, but since the calculation is fairly complex (and therefore time-consuming), you are better off displaying it on demand in the secondary window unless it is frequently needed.

The two techniques for displaying all the data in a many-to-many relationship are not mutually exclusive. You can provide a list of customers for each product in a hierarchical display on the main form, and you can also provide a "top ten customers" display in a secondary window that users view on demand. As always, you must base your decisions on how you anticipate the form will be used.