Hierarchies

Any one-to-many relationship is technically a hierarchy, but the term is usually restricted to relationships consisting of three or more recordsets: a one-to-many-to-many relationship, if you will. Hierarchical relationships occur frequently in data models, but they don't often need to be represented in forms.

In Figure 17-6, for example, the Customers to Orders to Order Details relationship is a three-level hierarchy.

With this kind of structure, most applications would have a Customers form that, if it displays order information at all, shows it in a summary format. A separate Orders form would display the Orders and Order Details items. The Orders form would reference the Customers entity, of course, but would display the one-to-many relationship between Orders and Order Details only. Not many applications require a form that displays the details from all three tables.

However, it's difficult to know whether this tendency to avoid displaying hierarchical relationships is due to a lack of real need or to the difficulty of displaying them sensibly. Suppose, for example, that the client has requested the ability to review orders using the customer maintenance form. It would be easy to provide this functionality by displaying the records from the Orders table (the middle level) one at a time and the Order Details records all at once. Essentially, this would mean embedding the form shown in Figure 17-3 as a sub-form on the Customers maintenance form.

A one-at-a-time view of Orders records isn't really appropriate in these situations, however. Users are more likely to be asking, "How many orders has this customer placed?" or "What was their average order total?" than they are to be asking about specific products. Answering these common questions would be tedious if you represent the Orders records one at a time.

To avoid this problem, you might decide to display only summary order information on the Customers form and provide a mechanism for opening a subsidiary Orders form if users require more details. Unfortunately, this "summary with details" approach has some serious drawbacks as well.

If users want to review order details to determine what products the customer orders most often, they must endure the overhead of opening a second form. Since the subsidiary Orders form usually displays an order in single form view, it's also difficult to compare the products ordered on multiple occasions.

One option is to use a TreeView control. TreeView controls are wonderful tools, but they are limited in the amount of data they can show at each level of the hierarchy, since all the data must appear on a single line. This restriction would make a TreeView inappropriate for our example. Imagine trying to display all the customer details in a single line.

Microsoft Access 2000 introduced the sub-datasheet to support the presentation of hierarchical data in an outline-like format, as shown in Figure 17-7.

Sub-datasheets aren't very pretty, but they work. They can be nested up to eight levels deep, but you can nest only a single recordset at each level. You couldn't, for example, create a sub-datasheet that included both Addresses and Orders at the same level.

Visual Studio .NET supports a DataGrid control that provides similar functionality, but somewhat awkwardlyalthough it maintains the hierarchical relationship between DataSets, it only displays one at a time. In our example, once the user displayed data from the Order table, the Customer data would disappear, and they would need to use a Back button to display it.