Cardinality

In a relationship between two entities, cardinality captures how many instances from one entity participate in the relationship with instances of the other entity. It is represented by the symbols that appear on both ends of a relationship line. It is through cardinality that the data rules are specified and enforced. Without cardinality, the most we can say about a relationship is that two entities are connected in some way through a rule. For example, Employee and Department have some kind of relationship, but we don’t know more than this.

For cardinality, the choices are simple: zero, one, or many. Many (some people read it as more), means any number greater than zero. Each side of a relationship can have any combination of zero, one, or many. Specifying zero or one allows us to capture whether or not an entity instance is required in a relationship. Specifying one or many allows us to capture how many of a particular instance participates in a given relationship.

Because we have only three cardinality symbols, we can’t specify an exact number (other than through documentation), as in “A Car contains four Tires.” We can only say, “A Car contains many Tires.”

Each of the cardinality symbols is illustrated in Figure 7.1, which contains Author and Title. The business rules are:

·         Each Author may write one or many Titles.

·         Each Title must be written by one Author.

Figure 7.1 Author and Title, take 1

The small vertical line means one. (Looks like a 1, doesn’t it?) The circle means zero. (Looks like a zero too!) The zero implies optionality and does not exclude the value one so in the above example an author can write just one title too.

The triangle with a line through the middle means many. Some people call the many symbol a crow’s foot. Relationship lines are frequently labeled to clarify the relationship and express the rule that the relationship represents. A data model is a communication tool, and if you think of the entities as nouns, the relationship label is a present tense verb, so we are just reading a sentence:

·         Each Author may write one or many Titles.

Having a zero in the cardinality makes us use optional-sounding words such as may or can when reading the relationship. Without the zero, we use mandatory-sounding terms such as must or have to. So instead of being redundant and saying:

·         Each Author may write zero, one or many Titles.

We take out the word zero because it is expressed using the word may, which implies the zero:

·         Each Author may write one or many Titles.

Every relationship has a parent and a child. The parent entity appears on the one side of the relationship, and the child appears on the many side of the relationship. In Figure 7.1, the parent entity is Author, and the child entity is Title. When I read a relationship, I start with the entity on the one side of the relationship (the parent entity) first. “Each Author may write one or many Titles.” It’s then followed by reading the relationship from the many side: “Each Title must be written by one Author.”

I also always use the word each in reading a relationship, starting with the parent side. The reason for the word each is that you want to specify, on average how many instances of one entity relate to a different entity instance.

In Figure 7.1 we modeled that a Title must be written by one Author. Let’s now allow a Title to be written by more than one Author. See Figure 7.2.

Figure 7.2 Author and Title, take 2

This is an example of a many-to-many relationship, in contrast to the previous example, which was a one-to-many relationship. The business rules here read as follows:

·         Each Author may write one or many Titles.

·         Each Title must be written by one or many Authors.

Write in Figure 7.2 is an example of a relationship label. Make sure relationship labels are as descriptive as possible. Here are some examples of good label names:

·         contain

·         work for

·         own

·         initiate

·         categorize

·         apply to

Always avoid the following words as label names, as they provide no additional information to the reader (you can use these words in combination with other words to make a meaningful label name; just avoid using these words by themselves):

·         has

·         have

·         associate

·         participate

·         relate

·         be

A useful technique for checking whether relationship names make sense is to read them out aloud to a colleague who’s not directly involved in creating the model. If they don’t understand what you mean, you may need to reconsider your choice of verb. For example, replace the relationship sentence:

Each Person is associated with one Company.

with

Each Person is employed by one Company.

Fortunately, in ER/Studio, as you write the verb on the relationship label in the Relationship Editor, the sentence phrase is automatically written so you can check the meaningfulness of the label. ER/Studio uses the words A or An instead of Each at the beginning of the sentence, and also prefers plural verbs over singular verbs for the relationship label. For example, in ER/Studio, “An Author writes one or many Titles” instead of “Each Author may write one or many Titles”. Either approach works fine, just remember to be consistent throughout the model.

Many modelers capture labels on both sides of the relationship line instead of just one side, as shown in this chapter. In weighing simplicity versus precision, I chose simplicity. The other label can often be inferred from the label that appears on the model. For example, if write is the verb phrase from Author to Title, then written by is assumed to be the verb phrase from Title to Author.