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 17-8 shows a many-to-many relationship between Customers and Products, with the Orders and Order Details tables acting as the junction tables.

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 17-8, 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 datewhich is a field in the Orders tableis 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.

Fortunately, this type of analysis is most commonly performed against a dimensional database and, as we've seen, dimensional databases interface tools, such as Microsoft's Data Analyzer, are specifically designed to handle this situation. But the requirement is perfectly valid as part of an administrative function in a production database.

One relatively simple solution in this case is to treat the relationships as hierarchies and use sub-datasheets or DataGrid controls for display. The danger in this technique is that it isn't necessarily clear what the additional information represents. If you insert a sub-datasheet that lists only customer names into a product listing, 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 overlapfor 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.