One-to-Many Relationships

Many forms need to display entities that are linked in a one-to-many relationship. Determining the best layout for these forms is easy, as long as you remember that the form needs to display a one-to-many relationship, not a many-to-one relationship. When you're modeling complex relationships, it's often easier to think in terms of records rather than instances of entities. So in this case, you must be certain that the record on the one side controls the display of records on the many side, and not the other way around. Try to control the one-side record from the many-side record, and you'll tie yourself (and the system, and your users) in knots.

This sounds obvious, at least to me, but it's a fairly common mistake. I made a whole bunch of money years ago fixing all the many-to-one forms that had been designed by a woman with the title of "Senior Interface Designer" at a major Australian bank. It was the first time I gave any credence to the theory that "real programmers don't work for banks."

Having established that the record on the one side controls the form display, you need to decide how the records on the many side are to be displayed. You have two choices: You can display them all at once or one at a time.

Your choice is largely determined by the amount of detail users need to see for each many-side record. If you need to show only a few fields, you can usually display them all at once. The form shown in Figure 17-3, from the Northwind sample database, uses a sub-form to display four fields for each record on the many side.

The vertical scroll bar on the sub-form indicates that not all the records are visible at any one time. For design purposes, this still counts as "all at once." In the next chapter, we'll look at some other ways to display multiple records.

I like the "all at once" form layout because it provides the most context for users. You can always provide a mechanism for displaying additional details on a subsidiary form if you have real estate problems. But this style of layout isn't always appropriate or possible, and you might need to display the many-side records one at a time. Here, there are two issues you must address: making it clear to users that there are multiple records, and providing a mechanism for users to move between records.

The form in Figure 17-4 is identical to the one in Figure 17-3, except that the sub-form is shown in single record view rather than as a continuous form.

The "video button" record selectors at the bottom of the sub-form are the default record navigation mechanism provided by Microsoft Access. As a rule, I stick to defaults wherever possible, but look at the result here: Two record selectors are stacked on top of each other, with only their position to indicate which record selector is for the recordset in the sub-form and which is for the primary recordset. This isn't the end of the world, but it's not an optimal solution.

You can sometimes remove the record selectors from the primary form. This could be feasible for the form in Figure 17-4 if you provided a good interface for finding product categories. Users aren't all that likely to page through a maintenance form such as this one anyway, and a good find facility would almost certainly meet their needs.

Alternatively, you could replace one of the record selectors with some other movement mechanismcommand buttons or a custom scroll bar, for example. However, using a different technique for moving through records can be difficult to handle because you're introducing inconsistency into the interface. It can be done, though, if you're careful about choosing a paradigm and sticking with it.

For example, you could decide to use something like the Forward and Back toolbar buttons in Microsoft Internet Explorer to control movement between records on the one side, and use the video button record selectors for moving between records on the many side. This will work as long as you use this paradigm consistently within the application. Thus even forms that represent simple entities, and therefore don't have many-side records, should use the Forward and Back buttons.

Occasionally, a form will display a primary table that has multiple one-to-many relationships, and you will need to display more than one of the tables on the many side of the relationships.

If you take my advice, you'll avoid these situations wherever possible. Multiple many-side tables on a form can be messy to design and even messier to implement, but sometimes you have no choice. If you must display multiple one-to-many relationships on a single form, the easiest solution is to use a tab control to separate the many-side displays. This solution provides a clear context for users. It can also improve performance, since the records for the tab control only need to be loaded when the control is displayed.

What you should try most earnestly to avoid is having a whole bunch of multi-record controls simultaneously visible. They would be slow and ugly, and their appearance on a single form implies a relationship between the entities on the many side that probably doesn't exist. In Figure 17-5, for example, it's unclear whether the listed phone numbers pertain to the company or the contacts.