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 14-5, for example, the Customers to Orders to Order Details relationship is a three-level hierarchy.

Figure 14-5. This E/R diagram shows 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 14-2 as a subform 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.

Until recently, about the only viable alternative was to use a tree view control. Tree view 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 tree view inappropriate for our example. Imagine trying to display all the customer details in a single line.

Fortunately, everything got a lot easier with the release of Access 2000 and Visual Basic 6. Both of these development tools provide a mechanism for nesting data shown in a multiple-record format. Access 2000 subdatasheets support the presentation of hierarchical data in an outline-like format, as shown in Figure 14-6.

Figure 14-6. Microsoft Access 2000 subdatasheets display hierarchical data.

Subdatasheets aren't very pretty, but they work. Subdatasheets 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 subdatasheet that included both Addresses and Orders at the same level. The Visual Basic 6 Hierarchical Flexgrid control allows you the same sort of hierarchical display as subdatasheets, except you can nest multiple recordsets at each level.

Unfortunately, data displayed by the Hierarchical Flexgrid is read-only. If users need to edit the data, you must tie it to an additional set of controls, either on the same form or a secondary one. This can obviously lead to awkwardness in the user interface—it's always preferable to allow users to edit data in place.

A read-only display is often acceptable, however. Clients tend to use hierarchical data displays for review and management and to maintain data as part of a separate work process.