Relationships of Known Cardinality

Occasionally the minimum, absolute, or maximum number of tuples on the many side of a one-to-many relationship will be known in advance. There are five periods in the school day, 200 bones in the adult skeleton, and a golfer is allowed to carry only 14 clubs during tournament play.

It's always tempting to model this situation by including the candidate key of each tuple as an attribute in the relation on the one side, as shown in Figure 3-19, but there are two major problems with this approach. First, it's a repeating group, and second, it's unreliable.

Figure 3-19. It's tempting, but unwise, to model known cardinality like this.

The repeating nature of the attributes is disguised in Figure 3-19 by the attribute names, but they're all defined on the same domain, ClassPeriod. Whenever you have multiple attributes defined on the same domain, chances are good that you have category or type values masquerading as attribute names.

In addition to this theoretical problem, structures like this are unreliable. It might very well be company policy, for example, that managers have a maximum of five employees reporting directly to them, but policy isn't reality. By embedding the policy in the data model, you're implementing it as a non-negotiable system constraint. I'd be willing to bet that you'll discover during initial data entry that at least one manager has six direct reports. What happens then? Will somebody suddenly have a new boss? Will the manager get entered (and maybe paid) twice? Or will the programmer who got a support call at 3 A.M. say some very uncharitable things about you?

Limitations on cardinality such as these must be implemented as system constraints; they shouldn't be embedded in the structure of the relations themselves. Furthermore, as we'll discuss in Chapter 16, you should think long and hard about the impact on usability before implementing limitations on cardinality at all.