Relationship Optionality

Relationship cardinality answers a fairly simple question: Given an entity A, how many instances, at most, of another entity B might potentially be linked to a given instance of A? The answer could be "zero" (in which case there's no relationship between the entities), but in general the answer is either "one" or "many" (in other words, more than one).

It can be useful to know one additional piece of information about a relationship. This is what's called the relationship's optionality. This information is not strictly necessary for a complete ERD, but it can be very useful information to gather.

Cardinality allows you to answer the question "How many?" What is the maximum number of orders with which a customer may be linked? One, or many? Optionality, by contrast, answers the question "How few?" What is the minimum number of orders with which a customer may be linked and still be considered a valid customer? Is it permissible to have a customer with no recorded orders? Answering these questions often reveals important information about business rules and workflow in the intended system. The answers probably won't mean designing your data structures any differently, but could be quite important when it comes to data validation and workflow in the finished system.

Optionality in Many-to-Many Relationships

Suppose that you have a database system designed to track information about college students (including their high school transcripts and grades from other schools, sports, student organizations, and classes). Two of the entities in this system are a Student entity (of course) and a Class entity. The relationship between these two entities is many-to-many. So you know that one student record can potentially be linked to many class records, if a student is enrolled in many classes. But is there a minimum number of classes that a student must be associated with at any time? Put differently, should it be permissible to have a student record in the system that's not associated with any class records?

Your first instinct might be to say no. After all, students have to take at least one class, don't they? But that's not quite the question that's being asked. The question is not whether all student records eventually have to be associated with at least one class record. Presumably they do. The question is, must a student record, always and at all times in its existence, be associated with at least one class record? And the answer to this question is clearly no. New students, or transfers, are not associated with class records until they first enroll for classes. But their records might be entered into the system weeks or even months prior to enrollment. So the answer here is that it's acceptable for student records to have no associated classes.

Here's how to show this rule in the ERD notation. Take a look at Figure 5.8 and notice that we've added some adornment to the Class end of the Student-Class relationship. In addition to the crow's-foot, which shows the fact that, potentially, multiple class records can be associated with a single student, we now also have an open circle to indicate that it's all right for a student to have no associated class records.

Figure 5.8. Entity-relationship notation for the Student-Class relationship, with optionality shown at the Class side.

In an ERD in which you fully diagram all the optionalities, each end of a relationship line has two notations: one to show the smallest number of records that have to exist in the related entity (the optionality) and the other to show the largest number that can exist (the cardinality). The graphical notation closest to the entity specifies the cardinality, and the one farther away specifies the optionality. So the way to read the notations at the Class end of the diagram in Figure 5.8 is something like this: "One student record may be associated with as few as zero class records or with many class records."

Now consider the other end of the relationship, the Student side. What's the fewest number of students with which a class may be associated? Well, before anyone enrolls for the class, the answer is zero. And what's the largest number of students with which the class may be associated? It doesn't matter whether the answer is 10 or 100. As long as the answer is more than 1, you can just use the generic term "many" again. The Student end of this relationship is drawn as shown in Figure 5.9.

Figure 5.9. Entity-relationship notation for the Student-Class relationship, with optionality shown at both sides.

This diagram now provides a bit more information than a plain, unadorned ERD would have. Now you've specified not only that the relationship of Class to Student is many-to-many, but also that it's permissible to have classes with no associated students, and students with no associated classes.

This set of questions about optionality applies equally to all relationship types. But each relationship type has some optionality scenarios that are, for lack of a better term, more typical of that relationship type. In the sections that follow we'll examine some of these typical scenarios. Nothing in the sections that follow, though, should suggest that a given relationship type will never exhibit other types of optionality.

Optionality in One-to-Many Relationships

In dealing with one-to-many (and, by extension, with many-to-one) relationships, there are two broad scenarios, which can be called "loose binding" and "tight binding."

Optionality in One-to-Many Relationships: Loose Binding

Consider the relationship between the entities Customer and Sale. It seems to be one-to-many: One customer may have many sales. That's the cardinality. What about the optionality? Take it one side at a time. Is it permissible to have a customer with no associated sale records? This is a business rules question; in many business scenarios, it seems likely that this would be all right. Until people actually buy something, they're better described as prospects than customers, but we probably still want to allow them in the database without a sale. So a customer can have anywhere from zero to "many" sale records.

Now look from the other side. Is it permissible for a sale record not to be associated with any customer records? This, again, is a business rules question, and will need to be determined based on the system's intended use. If the answer is no, a sale must be associated with at least one customer. Zero customers on a sale would not be permitted.

Figure 5.10 shows this relationship with all the optionalities drawn in on both sides. This optionality pattern is very typical of one-to-many or many-to-one relationships: The "many" side may range from zero to many associated items, whereas on the "one" side each child record must have exactly one parent, no more, no less. The double lines on the Customer side indicate the cardinality and optionality of a Customer seen from the perspective of a Sale: Each Sale must have a minimum of one Customer, and a maximum of one Customer. Put more succinctly, a sale is associated with one and only one customer.

Figure 5.10. Entity-relationship notation for the Customer-Sale relationship. This is a very typical optionality pattern for one-to-many and many-to-one relationships.

We call this optionality configuration a "loose binding" because it's permissible to have Customer records with no associated Sales. A given customer may have one or more sale recordsor then again, she may not.

Optionality in One-to-Many Relationships: Tight Binding

Consider another common business model: the model for an order of some kind. Each order can contain requests for multiple kinds of goods. You would put each request on its own order line: five kumquats on the first line, three bass lures on the second, and so on. Each order can have as many order lines as it needs in order to list everything that was ordered.

So, clearly, you have a one-to-many relationship from order to order line. If you look at the "one" side first, you'll see that, as with other one-to-many relationships you've seen, the "one" side is pretty hard and fast: Each order line must be tied to one and only one order. On the other side, we know that an order can possibly contain many order items. But what's the fewest items an order may contain and still be considered a valid order? Should it be permissible to leave an order sitting there with no items on it?

This, as is generally the case with optionality questions, may end up being a question about business rules that a database designer may not be able to decide on his own without conferring with someone involved on the business side of the process being modeled. Let's assume that you learn that it should not be permissible to create an order with no associated order items. Every order has to be an order for something. You can't leave it blank. So an order needs a minimum of one associated order line, and the ERD with optionalities will look as shown in Figure 5.11.

Figure 5.11. Entity-relationship notation for the OrderOrder Line relationship. This shows a parent entity that must always have at least one child.

Note

This kind of tight binding between a parent and a child entity is not as common as the looser type of one-to-many relationship, in which it's permissible for the parent to be childless, so to speak. But it does happen, so you should be familiar with it.

Optionality in One-to-One Relationships

Optionality is a concept that's easily learned by example, so let's look at a few more examples. Look again at the earlier example of a legitimate one-to-one relationship. The scenario that was previously discussed included spacecraft that would have an associated Decommission record created at the end of their lives. So a Craft spends most of its time without an associated Decommission record. As a result, the minimum number of Decommission records associated with a craft is zero: It's fine to have a Craft with no associated Decommission record. That's just an active Craft!

From the other side, it's not logical to have a Decommission record that doesn't relate back to some Craft record. Having a Decommission record that stood alone would be meaningless. So your optionalities for this relationship appear as shown in Figure 5.12. The optionalities tell a lot in this case. With the optionalities added to the ERD, you can easily tell which of these two is the "strong" entity, and which is the "weak" or optional one. This diagram reveals clearly that there will always be a Craft record, and there will sometimes be an associated Decommission record.

Figure 5.12. Entity-relationship notation for the Craft-Decommission relationship. This is a typical optionality pattern for one-to-one relationships.

It's worth noting that this specific optionality pattern is the one that's most likely to lead to preserving two separate entities in a one-to-one relationship. When one of the two entities is optional, and loosely coupled to the other, it is often the most compelling argument for keeping the loosely coupled entity distinct.