I must have read Terry’s book, for one reason or another, 10 or so times by now. As is the case, I suppose, with any comprehensive text, on each subsequent reading I usually come away with some nuance I hadn’t caught before.

So, I suppose I shouldn’t have been too surprised when, upon reviewing materials for an upcoming ORM class I was teaching (read: I was seeing how Terry presented a concept so I could steal it for my lecture), I came across this:

"Just as internal uniqueness constraints can be generalized to internal frequency constraints, external uniqueness constraints can be generalized to external frequency constraints. However, such constraints are rarely encountered in practice." [Halpin, Information Modeling and Relational Databases, Section 7.2, Page 279]

Recall a previous discussion, in which I mentioned that a frequency constraint of <= 1 on a given role (or role sequence) was essentially a uniqueness constraint on that role (or role sequence). For example, in the following figure, the uniqueness constraint could be implemented by a frequency constraint of <= 1, but the uniqueness constraint version is preferred because of the importance and (if you’ll forgive the pun) frequency of the uniqueness constraint in ORM schemas.

For simplicity, I have omitted any mandatory constraints. However, note that a frequency of (exactly) 1 – as opposed to <= 1 – would be equivalent to the combination of a mandatory constraint and a uniqueness constraint on the role.

Now, let’s generalize an external uniqueness constraint in the same manner. The external frequency constraint below says that each combination of Office Number and Floor can occur at most 1 time for all Office instances (and this is also the definition of the external uniqueness constraint below). Again, the external uniqueness constraint version is preferred.

I hadn’t really thought much about external frequency constraints until now because, as Terry mentioned, they are rare. So rare, in fact, that I struggled to even come up with an example. The only examples I could come up with would be better expressed in a different manner -- namely by using co-referencing. For example, consider the following two schemas:

In this Universe of Discourse (UoD) we are assigning offices to people. Because companies appear to hate productive employees, they usually stuff them into cubes. However, in our example, offices (coveted structures with closing doors and walls that go all the way to the ceiling) can be assigned to employees with a catch: in keeping with our hatred-of-productivity business rule, up to two employees might share an office.

Notice that the first example effectively bypasses the use of a co-referenced object type but that the concept is still there (note that "office" is even used in both predicate names). The mandatory constraints on the Office roles of the latter schema are implemented as an equality constraint in the former schema. And the (internal) frequency constraint on the Office role in the former schema is implemented as an external frequency constraint across the Office Number and Floor roles in the latter schema.

All we are doing is unpacking the reference mode of Office (into it’s two component facts) and attaching that reference mode directly to any object in a functional role with Office (in this case, Person). Thus, this is really a schema transformation (and this is precisely what the RMap algorithm does when mapping co-referenced objects into relational tables).

The only time I can think of when the external frequency constraint version is preferred is when you are trying to better control the logical names that get generated in Visio (Visio usually inserts the co-referenced object name in the logical column names -- whether you want it there or not). Otherwise, if you ever come up with a schema that is using an external frequency constraint, I bet your model would be more clear if you introduced a co-referenced object type (or perhaps a nested predicate) as discussed here.