Discover the relationships

A convenient way to discover the relationships is to prepare a matrix that names all the entities on the rows and again on the columns. The matrix in Figure 1 reflects the entities for the personal telephone directory.
Figure 1. A matrix that reflects the entities for a personal telephone directory
The diagram shows a table with five columns and five rows. The same labels are used for both the rows and the columns. There is one row and one column for each of the following: -name -address -number (voice) -number (fax) -number (modem) Those cells in the table that are duplicates are blocked out. For example there are two cells that represent the relationship between name and address. One is (name,address) and the other is (address,name). The (address,name) cell is blocked out.

You can ignore the shaded portion of the matrix. You must consider the diagonal cells; that is, you must ask the question, “What is the relationship between an A and another A?” In this model, the answer is always none. No relationship exists between a name and a name or an address and another address, at least none that you are required to record in this model. When a relationship exists between an A and another A, you have found a recursive relationship. (See Resolve other special relationships.)

For all cells for which the answer is clearly none, write none in the matrix. Figure 2 shows the current matrix.
Figure 2. A matrix with initial relationships included
The same matrix shown in the previous figure except that the word "none" has been added to all cells which have the same label for both row and column. For example the (name,name) cell has the word "none" in it.

Although no entities relate to themselves in this model, this situation is not always true in other models. A typical example is an employee who is the manager of another employee. Another example occurs in manufacturing, when a part entity is a component of another part.

In the remaining cells, write the connectivity relationship that exists between the entity on the row and the entity on the column. The following kinds of relationships are possible:
  • One-to-one (1:1), in which not more than one entity A exists for one entity B and not more than one B for one A.
  • One-to-many (1:n), in which more than one entity A never exists, but several entities B can be related to A (or vice versa).
  • Many-to-many (m:n), in which several entities A can be related to one B and several entities B can be related to one A.

One-to-many relationships are the most common. The telephone directory model shows one-to-many and many-to-many relationships.

As Figure 2 shows, the first unfilled cell represents the relationship between names and addresses. What connectivity lies between these entities? You might ask yourself, “How many names can be associated with an address?” You decide that a name can have zero or one address but no more than one. You write 0-1 opposite name and below address, as Figure 3 shows.
Figure 3. Relationship between name and address
begin figure description - This figure is described in the surrounding text. - end figure description

Ask yourself how many addresses can be associated with a name. You decide that an address can be associated with more than one name. For example, you can know several people at one company or more than two people who live at the same address.

Can an address be associated with zero names? That is, should it be possible for an address to exist when no names use it? You decide that yes, it can. Below address and opposite name, you write 0-n, as Figure 4 shows.
Figure 4. Relationship between address and name
The (name,address) cell shows both the name to address and the address to name relationships. The name to address relationship is 0-1 and is written closer to the left side of the cell to show that the name of the row comes first in the relationship. The newly added address to name relationship is 0-n and is written closer to the top of the cell to show that the column name comes first in the relationship.

If you decide that an address cannot exist unless it is associated with at least one name, you write 1-n instead of 0-n.

When the cardinality of a relationship is limited on either side to 1, it is a 1:n relationship. In this case, the relationship between names and addresses is a 1:n relationship.

Now consider the next cell in Figure 2: the relationship between a name and a voice number. How many voice numbers can a name be associated with, one or more than one? When you look at your telephone directory, you see that you have often noted more than one telephone number for a person. A busy salesperson might have a home number, an office number, a paging number, and a car phone number. But you might also have names without associated numbers. You write 0-n opposite name and below number (voice), as Figure 5 shows.
Figure 5. Relationship between name and number
begin figure description - This figure is described in the surrounding text. - end figure description
What is the other side of this relationship? How many names can be associated with a voice number? You decide that only one name can be associated with a voice number. Can a number be associated with zero names? You decide and are not required to record a number unless someone uses it. You write 1 under number (voice) and opposite name, as Figure 6 shows.
Figure 6. Relationship between number and name
The (name, number (voice)) cell shows two relationships. The name to number relationship is shown as 0-n. It is written closer to the left of the cell to show that the name of the row comes first in the relationship. The number (voice) to name relationship is 1 and is written closer to the top of the cell to show that the name of the column comes first in the relationship.
To fill out the rest of the matrix in the same fashion, take the following factors into account:
  • A name can be associated with more than one fax number; for example, a company can have several fax machines. Conversely, a fax number can be associated with more than one name; for example, several people can use the same fax number.
  • A modem number must be associated with exactly one name. (This is an arbitrary decree to complicate the example; accept it as a requirement of the design.) However, a name can have more than one associated modem number; for example, a company computer can have several dial-up lines.
  • Although some relationship exists between a voice number and an address, a modem number and an address, and a fax number and an address in the real world, none of these relationships must be recorded in this model. An indirect relationship already exists through name.
Figure 7 shows a completed matrix.
Figure 7. A completed matrix for a telephone directory
Any cell that is not in the name row is either blocked out or has the word "none". The (name,name) relationship is "none". The rest of the relationships are as follows: name to address: 0-1 address to name: 0-n name to number (voice): 0-n number (voice) to name: 1 name to number (fax): 0-n number (fax) to name: 1-n name to number (modem): 0-n number (modem) to name: 1

Other decisions that the matrix reveals are that no relationships exist between a fax number and a modem number, between a voice number and a fax number, or between a voice number and a modem number.

You might disagree with some of these decisions (for example, that a relationship between voice numbers and modem numbers is not supported). For the sake of this example, these are our business rules.