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 actually no relationship), 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? Is it permissible to have a customer with no recorded orders? Answering such questions about the data can provide considerable additional precision to your model.

Optionality in Many-to-Many Relationships

Suppose 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, is it 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 where 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? There's no maximum (that we know of), so 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.

Optionality in One-to-Many Relationships

When 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? It seems likely that this would be all right. Until they actually buy something, they're more of 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 to be missing an associated customer? No, this seems unlikely to be allowable . You'd want to tie each sale to a customer. So it seems best to say that a sale must be associated with at least one customer. Zero customers on a sale is not permitted.

What's the largest number of customers to which a sale can be related? The answer again is "one." If a sale could be associated with more than one customer, then the relationship of Sale and Customer would be many to many, rather than the many to one that we've already settled on.

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 records ”then again, she may not.

Optionality in One-to-Many Relationships: Tight Binding

There is another common business model: the model for an order of some kind. Each order can contain requests for multiple different kinds of goods. You would put each different 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 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? Should it be permissible to leave an order sitting there with no items on it?

This 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 that's being modeled . But 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 like Figure 5.11.

Figure 5.11. Entity-relationship notation for the Order/Order 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 it's worth being 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. 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. And a Craft may have at most one Decommission record.

From the other side, it's not possible to have a Decommission record without an associated Craft. Having a Decommission record that stood alone would be meaningless. At the same time, a Decommission record can't be associated with more than one Craft; by definition, one decommissioning applies to only one craft. So your optionalities for this relationship appear as 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 "weaker" 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.

Optionality Recap

Not everyone uses optionality in ER diagrams, but we think it's a good habit to cultivate. After you do it for a while you might start to omit it except in cases where the optionality is a little different from what you might expect. Most one-to-many relationships, for example, are of the "loose binding" type, where the children are optional. With sufficient practice, you may find you want to write in the optionality of a one-to-many relationship only when the binding is tight (that is, when at least one child record must always be present). Likewise, it's rather rare to find a many-to-many relationship that isn't "loose" on both sides. So you may eventually decide to annotate only the exceptional cases.